For some folks, connecting to a relational database system (RDBMS) seems like a magical art. It’s not really that difficult, and I’ll show you how to do it in this post.
Even though Visual Basic 6 has been out of production for quite a number of years, I find that it is still being used to teach programming concepts to budding computer programmers. I see tons of hits to this site with query strings containing “How to VB6”, which shows there are a lot of people out there still using this robust little tool.
This post isn’t meant to be a “my language is better than your language” argument. It is targeted to those folks who are wanting or needing to utilize an RDBMS with VB6 as part of a training program, or perhaps as part of a finished software product. I’m a big proponent of using the correct tool for the job, not just blindly following the latest trend like some kind of zealot.
… Anyway …
In most beginning VB6 programming classes, students are taught to use the built-in ActiveX controls to perform database access. I’m not a big fan of using controls of this nature, which (to me) seem to add quite a bit of overhead to database operations. Plus, you’re tied to an ActiveX control, which means you need to ensure the control is available on the end-users system.
In software that I write, I leverage Microsoft Active Data Objects Library, or MSADO. This database library is distributed as a part of Windows 10, and if not included with previous version of Windows, it is easily downloaded from Microsoft. By leveraging this DLL which is already pervasive in Windows installations, it precludes the need to distribute or reference any “oddball” ActiveX controls.
To enable your program to access a database with this method, all you need to do is add a reference to the DLL in the development environment. To do this, simply click pn the Project menu item at the top, and select References.
Scroll down the list, looking for the correct MSADO entry. In this example, I’ve chosen the Microsoft ActiveX Data Object 6.1 library:
The next thing you need to do is instantiate the database connection. This is done by using DIM to define it. The database connection is what is opened and closed when talking to the database, as well as sending commands.
Similarly, you’ll need a recordset object, which is used to receive data returned from the database by queries.
The initial set up of database connectivity would like something like the following:
Dim objConn as New ADODB.Connection
Dim objRS as New ADODB.Recordset
Now that you’ve got the basic connection object built, and before you start trying to throw queries at the database, you need to first configure the database connection object. This is done by defining a connection string for the object. Here is an example:
objConn.ConnectionString = "Driver={MySQL ODBC 8.0 Unicode Driver};" & _
"Server=MyDatabaseServer;" & _
"Database=MyDatabaseSchemaName;" & _
"User=MyDBUserName;" & _
"Password=MyPassword;"
In this example, I’ve defined the driver type that will be used to connect to the database. You can specify any driver that is installed on your system. As shown, I am using the MySQL ODBC version 8.0 driver, but you can use SQL, Oracle, MS Access, or even text drivers to connect to your database.
The rest of the fields are pretty self explanatory, basically telling the connection object where the database lives, the name of the database, and what credentials to use when accessing the database.
Now that all the preliminaries are taken care of, we can actually open the database object, send a query, and retrieve the results into a recordset. Here is some example code for it:
strSQL="select firstname,lastname from employees where active=1"
objConn.Open
Set objRS=objConn.Execute(strSQL)
if objRS.EOF=True then
' no records found
' use this space to inform the user
debug.print "no records found"
else
do while not objRS.EOF
strFirstName=objRS("firstname")
strLastName=objRS("lastname")
debug.print strFirstName & " " & strLastName
objRS.MoveNext
Loop
End if
objRS.Close
Set objRS=Nothing
objConn.Close
In this sample code, the query command to be sent to the database is stored in strSQL. Next, the database connection is opened, and the query is sent, storing the results in the objRS recordset.
After the recordset is returned, a simple check is performed in the If/Then/Else test to see if any records returned. If the recordset is empty (or EOF (End of File)), a “no records” message is sent to the immediate window. If records were returned, they are printed in the immediate window.
QuickHint
Note the use of the .MoveNext command for the recordset. This is telling the recordset object to move to the next record in the recordset. If you do not include this in a loop that is reading from the recordset, your code will appear to hang.
Why does this happen? Because the record set will keep returning the same record in the set, never advancing towards the end of the set! We never see the end!
Think of it like running in place. It might be excercise, but you’ll never go anywhere if you are running in place.
After the data is read through the do loop, the code continues to the closing and destruction of the recordset, and finally the closing of the database connection. ALWAYS close and destroy your recordsets, and ALWAYS close your database connections! Generally speaking, you don’t want to leave your connections open.
In larger, more complex programs, there many connections may be opening and closing constantly. Connecting to a data source can be time consuming. To minimize the cost of opening connections, you can use an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. But that is a topic for another day.
Conclusion
So this is it. The above should get you a good start to connecting with just about any datasource you can think of. You can use this new found knowledge to build such software as library systems, HRMS’, knowledge repositories, and more.
I’ve included a sample project, with a sample database for MySQL. The project shows expanded examples of the content above, as well as modularization of functions and generalization of connection objects.
The hope is that people find this post helpful in grasping the basic concepts of manipulating data in an RDBMS.
Questions? Hit me up.
Attachments
| File | Uploaded | Size |
|---|---|---|
| 389-20181123-145220-VB6MySQLDemo.zip | 11/23/2018 2:52:20 PM | 90769 |