1

I am writing a simple VBA that inserts a query to my local MySQL database.

With Mysql ODBC Connector 64bit Installed.

Private Sub CommandButton1_Click()

Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
strCon = "Driver={MySQL ODBC 5.1 Driver};SERVER=127.0.0.1;DATABASE=DATABASENAME;UID=USER;PWD=PASSWORD"

Dim rw As Integer
c.Open strCon

sq = "INSERT INTO `DATABASE`.`TABLE` (`ID`, `title`) VALUES ('1', 'SOMETITLE');"

Set r = c.Execute(sq)
c.Close

End Sub

And added the reference

Tools > References >"Microsoft ActiveX Data Objects 2.5 Library"

When I test it it gives me the error below

Run-time error '-2147467259 (80004005)':

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

When I install Mysql ODBC Connector 32bit everything works.

Yet, my question is Why would the 32 bit work? Despite my system is 64 bits and MySQL is also 64 bit . Why would the 64 bit version giving me such error, yet 32 bit worked?

Chester Chin
  • 11
  • 3
  • Is your VBA also 63-bit or still 32-bit? As far as I know the driver must comply with the used application not the OS. – Kurt Ludikovsky Feb 11 '16 at 14:27
  • HI my excel VBA is 32 bit(Under the assumption that its 64 bit since its running the latest), thanks for pointing that out, I did more searches and it seems that OS bits doesn't matter. [Reference](http://stackoverflow.com/a/13802966/5829379) I even went through the trouble changing the registry and odbc management and none worked in the first place. @KurtLudikovsky – Chester Chin Feb 11 '16 at 14:33
  • I believe driver should be `Driver={MySQL ODBC 5.1w Driver};` in the connection string. Pls note the letter `w` in the driver name. – Shadow Feb 11 '16 at 15:02
  • But it would be simpler to create a connection string using myodbc's GUI and just use that one from you app. – Shadow Feb 11 '16 at 15:06
  • The driver is dependet on the Applicattion (Excel) and not on the OS. Unfortunatly it's somewhat difficult to distinguish both drivers. I had this some time ago. (BY THE WAY, 5.1 seems somewhat outdated. I used 5 years abo already 5.2, just as a sidenote) – Kurt Ludikovsky Feb 11 '16 at 15:20
  • @Shadow The Driver={MySQL ODBC 5.1 Driver}; worked on my case, is there any difference for the 'w'? Do let me know as I would like to understand the difference and I am overwhelmed there are many drivers to choose from. – Chester Chin Feb 11 '16 at 18:42
  • @KurtLudikovsky Thanks for the note, the code is referenced from some tutorials would stick to 5.1. Did some research and The 5.2 version comes in two flavors. ANSI and Unicode. For now I would stick with 5.1 till I am comfortable working with it. I assume there are not much difference for general purpose query(Correct me if I am wrong). – Chester Chin Feb 11 '16 at 18:48
  • 5.1 also comes in 2 flavours, w being the unicode one. – Shadow Feb 11 '16 at 19:32
  • Frankly, I don't have too much experience. I was just wounderung that I already used 5.2 several year ago, and did not have any troubles, after getting it to work. – Kurt Ludikovsky Feb 11 '16 at 20:17

1 Answers1

1

you must check your OFFICE version 32 or 64 bits and use the same odbc mysql driver version.

HeyYouCoding
  • 103
  • 4