Search Tools Links Login

How to Connect to MySQL with VB6

Posted: 2018-11-23
By: vb6boy
Viewed: 10,223

Filed Under:

VB6 Tutorials, VB6 Databases, Tutorial, VB6 Code Cache

Title Uploaded Size 11/23/2018 2:52:20 PM 90,769

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.

Selecting the Project dropdown

Scroll down the list, looking for the correct MSADO entry. In this example, I've chosen the Microsoft ActiveX Data Object 6.1 library:

Selecting the referenc

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;" & _

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"
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"
   do while not objRS.EOF
      debug.print strFirstName & " " & strLastName
End if
Set objRS=Nothing

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.


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.


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.

Comments on this post

AnonymousCoward posted this comment on 2020-03-24:

Hola, estoy tratando de conectar VB6 usando el ODBC de MySQL 8.0, pero solo obtengo un mensaje que dice "No se encuentra el nombre del origen de datos y no se especificó ningún controlador prefeterminado". Hay alguna configuración que esté olvidando?


Hello, I am trying to connect VB6 using MySQL 8.0 ODBC, but I am only getting a message saying "Data source name not found and no default driver specified". Are there any settings that I am forgetting?

dwirch posted this comment on 2020-03-24:

The first thing I would check would be to ensure that you have the necessary MySQL driver loaded. In the control panel, select ODBC 64-bit (or 32-bit, depending on your architecture), and ensure the name of the driver matches the name you are specifying in the connection string. See the screenshot for an example of MySQL ODBC drivers.

The screenshot shows the drivers currently loaded on my system. For my VB6 projects, I utilize the MySQL ODBC 8.0 Unicode Driver, and I am able to connect to MySQL databases for which I have permissions.

En español:

Lo primero que comprobaría sería asegurarme de que tiene el controlador MySQL necesario cargado. En el panel de control, seleccione ODBC de 64 bits (o 32 bits, dependiendo de la arquitectura) y asegúrese de que el nombre del controlador coincide con el nombre que está especificando en la cadena de conexión. Vea la captura de pantalla para ver un ejemplo de controladores ODBC de MySQL.

La captura de pantalla muestra los controladores cargados actualmente en mi sistema. Para mis proyectos VB6, utilizo el MySQL ODBC 8.0 Unicode Driver y puedo conectarme a bases de datos MySQL para las que tengo permisos.

Disculpas. Mi español no es bueno.

AnonymousCoward posted this comment on 2022-04-18:

Thanks for explaining data communication with MySQL using very old version of VB6.
If possible, I would like you to explain the structure of the data table as well.

dwirch posted this comment on 2022-04-19:

The demonstration database contained within the zip file attached to this post contains a World Facts database. The database contains several tables.

If you examine the content of the .sql file, you'll see the data within, along with the table and field definitions.  The easiest way to see it in a "nice" format is to import the .sql file in to your MySQL server.  The .sql file contains all the necessary operations to create the schema, the tables, and insert the data.

You must be logged in to make a comment.