Re:solution - Using Microsoft Access as a Front-end to MySQL

[Dave Schomburg]

Here's the code for appending a local access table into a remote mysql server when a link table won't work (error #2006)
----------------------------
Dim localtablefield1 As String
Dim localtablefield2 As String
Dim localtablefield3 As String

Dim db As dao.Database
Dim tdf As dao.TableDef
Dim rs As dao.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("myaccesstable")
rs.MoveFirst
Do Until rs.EOF
localtablefield2 = rs!field1
localtablefield2 = rs!field2
localtablefield2 = rs!field3


myodbcconn = "Driver={MySQL ODBC 5.1 Driver};Server=xx.xx.xxx.xx;Port=3306;Database=mysqldatabase;User=myuser; Password=mypassword;Option=3;"

Dim adoConn As ADODB.Connection

Dim adoRS As ADODB.Recordset

Set adoConn = New ADODB.Connection

Set adoRS = New ADODB.Recordset

Dim strSql As String

strSql = "INSERT INTO mysqltable(mysqlfied1, mysqlfied2, mysqlfied3) VALUES ('" & localtablefield1 & "', '" & localtablefield2 & "', '" & localtablefield3 & "');"

adoConn.Open myodbcconn

adoRS.Open strSql, adoConn

Set adoRS = Nothing

Set adoConn = Nothing
rs.MoveNext
Loop

MsgBox ("INSERT COMPLETE")
----------------------------------

Hope this helps someone.
Dave