How to connect ms access and vb.net using odbc

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.

Requirements:

  • 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:

image

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’.

image

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:

image

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.

image

Mine is in:

C:\Users\Wern\Documents

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:

  • namespace
  • connection string

I used odbc to connect so the name space will be:

Imports System.Data.Odbc

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’.

image

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

 

Conclusion

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.

5 thoughts on “How to connect ms access and vb.net using odbc

  1. Pingback: vb.net database connection basics part 1 « Data Integrated Entity

  2. i having trouble shoot in this connection…while i going to select the ms access in administrator tools its showing one msg…that is odbc driver not found so re install yours drive…here could you please explain or send me the details in my mail id..please its urgent…here whats the drive i want to re install and how it will be

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s