MySQL Forums
Forum List  »  Microsoft Access

Re: ODBC 4-byte UTF8 error and MS Access 2010
Posted by: Graham Gunn
Date: November 01, 2012 06:55PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
18788
September 23, 2012 02:43PM
Re: ODBC 4-byte UTF8 error and MS Access 2010
8139
November 01, 2012 06:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.