Concept of Connecting MySQL data into the EXCEL using VBA

There're numerous modules available on the internet about Importing MySQL Data into Excel using VBA. But What they don't explain is about the concept of the Connection string. you need to understand few important attributes of connecting MySQL server with ODBC Drivers.

We need the following few things to attempt this procedure.

1. Go to the Tools > References > and choose Microsoft ActiveX Data Objects x.x Library (6.1 in my case) 
2. Then we need to create two objects with ADODB (ActiveX Data Objects), one for connection and one for data storage (which brings data with it).

We refer,
Connection as ADODB.Connection, and 
Recordset as ADODB.Recordset

The first part is over. Now, comes the toughest part of the program where we need to create the connection between our excel workbook and the MySQL server where your data is kept under tables.

Now, to connect both excel and server, we need to address the things very properly. and it should be. because without providing the proper addressing, we will surely get the frustrating errors. anyway, let's get back to the point. so, addressing the server correctly, we need to provide the various pieces of information to the objects. and those pieces of information are attributes which we will use to get the perfect data as queried.

Here are those important attributes / element. 
- Driver / Provider
- Server Name / Host
- DataSource / Database 
- Initial Catalog / Table Name 
- Username 
- User Password
- Port No.

You need someone who supports you while reaching towards your destination. that we called a DRIVER. the first element. there are many attributes we required to mention but the DRIVER is the most sensitive part of the program. Because it requires the damn clear conception of ODBC Driver. (There are many kinds of drivers, but mostly ODBC driver has been used to fetch the MySQL Server Data)

ODBC is the clear and simple driver/software, which will use your attributes/elements and will reach the perfect destination using your SQL Queries. Let's looks at the definition of ODBC by Microsoft. "Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data."

Now, ODBC Drive Which I have used is. "MySQL ODBC 5.3 ANSI Driver". There are many version available of the same software but I prefer to use this because it's the most successful one till.

You can find the ODBC driver in the System Drive C: > Windows Folder.

now the most important thing to remember is that, where this file is actually placed?

if your Windows operating is 32bit and office application also 32bit then VBA will look the ODBC driver in the system32 folder under windows. there's no issue at all.

but what if you have 64bit windows and running the 32bit office? you will face Driver Lost errors. It's just because, mostly all active drivers in the 64bit windows are kept in SYSWOW64 named folder under the same windows folder. So you need to copy that driver from system32 to the syswow64 folder.

but first, find the driver which named as odbcad32.exe (you can even find that drive in Administrative Tools)

Double click and find the DRIVER tab. you will get the above-mentioned ODBC driver name there. what if you don't have that driver specified there? just don't worry. Google the "MySQL ODBC 5.3 ANSI Driver" to download that driver's MSI form. and installed it. it has nearly an 8 MB of size. and I recommend you to choose 32bit if your office is 32bit. vice versa.

after installing, check the driver tab again, you will get that name there.... and now try your code again. you will get the result. and that's how I get.

it's the only thing which hasn't been discussed anywhere. so I thought I should share with you the proper concept.

thank you.



- Kamal C Bharakhda