Blog Entry
Creating ADODB Connection in VB
April 23, 2008 by Guru, under Programming, Tips and Tricks.
Want to read our new posts as they are published? Subscribe to our feed or by email for instant updates. Thanks for visiting and keep visiting!
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
rsquery.close
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!
You might also be interested in:
7 Responses to “Creating ADODB Connection in VB”
Comment by Albert
make an video tutorial for this tutorial for the new user or doesn’t have any background on vb.
Comment by InF
Good suggestion Albert! It will be considered.
(Hear that Guru? Get going! Thou art the VB-Guy! )
Comment by Swetha
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
Comment by 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.
#1
rstable(0)=txtsomething.text
“(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
#2
We can also use the below method which i prefer.
rstable(“fieldname”)=txtsomething.text
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. 
Comment by sanusiemmanuel
Pls, how do i connect to MySQL Database,ODBC , oracle and access 2007
Comment by pcnorb
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?
TIA!
John Croson
http://www.croson.us
pcnorb.homelinux.org
ihbsonline.com
Comment by Metting Project deadlines | YasirMX.
[...] Well to my friends, VB6 seems to be a toy and they even had time to write a tutorial about connecting to an ADODB connection. http://www.geekscribes.net/blog/2008/04/23/creating-adodb-connection-in-vb/ [...]