Help with appending data into Access from Mysql Database

[acemillion]

Hello,
I have a local access database which I would like to be able to append data from a network MySQL database. I've searched around the discussions and so far have not seen a full macro that does this operation, but only bits and pieces of it.

So far this is what I have pieced together:

Sub zero()
Dim strDatabaseName As String
Dim strDBCursorType As String
Dim strDBLockType As String
Dim strDBOptions As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
 

Dim b As Long
strDBCursorType = adOpenDynamic  'CursorType
strDBLockType = adLockOptimistic   'LockType
strDBOptions = adCmdText         'Options
 
Set cn = New ADODB.Connection
 
cn.Open ConnectString()
 
    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
 
    Set rs = New ADODB.Recordset       'Creates record set
 
    strSQL = "select nw.repid, nw.start from northwind nw where nw.start = '2012-11-27' "
 
    rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
 
 
If rs.EOF Then
   GoTo ExitSub
Else
    For b = 0 To rs.RecordCount - 1
    '<do whatever you need to do with the data here>
    Next b
End If
 
ExitSub:
 
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
 
On Error GoTo 0
Exit Sub
 

End Sub

Private Function ConnectString() As String
Dim strServerName As String
Dim strDatabaseName As String
Dim strUserName As String
Dim strPassword As String
 
    'Change to IP Address if not on local machine
    'Make sure that you give permission to log into the
    'server from this address
    'See Adding New User Accounts to MySQL
    'Make sure that you d/l and install the MySQL Connector/ODBC 3.51 Driver
 
strServerName = "xxxxxx"
strDatabaseName = "xxxxxxxxxx"
strUserName = "xxxxxxxxxx"
strPassword = "xxxxxxx"
 
ConnectString = "DRIVER={MySQL ODBC 5.1 Driver};" & _
                "SERVER=" & strServerName & _
                ";DATABASE=" & strDatabaseName & ";" & _
                "USER=" & strUserName & _
                ";PASSWORD=" & strPassword & _
                ";OPTION=3;"
 
End Function

How do I get this to append to a table that I have in Access?

Thanks,

 

[mdlueck]

Given the fact that you have available a SQL BE DB, I would send the SQL up to that box and have it process the SQL / only return the matching rows to Access.

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

This solution works well to download multiple times and if the Access table is not cleared out, old/new records will be merged/retained in the Access table.

P.S. You will need to come up with a working ODBC connection string to connect to the server in order to send the Pass-Through query to it. Google will be your friend about locating the correct syntax to connect to your specific MySQL DB. Also, I am aware of peers in this forum who work with Access / MySQL so perhaps they have helpful pointers.

 

[TJayy]

I would recommend linking the mysql tables to your access database, then you can just do a simple append query...

 

[mdlueck]

I would recommend linking the mysql tables to your access database

Unless that is too large a security hole for the OP.

Easy, yes, at a price.