'----------------------------------------------------------
' STANDARD Module : MySQl_to_acc
'----------------------------------------------------------
Option Explicit

Function MySql2AccTable() As Boolean
    ' Referencje
    'ADODB: Microsoft ActiveX Data Objects 2.X Library
    'ADOX: Microsoft ADO Ext. 2.X for DDL and Security
    ' w zasadzie nalezaloby bez refrencji CreateObject, pozne wiazanie
    ' sprawdzac tylko wersje Jet w obiekcie Connection
    On Error GoTo MojErrHand_Error
    '----------------------------------
    ' ponizsze to pewnie u Ciebie parametry
    Const strLinkTblName As String = "tblMySqllnk"
    Const strMySqlTable As String = "osoba"
    Const strAccTable As String = "tblxxxx"
    '----------------------------------
    Dim Conn As ADODB.Connection
    Dim catDB As ADOX.Catalog
    Dim tblLink As ADOX.Table
    Dim strExecSql As String
    Dim lngIle As Long
    '----------------------------
    Set Conn = New ADODB.Connection
    With Conn
        .ConnectionString = MyConnectionStringToMdb
        .Open
    End With
    Set catDB = New ADOX.Catalog
    With catDB
        Set .ActiveConnection = Conn
        On Error Resume Next
        ' usuniecie linkowanej tabeli jezeli jest
        ' byc moze jednak w ogole wystarczy miec linkowana tabele
        ' i nic wiecej lecz nie znam problemu w calosci
        .Tables.Delete (strLinkTblName)
        If Err.Number > 0 Then Err.Clear
        On Error GoTo MojErrHand_Error
    End With
    Set tblLink = New ADOX.Table
    ' stworzenie tabeli linkowanej do tabeli MySQL
    With tblLink
        .Name = strLinkTblName
        Set .ParentCatalog = catDB
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link Provider String") = MyConnectionStringToMySql
        .Properties("Jet OLEDB:Remote Table Name") = strMySqlTable
    End With
    With catDB
        .Tables.Append tblLink
        .Tables.Refresh
    End With
    ' tu wobec podlinkowanej tabeli można zdawać zapytania
    ' wtedy mamy tylko zapytanie do tabeli ACC
    ' dolaczenie danych z linkowanej tabeli do docelowej
    strExecSql = "INSERT INTO " & strAccTable & " SELECT * FROM " & strLinkTblName
    Conn.Execute strExecSql, lngIle
    MySql2AccTable = True

MojErrHand_Exit:
    On Error Resume Next
    Set tblLink = Nothing
    ' usuwamy tabele linkowana aby nie bylo powiazania kompaktowac czasem mdb
    If Not catDB Is Nothing Then
        catDB.Tables.Delete (strLinkTblName)
        Set catDB = Nothing
    End If
    If Not Conn Is Nothing Then
        With Conn
            If .State = adStateOpen Then .Close
        End With
        Set Conn = Nothing
    End If

    If MySql2AccTable Then
        MsgBox "Dane do tabeli bazy danych ACC przekazano, liczba dodanych rekordów :" & CStr(lngIle)
    Else
        MsgBox "Eksport danych do bazy zakończył się niepowodzeniem! ", vbExclamation
    End If
    Exit Function

MojErrHand_Error:
    MsgBox "Błąd - " & Err.Number & vbCrLf & "Opis - " & Err.Description & vbCrLf _
           & "Procedura - " & "MojErrHand", vbExclamation, _
           "VBAProject - MySql2AccTable"
    Resume MojErrHand_Exit
End Function

rs.Open "select * from tabela", "DSN=baza1", adOpenStatic, adLockOptimistic
    ' Range("A1").CopyFromRecordset rs
    
    Dim conn As ADODB.Connection
    Dim rec As Recordset
    rec.<insert from rs>