0

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?

user2119980
  • 499
  • 4
  • 12
  • 25
  • Google? `Insert Into` – SQLMason Feb 24 '14 at 19:56
  • possible duplicate of [How to do INSERT into a table records extracted from another table](http://stackoverflow.com/questions/74162/how-to-do-insert-into-a-table-records-extracted-from-another-table) – SQLMason Feb 24 '14 at 19:57
  • This seems to be internal to access, it is the right idea. I just need it from Access to SQL Server. – user2119980 Feb 24 '14 at 20:03
  • 1
    Use Access to Link your SQL tables and insert as if they're Access tables. http://office.microsoft.com/en-us/access-help/link-to-sql-server-data-HA102809758.aspx – SQLMason Feb 24 '14 at 20:10
  • Just FYI, SO isn't for "getting started" or "discussion" questions, it's to help with very specific problems with your code. Google is usually a good place to start otherwise. – SQLMason Feb 24 '14 at 20:12

3 Answers3

3

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

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    What if I have a username and password I need to enter? – user2119980 Feb 28 '14 at 19:15
  • 1
    @user2119980 you can replace the `Trusted_Connection=Yes;` parameter with `UID=xxx;PWD=yyy;`. See connectionstrings.com for details. – Gord Thompson Feb 28 '14 at 19:30
  • So like this then: Public Function Update() DoCmd.TransferDatabase _ acExport, _ "ODBC Database", _ "ODBC;" & _ "Driver={SQL Server Native Client 10.0};" & _ "Server=(local)\SQLEXPRESS;" & _ "Database=DATABASE;" & _ "Username=USERNAME;" & _ "Password=PASSWORD;" & _ acTable, _ "CDData", _ "dbo.AC_CDData", _ False End Function – user2119980 Feb 28 '14 at 19:32
  • @user2119980 something along those lines, yes. – Gord Thompson Feb 28 '14 at 19:47
  • I got it working but it keeps prompting me for the login which I thought if I put in the code which I did above it wouldn't ask me for it? – user2119980 Feb 28 '14 at 20:11
  • 1
    @user2119980 If you used `Username=` and `Password=` then I don't think that the ODBC driver will recognize those keywords and hence will prompt you for credentials. Try `UID=` and `PWD=` instead. – Gord Thompson Feb 28 '14 at 20:32
  • @Gordo Thompson - that worked. But now I have an issue of it not overwriting the table that is there. It says the table already exists which I know it does but I just want it to upload and overwrite anyway. SO I need it to delete the table and replace it with the updated one, or just overwrite the table that is there – user2119980 Feb 28 '14 at 20:49
1

I would link/attach to the sql server table you intend to copy to, making sure it has a primary key(so that it doesn't become readonly to access), and then create an access 'Append' query to select data and map them to the columns in sql server.

Writing a macro to then run the query you have written should be trivially easy.

This method will generally work, though can be slow at times with lots of data.

E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
-1

Check Upsizing Wizard for data, indexes, and defaults.

bjnr
  • 3,353
  • 1
  • 18
  • 32
  • This doesn't really seem to address the problem. The upsizing wizard is for moving a database to SQL server, not data to an existing SQL server DB. – Brad Feb 24 '14 at 21:01