How can I copy records from Access Table to SQL Server DB

Question

I need to copy data from an access table into sql server. I have thought about just linking the tables but this will not work for what I am trying to do. I need the data to export from the access table to the sql server when I click a macro button. Are there any ideas on how I can get started or where to look?

Answer 1

Your macro could use the RunCode action to run a VBA function similar to this one:

Option Compare Database
Option Explicit

Public Function TransferTableToSqlServer()
    DoCmd.TransferDatabase _
            acExport, _
            "ODBC Database", _
            "ODBC;" & _
                "Driver={SQL Server Native Client 10.0};" & _
                "Server=(local)SQLEXPRESS;" & _
                "Database=myDb;" & _
                "Trusted_Connection=Yes;", _
            acTable, _
            "sourceTableName", _
            "destinationTableName", _
            False
End Function

For more information see

DoCmd.TransferDatabase Method