We have managed to fix the problem:
a. Install mysql-connector-odbc-3.51.30-win32.msi on client workstation
b. Change password on MySQL server to avoid use of special characters (Ref.
http://www.utteraccess.com/forum/Problem-Finding-Correct-m-t1995008.html Post Oct 22 2012, 02:25 PM)
c. Cite Host IP address rather than hostname in the connection string (which gets around the error: Unknown MySQL server host )
Note: Client Workstations are running XP SP3 with Access 2010
Connection String in Access is:
ODBC;Driver=MySQL ODBC 3.51 Driver;SERVER=<ServerIPaddress>;DATABASE=tawp;USER=tawp;PASSWORD=<ServerPassword>;PORT=3306;IGNORE_SPACE=1;LOG_QUERY=1;DFLT_BIGINT_BIND_STR=1
where <ServerIPaddress> should be replaced by the ip addr of your mysql server
and <ServerPassword> should be replaced by the password for your mysql server
====
ALSO readers may find the following VBA useful which I developed to make it easy to update the Connection Strings of my linked tables in my Access 2010 Front-End.
---
VBA
Public Sub ModifyODBCConnectionStrings(Optional ConnectionStringFieldName As String = "ConnectionStringB")
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim rstRef As Recordset
RefTable = "ConnectionStrings"
Set rstRef = dbs.OpenRecordset(RefTable, dbOpenDynaset)
Dim rst As Recordset
Dim tdf As TableDef
Dim LocalTableName As String
SearchCriteria = "[UpdateYN] = " & True
rstRef.FindFirst (SearchCriteria)
While Not rstRef.NoMatch And Not rstRef.BOF And Not rstRef.EOF
LocalTableName = rstRef!LocalTableOrQueryName
Set tdf = dbs.TableDefs(LocalTableName)
' Display Current Connection String in debug window
CurrentConnectionString = tdf.Connect
Debug.Print "#####################################################################"
Debug.Print LocalTableName & ": CURRENT connection string for linked table: " & LocalTableName
Debug.Print " " & CurrentConnectionString
'Debug.Print " "
' Setup and update new connection string
' (BAD) ConnectionString = rstRef(ConnectionStringFieldName) & ";" & "TABLE=" & rstRef!RemoteTableOrQueryName ' This line causes tdf.RefreshLink line below to FAIL: with runtime error '3001': Invalid Argument
ConnectionString = rstRef(ConnectionStringFieldName)
tdf.Connect = ConnectionString
tdf.RefreshLink
' Display new connection string in debug window
NewConnectionString = tdf.Connect
Debug.Print " "
Debug.Print LocalTableName & ": connection string for linked table: " & LocalTableName & " , HAS BEEN CHANGED TO:"
Debug.Print " " & NewConnectionString
'Debug.Print " "
rstRef.FindNext (SearchCriteria)
Wend
Set tdf = Nothing
Set rst = Nothing
Set dbs = Nothing
End Sub 'ModifyODBCConnectionStrings
---
The above VBA code makes use of a lookup table in the Access database called "Connection Strings" which lists the Local and Remote table names and the new Connection String required.
Table Spec:
ID Autonumber
LocalTableOrQueryName Text
RemoteTableOrQueryName Text
Update Yes/No
ConnectionStringB Text
Simply populate the table, and check the "Update" box for each item you want to update then run the above code.
=====
Another handy VBA function (below) lists all existing Connection Strings in the Immediate window:
Public Sub PrintAllConnectionStrings()
Dim tdf As TableDef
'Dim db As Database
Set db = CurrentDb
'MsgBox "Connection Strings will be printed in the Immediate Window"
Dim sep As String
sep = " -- "
sep = ","
'sep = Chr(9) ' Tab
For Each tdf In CurrentDb.TableDefs
If tdf.Connect <> vbNullString Then
'Debug.Print tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
Debug.Print tdf.Connect; sep; tdf.Name; sep; tdf.SourceTableName
End If
Next
End Sub 'PrintAllConnectionStrings