VB/ADO connection string

from the Artful MySQL Tips List


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

Last updated 27 Aug 2009


Return to the Artful MySQL Tips page