Connection to MySQL using VBA
 

Hi,

I have been using Excel to connect to a Simply Accounting SDB
database. Now I switch to 2009 which uses MySQL. The connection method
that I have been using doesn't work anymore. Appreciate if someone
could show me how to do it. The way I have been using is like this:

sDSN = "Simply Accounting: TestDB"
With ActiveSheet.QueryTables.Add
(Connection:="ODBC;DATABASE=TestDB;UID=john;PWD=12 34;DSN=" & sDSN,
Destination:=Range("A1"), Sql:=sSQL)
.FieldNames = bFieldname
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.PreserveColumnInfo = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Is it possible to just change the connection string?

Thanks
David

Connection to MySQL using VBA
 

You can edit the code as required. if you changed the location or name of
the data source you'll have to edit the code.

" wrote:

Hi,

I have been using Excel to connect to a Simply Accounting SDB
database. Now I switch to 2009 which uses MySQL. The connection method
that I have been using doesn't work anymore. Appreciate if someone
could show me how to do it. The way I have been using is like this:

sDSN = "Simply Accounting: TestDB"
With ActiveSheet.QueryTables.Add
(Connection:="ODBC;DATABASE=TestDB;UID=john;PWD=12 34;DSN=" & sDSN,
Destination:=Range("A1"), Sql:=sSQL)
.FieldNames = bFieldname
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.PreserveColumnInfo = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Is it possible to just change the connection string?

Thanks
David