1

I'm new to SQL Server and trying to automatically update tables in SQL Server from tables in MS Access.

I have an Access database of metadata that must be kept updated for sending records to other groups. I also have a database in SQL Server which also has these same metadata tables. Currently these tables in the SQL Server database get updated manually by exporting the Access tables as Excel files, and then importing them into the SQL Server tables.

It's not the most efficient process and could lead to errors in the SQL Server database if someone forgets to check that they are using the most recent data from Access. So I would like to integrate some of the tables from Access to my database in SQL Server. Ideally I would like for the tables in my SQL Server database to be updated whenever Access is updated or at least update the tables automatically in the SQL Server database when I open it.

Would replicating the Access tables be the best? I am using SQL Server 2014 Developer so I think I have this capability. From my understanding, mirroring is for an entire database not just pieces of it. However, I do not want to be able to alter the metadata from SQL Server and have it reflected in Access. I cannot tell if reflecting the tables would do this...?

I also looked at this post about writing multiple insert statements but was confused (What is the best way to auto-generate INSERT statements for a SQL Server table?). Someone else suggested importing all the data into SQL Server and then using an ODBC driver to connect the two, but I'm also not sure how this would update the database in SQL Server anytime Access is updated.

If you have any suggestion and a link to easy to follow tutorial I would really appreciate it!

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user562
  • 51
  • 1
  • 4
  • 1
    Rename your local Access tables. Add new [link tables](https://support.office.com/en-us/article/import-or-link-to-data-in-an-sql-server-database-a5a3b4eb-57b9-45a0-b732-77bc6089b84e) in Access from your SQL Server. They will appear as actual tables in your Access database. Then when you monkey with those tables in Access you are actually monkeying with the SQL Server table. Once your data is in sync between your renamed access tables and your linked sql server tables, you can delete your renamed access tables. Your tables in Access are now one-and-the-same as the ones in SQL Server. – JNevill Feb 15 '18 at 19:30
  • Asking for an off-site tutorial is off-topic here, and without one, your question currently is both very broad, and opinion based. However, when you're on an old version of Access, you can just use the upscaling wizard to do most of the work for you. – Erik A Feb 15 '18 at 20:07

1 Answers1

4

In Access, go to 'External Data', ODBC Database, and connect to the SQL Server database directly - make sure you select 'Link to the data source by creating a linked table' on the first page of the wizard. Now, this linked table is available in Access, but is actually the SQL Server table.

Get rid of the local Access tables, using the new linked tables in their place in whatever queries, forms, reports, etc that you have in Access.

Now, any changes to the tables you see in this Access db ARE changes to the SQL Server database.

tysonwright
  • 1,525
  • 1
  • 9
  • 19