Creating ADODB Connection in VB


Developing applications in Visual Basic (VB) is rather easy. But, thinking of connecting to a database using purely codes may look difficult. But it is not! After understanding the basics and coding a little, it becomes as easy as pie. First we need to get everything ready with the references, then understand what you will need to do (I leave this part to you. Google is here!) and finally creating the connection… Note that this article pertains to VB6, but might work for other flavors.Setting the Reference We will be using Microsoft ActiveX Data Objects 2.6 Library. You can find it under “Project” -> “References“. Now, you must have got all the references shown, scroll down and find “Microsoft ActiveX Data Objects 2.6 Library” and check the box beside it. You are now ready for connecting to your database.

Setting up the Module – Do the following steps, “Project“->”Add Module” and you will have something like “Module1.bas” appearing in your project. Don’t panic. ๐Ÿ˜› Just head over to the module, and in the “Properties” window, set its name to “conndb”, that’s something meaningful. ๐Ÿ˜€ Now, head over in the module by double-clicking on it. Remember, when we want to start up our forms, (well technically, any objects via a module) we must set “Startup Object” as “Sub Main”. Still thinking where to do it? This is found in your Project Properties. In your “conndb” module, put the code snippet below:

Private sub main()
‘this can be considered as the starting point of the project
‘here we will start our main form and open our connection to database
End Sub

Shooting the connection – We can open our database connection now. Please note, this tutorial exclusively makes use of MS Access Database. But! The concept remains same when using other data engines. We proceed further by creating a new “Sub” in our “conndb” module. I would name it as “connect_db“, but you can choose your name as you like.

Private sub connect_db()

End Sub

Go to the top of the module (i.e the first line in conndb) type:

option explicit

dim conn as new adodb.connection

dim cmdrs as new adodb.command ‘repeat this for each table (recordset) you will be using

public rstable as new adodb.recordset ‘repeat this for each table (recordset) you will be using

Private sub connect_db()

‘create the database connection

with conndb

.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source_

=” & App.Path & “\” & “database\database.mdb;Mode=Read|Write” ‘your DB path here

.CursorLocation = adUseClient

.Open ‘open the connection

end with

‘we check if the connection to the database is working

if conndb.state=adstateclosed then

msgbox “Connection to database failed” ‘whoops! What happened now? Go figure!

end ‘exits application

end if

‘open all the recordsets needed

‘this is a sample for only one recordset. You just need to replicate with appropriate settings

With cmdrs

.ActiveConnection = conndb

.CommandText = “Set your SQL query over here

.CommandType = adCmdText

End With

With rstable

.CursorType = adOpenStatic

.CursorLocation = adUseClient

.LockType = adLockOptimistic

.Open cmdrs

End With

End Sub

Simply said, we are done with the database connection. Wasn’t it quite fast? Yesh, real fast. The rest, the programmer will use the connection and opened recordset to beautifully code any database-related application and jobs. Only the recordsets need to be made public as they will be used in in other forms and I assume more than once on many forms.

Tip: You will often need to query your database. So just use a global (public) sub with a parameter to pass sql statements. In turn, the sql statement will be processed and result returned in recordset. Check this:

dim cmdquery as new adodb.command

dim rsquery as new adodb.recordset

public sub query(sql as string)

if rsquery.state=adstateopen then ‘if the recordset is already close it


end if

With cmdquery

.ActiveConnection = conndb

.CommandText = “SQL” ‘here your SQL statements are being assigned

.CommandType = adCmdText

End With

With rsquery

.CursorType = adOpenStatic

.CursorLocation = adUseClient

.LockType = adLockOptimistic

.Open cmdquery

End With

End sub

That’s all. You do normal recordset manipulation with rsquery.Keep tuned. I will post more goodies. If you got questions, comment box’s below! ๐Ÿ˜€

  • Pingback: Metting Project deadlines | YasirMX()

  • Albert

    make an video tutorial for this tutorial for the new user or doesn’t have any background on vb.

  • InF

    Good suggestion Albert! It will be considered.

    (Hear that Guru? Get going! Thou art the VB-Guy! :D)

  • Hi, I read through the coding. Till now i was using the ADODC to get the connections done. Using this coding snippet if the connections are done then later to add or delete from the recordset what would be the coding that i need to use. Please help.
    Thanks in advance

  • Guru

    Ideally, you would use rstable.addnew

    Then, it is logical to push all the data from our input form to the recordset and update it so as the data gets stored in our database. Let me show you how to do it.


    “(0)” represents the column it is referring to. “0” is the index. It starts from 0 to number of fields in database minus 1 (n-1).

    Lets say we have got 5 fields, so it will start from 0 to 4

    We can also use the below method which i prefer.


    This is more meaningful. We can understand what is going on and our code becomes more readable.

    The last step for adding new record is rstable.update

    Now we are done with adding new record.

    Deleting a record is simple. Use any method (record navigation, search or filter) for going to a record, then on your delete button (that is what i use normally), use rstable.delete

    That’s it
    Note: I have not tried the codes in my comment right now. It was some time back. So test it and let me know. And btw, the codes in the tut work well. ๐Ÿ™‚

  • sanusiemmanuel

    Pls, how do i connect to MySQL Database,ODBC , oracle and access 2007

  • Excellent examples. Gave me the push I needed for querying a MSIDXS data source for sorting OCR’d tif documents.

    Which reminds me; anyone know of a source for good comparisons of the MODI iFilter compared to the likes of iFilterShop or Captaris?


    John Croson

  • Public cn As New ADODB.Connection
    Public rs As New ADODB.Recordset
    Public mysqlport, myusername, mypassword, mydatabasename, serverhost As String

    Public Sub main()
    Open App.path & “/connection.urs” For Input As #1
    Input #1, serverhost
    Input #1, mydatabasename
    Input #1, myusername
    Input #1, mypassword
    Input #1, mysqlport
    Close #1

    cn.ConnectionString = “Driver={MySQL ODBC 5.1 Driver};Server=”&serverhost&”;Port=”&mysqlport&”;Database=”&mydatabasename&”;Password=”&mypassword&”;Option=3;”
    Debug.Print “Connection Object Created”

    If cn.State = adStateClosed Then
    MsgBox “unable to connect to database”, vbCritical
    Exit Sub
    End If
    cn.CursorLocation = adUseClient
    End Sub

    Public Function openrecordset(YourQuery As String)
    If rs.State = 0 Then rs.Open YourQuery, cn, adOpenDynamic, adLockBatchOptimistic
    End If
    End Function

    Public Function closerecordset()
    If rs.State = 1 Then rs.Close
    End Function
    Public Function ConnectionDetails(Server As String, DatabaseName As String, Username As String, Password As String, Port As Integer)
    Open App.path & “/connection.urs” For Output As #1
    Print #1, Server
    Print #1, DatabaseName
    Print #1, Username
    Print #1, Password
    Print #1, Port
    Close #1
    End Function

  • ifung

    how to execute sql query(insert into data base)