Using VBA to pull data from mySQL to excel with Macro button

Hey everyone,

So I have a materials database made up in mySQL, which holds linked tables for materials, manufacturers and vendors.

I also have a Bill of Materials (BOM) in excel, in which there are empty columns for manufacturers, vendors, etc.

The goal is to have a Macro button that, once pressed, it will populate the BOM's empty columns with the appropriate data from the database.

I have very little experience with VBA and macros, so the information I have gathered is all from forums and the web.

In VBA, I have created a module for the connection to the database.

Please Login or Register  to view this content.

After getting a few debug errors, I am now able to run it without any errors. Although when I click run, nothing really seems to happen, but I am assuming a connection is being made behind the scenes?

*Not sure if it's useful to mention at this point, I have already set up the ODBC driver and successfully tested the connection to the db, as well as added ActiveX Data Object 2.8 to Reference in VBA

Back in excel, I added a macro button to the BOM, and inserted the following code:

Please Login or Register  to view this content.

Although the code for the button does not do what I intend, it's mainly just to confirm that connections and whatnot are correct. Once I have confirmed that, I will work on the button code to do what I actually want it to..

I seem to be getting an error at the .ActiveConnection line and not sure why.

Was wondering if someone could let me know if I have the right idea/heading in the right direction, and possibly, what might be wrong with my code?

Thanks!

Your own connection would be at the Accesscontr$ line?

For provider, would I then use Microsoft.ACE.OLEDB.5.3; ? I just copied your formatting and changed it to the version I have... I think I'm lost

I have found in C:\Users\Me\Documents\My Data Sources the materialsdb.odc but no .accdb file?

No, you need to connect to MYSQL. I am trying to find the link website and will post it shortly.
This site has all the connection strings. All you need is copy the string and then adjust the file name
http://www.connectionstrings.com/mysql/

I posted code for two different modules. The first one I assumed opens the connection. The second one carries out the data import when the button is clicked. Is that not how it would work? I'm very inexperienced in vba.

I have it as:

Please Login or Register  to view this content.

As I step through it, it stops at .Open "Run-time error '3709': Application-define or object definer error"

Noted! Thanks

I might have posted this already, not sure why but my comments are not appearing..

Anyways, the line

Please Login or Register  to view this content.

was commented out in AB33's code example. Using yours, it's not commented out, and it's called cn (not cnMaterialsdb)

I would much rather use the two samples of code you posted because it seems to be somewhat functional.

I have been able to step through the button click module, which automatically jumps into and steps through the ADOExcelSQLServer function, and then jumps back into the button click function, steps through but seems to stop at rsMaterialsdb.Close with errors.

Unless you think I should scrap it and go back to AB33's code you just posted?
If I go use that code instead, I step through and get stopped after rsMaterialsdb.Open

Should I have C:\Analysis Version1.xlsx ?

What is [Select File] button for?

@AB33 I have been having some success with the previous code. I have been able to run it, and when I look at column O, I see that it has imported the Manufacturers table from O6:P64 (which is the whole table size)

The code I am currently using for the connection:

Please Login or Register  to view this content.

And for the button:

Please Login or Register  to view this content.

I had to comment out the rsMaterialsdb.Close line because, when uncommented, I would get an error pointing to that line, but the data would still import to O6. Does that just mean that the connection will stay open now that it is commented out?

SOLVED! Thanks, you guys have been so helpful! Much appreciated