Macro - Query MySQL Database with DSN-less Connection

Question

Macro - Query MySQL Database with DSN-less Connection

Anonymous

Can anyone help me with writing a macro to query a MySQL database? From Excel?

I can successfully query the remote MySQL db using MS Query and a DSN. But I need to make it more portable, so multiple users can grab the file and work on it anywhere.

I only need to bring the data into Excel from the MySQL database once each time the file opens.

Any ideas? I've seen a number of articles about it, but I can't seem to get the syntax correct.

Users are currently using Excel 2003 on Windows XP and Vista (32 bit).

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

For anyone else looking, this is what I came up with. I couldn't find another way to create a DSN-less connection. I thought you should be able to create the DSN-less connection manually, but I guess not. It always asks for a DSN or Data Source Name.

Create a Macro that creates a TableQuery. You only need to do this once. Once the table is setup, the connection stays saved in the file.

Here is the general code I used for the Macro in Excel (you need to replace the <info> with your actual values, without the brackets):

Sub DSNLess_Connection ()

Dim dSource As String

dSource = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};" _

& " SERVER=<server name>;DATABASE=<database name>;" _

& " UID=<user name>;PWD=<strong password>;Port=3306"

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _

dSource), Array("E=db35211_CustomerMasterList;DefaultTable=Customers;")), _

Destination:=Range("$A$1")).QueryTable

.CommandText = Array("Select * FROM <TableName>")

.RefreshOnFileOpen = True

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = True

End With

End Sub

Refresh the Data Table in Excel.

You may need to manually refresh the data in the query table the first time. Once that is done, the table should refresh based on the properties in the Connection settings (see #3).

Check the connection settings.

In Excel 2007, it is under Data>Connections. You are given a few options about when the data will refresh in the "Usage" tab of the Connection Properties. Also, if you go to the "Definition" options, you can see how the macro set up the connection.