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! 😀
Pingback: Metting Project deadlines | YasirMX()