In a MySQL Forum tip, Jay Alverson suggests encapsulating all the details in a file DSN:
Sub FileDSN()
With ActiveSheet.QueryTables.Add(Connection:="ODBC;FileDSN=C:\Writing\Winbatch\MYSQL\VBS-MySQL.dsn;", Destination := Range("A1"))
.CommandText = "SELECT * from orders"
.Name = "MySQL Test 1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = "C:\Writing\Winbatch\MYSQL\VBS-MySQL.dsn"
.Refresh BackgroundQuery:=False
End With
End Sub
The .dsn file says:
[ODBC]
DRIVER=MySQL ODBC 5.1 Driver
UID=Winbatch
PWD=WBTPassword
OPTION=35
PORT=3306
DATABASE=test
SERVER=localhost
|
|