[SOLVED]Excel, VBA to MySQL?

Hello Everyone,

I have been looking the last half an hour for help & tried varies things to get this working, but no luck. So I hope I can be helped with a new thread.

I want to say previously that I learned VBA "on the go" and thus am really a "newbie with a bit more knowledge".
I have however worked with Excel a lot I used MySQL before and PHP and other Web Application languages.

Well on to the actual topic:

I am working for a company that has been running for half a year now and was using a pile of Excel Workbooks to store clients, calculate commissions, make reports and so on. Now, I am not a big fan of using Excel as a big database, however it is good for making calculations and reports. Therefore as we will be having a Server running MySQL in the next couple of Weeks I am preparing for the change of the Excel sheets and whatever is necessary.

So, I set up on my PC a MySQL database. Which was working fine already set up the tables. Now what I wanted to try out if I can input all the client data from the excel sheet into the MySQL database and in reverse using Excel to get necessary data from the MySQL database & then calculating it for reports or whatever in Excel.

My Questions: (finally) a) Would you say this is the right way of doing so? (As a temporary solution until we will change completely web-based app)
b) Using ODBC-5.1.6 Connector I was unable to connect to the database using Excel 2007 with the Error that the Structure of the DSN does not match control and application (Sorry have to translate from spanish..) - any clues why that would be?
c) Using a VBA script I tried connecting via ADO or the ActiveX library however I keep getting a runtime error..

The code is here:

Code:

---

Option Explicit
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub UserForm_Initialize()
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
con.ConnectionString = "server=localhost" & _
                      ";driver=MySQL 5.1 driver;db=astramedia;" & _
                      "uid=root;pwd=password;" & _
                      "Option=16386"
con.CursorLocation = adUseClient
con.Open
end sub

---

I have left out the code for the recordset for now as on the error VBA highlights "con.Open" and obviously does not proceed.

Sorry for the long post.

Thanks alot for any suggestions & help!

I use an Oracle database and this is how I connect to our DB (except I use Msdaora for my provider)... maybe if you do some changing around it will work for you.

vb Code:

Dim Conn As ADODB.Connection
Dim rsRec As ADODB.Recordset
 
Set Conn = New Connection
 
Conn.ConnectionString="Provider=MySQLProv;Database=astramedia;Uid=root;Pwd=Password;"
Conn.Open
 
Set rsRec = New ADODB.Recordset
rsRec.CursorType = adOpenKeyset
rsRec.LockType = adLockOptimistic
rsRec.ActiveConnection = Conn

Thanks for the reply Chrissy!

Unfortunately I continue to get errors. Here is the correct translation of the last error:

Quote:

---

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

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

---

I followed some help on the Internet regarding to create a System DSN and not a User DSN. However this did not change anything and the error message stays the same.

Using the method suggested by Chrissy i get a different error:

Quote:

---

Run-time error '3706':

Unable to find the specified provider. It is possible that it has not been installed correctly.

---

So, I guessed changing Provider to Driver will give me different result.. And yes it throws me back to exactly the same error message as before (first one quoted)..

I am running out of ideas..
Could this be because I am running the Database on the same PC that I want to connect from?

Regards, thanks for further help!

Okay I think I just solved the problem. Thanks anyway!

Here I share the fix that worked for me:

I am running Windows 7 64 Bit system and thus downloaded all the ODBC Connectors for the 64 Bit system. However using this I was unable to connect Excel to the MySQL Database & I was unable to run a macro that would connect to the Database. Yet, on Administrator Tools the ODBC Driver always created a successful connection to the database - so I thought the MySQL ODBC 5.1 Driver would be fine to use.

However it seems on a 64 Bit system you have two Connectors one 32 Bit and one 64 Bit. The file "odbcad32.exe" lies in "C:\Windows\SysWOW64\" - You will realise that there is no MySQL ODBC Driver listed.
Now, to basically fix all this you have to also download the ODBC Connector 32Bit and install it. Then configure the driver to your Database. You will realise that the driver will not be a 5.1 instead 3.51 - Which you will need to change in your VBA script as well. Then it should work.

Link to the ODBC Connector here:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

Best regards, thanks for the help anyway!
I hope the next person running into this problem will have the solution a hell of a lot faster then I did!

Hey guys, How can i connect Excel-vba to mysql database.Plz send me suitable code.