CodeItBetter Programming Another VB Programming Blog

'Database - How to Import SQL Server data into an Access database
'You can quickly import SQL Server data into an Access database using an
'Execute statement in either ADO or DAO. I prefer ADO but for the benefit of
'gaining a reference to Access object model I use DAO.

Private Sub ADOGO()
    Dim lcnn As ADODB.Connection
    Dim SQL As String
    'Create ADO Connection Object
    Set lcnn = New ADODB.Connection
    lcnn.Open "Driver={Microsoft Access Driver (*.mdb)};" & "Dbq=DB1.mdb;" & _
        "DefaultDir=d:\Temp;" & "Uid=Admin;Pwd=;"
    SQL = "SELECT * INTO " & "[table_data] " & "FROM " & "[ODBC;Driver=SQL Server; " & _
        "SERVER=MySQL_Server;DATABASE=MySQL_DB;" & "UID=MySQL_User;PWD=MySQL_Password;]." & _
        "[table_data];"
    lcnn.Execute SQL
    Set lcnn = Nothing
End Sub