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

For information on using MSDE instead of SQL Server, see the tip Database Programming With MSDE.