title: Automatically update SQL Server database from tables in MS Access
body: <p>I'm new to SQL Server and trying to automatically update tables in SQL Server from tables in MS Access.  </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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...?</p>
  
  <p>I also looked at this post about writing multiple insert statements but was confused (<a href="https://stackoverflow.com/questions/982568/what-is-the-best-way-to-auto-generate-insert-statements-for-a-sql-server-table">What is the best way to auto-generate INSERT statements for a SQL Server table?</a>). 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.  </p>
  
  <p>If you have any suggestion and a link to easy to follow tutorial I would really appreciate it!</p>
  
  <p>Thanks</p>