Lets get started, first thing that you need to do is to download and install the requirements below. If you already have it installed then scroll down a little bit.
- MS Access 2003 or later(In this tutorial, I’ll be using MS Access 2010)
- Visual Studio 2008 or later
If you don’t already have an ms access database created, then create one. You will see something like this when you open up ms access 2010:
What you’ll want to do here is to create a new database and add a table on it.
After creating your new database, just head to ‘table 1’ right click it then select ‘design view’.
Input the necessary fields that you would want. If you don’t want an auto number(usually this is the primary key). Then delete it.
Press ‘ctrl+s’ on your keyboard. Then input the table name that you want.
Now, click on start orb. Then ‘control panel’. Select ‘administrative tools’ then click on ‘data sources’. ODBC means Open Database Connectivity. In case you’re wondering. It’s the built in function in windows operating system that allows you to open database connection for almost any database that you know. You can also use this method if you want to connect to mysql database.
Select, ‘MS Access database, from the list of database that is already listed. And you will see something like this:
You then would want to select the database that you created a while ago. Just go to the ‘database portion’ then click on ‘select’.
The next part is just easy. Just try to find where you have save your database. Usually comes with the file extension .mdb for database created using ms access 2003. And .accdb, for those that are created using ms access 2007 or 2010.
Mine is in:
After you’ve gotten a similar window to that of my screenshot. Select the database. In my case its ‘Database1.accdb’. Then click ‘ok’.
Remember: close the ms access windows before trying to click ‘ok’ because you will get an invalid directory error if you don’t.
If everything is clear on that, go to your vb.net application. Then add a new class. Where we will inherit the methods needed for manipulating the database. This is to separate the application logic from the interface partially. And to make the application more object-oriented.
If you don’t know how to add a class, just search for it on google.
As I’m always saying, if you connect to different database using vb.net. The code is almost always the same, the only thing that is changing is the:
- connection string
I used odbc to connect so the name space will be:
And for the connection string parameters, you can use the dsn name that you supplied earlier.
As a shortcut, you can always use the DSN or the data source name as a parameter for the connection string. I named mine ‘yoh’ from the shaman king character ‘yoh asakura’.
Here’s the full code, in the parent class where you will be inheriting:
Imports System.Data.Odbc Dim con As New OdbcConnection("DSN=yoh;") Dim cmd As New OdbcCommand Public neym As String Public Sub New() con.Open() cmd.Connection = con cmd.CommandText = "SELECT * FROM table1" End Sub Public Sub creates() cmd.CommandText = "INSERT INTO table1(Neyms) VALUES('" + neym + "')" cmd.ExecuteNonQuery() End Sub
Now, lets try to break the code down. So that you’ll understand what every line of code does.
This is the declaration of the class, its automatically generated code so you don’t have to type it. Its generated when you create a new class in visual studio:
Public Class Class1
This is the declaration of our connection string. This is the one who is responsible for connecting to the ms access database through odbc. Without it, everything won’t work. Of course, the DSN equivalent here is ‘yoh’ and it may vary depending on what you have declared in control panel.
Dim con As New OdbcConnection("DSN=yoh;")
This is the declaration of command string. Used for executing sql queries in your application:
Dim cmd As New OdbcCommand
This one is just the declaration of the string that you’ll be using to represent the data which you want to enter or retrieve from the database. Always remember that it should be a public string. That is if you don’t need calculations. If you need calculations use the proper data type like int or float or double. It should always be public so that the other class can access it. When I say other class, I’m referring to the other windows form that you have in your project.
Public neym As String
This is the constructor for the class. It instantiates the connection by opening it. And sets the connection that will be used by the command. Then sets an initial query to the database to select all the records from table1. You don’t have freedom to change the name of the subroutine, it should always be ‘new’ since it signifies that it’s a constructor.
Public Sub New() con.Open() cmd.Connection = con cmd.CommandText = "SELECT * FROM table1" End Sub
Lastly, our sample query. Which is enclosed in a subroutine. Again it should be public so that it can be called from any other class in your project. What were doing here is to insert a new record into our database and set the value to be equal to the strings that we declared a while ago. Then cmd.executeNonQuery, just executes the cmd.commandText.
Public Sub creates() cmd.CommandText = "INSERT INTO table1(Neyms) VALUES('" + neym + "')" cmd.ExecuteNonQuery() End Sub
There are other methods in connecting ms access with vb.net and I’m going to show it later if I have the time. But this one, is the easiest. Because the other methods require more parameters on the connection string.