Sync data from MS Access to MS SQL

Asked 4/1, 2012 at 17:42 Answered 21/7, 2021 at 16:18

Is there any way so that whenever we insert data in Ms Access table then it will sync in MS SQL table. Table schema will be same in both database.

I have to do something so that data will sync from MS Access to MS SQL because both database are live.

Hoenack answered 4/1, 2012 at 17:42 Comment(3)

Synch, as in short for Synchronize. People will understand what you are asking, but thought I'd mention it because it may impact search results from engines that don't know any better. – Brassica 4/1, 2012 at 17:53

If the data model is the same, and if Access data and SQL data have to be synched, the correct way is to get rid of the Access part of your system. – Bridie 5/1, 2012 at 7:13

A couple of questions: 1. Do you mean many MS Access clients storing local data, then at some point all these local tables get updated to SQL Server table? 2. Who does the synching and how: MS Access/Scheduled Event? This is a very valid question in the context of never having 100% availability to SQL Server or your MS Access client not always being able to connect to network. Please respond in comments so that I can answer you completely. – Peacoat 4/8, 2012 at 15:5

You can write an MS SQL SSIS package to do this. Here's an article on how to set up your AccessDB as a datasource so that you can reach it from within your SSIS package (note that you'll want to read the section on Access 2003 and earlier). And if you've never created an SSIS package before, here is a simple SSIS tutorial.

Belize answered 4/1, 2012 at 17:50 Comment(2)

When data will insert in MS Access then how can we identify in SSIS package that data insert in Access table. I mean this is not one time task whenever data will insert in MS Access then it should be sink in MS SQL table – Hoenack 4/1, 2012 at 17:53

There are a few different approaches here. You can tell SSIS to only move the rows that are not in your SQL Server table, or you can specify your rows by a query, or you can add all of your Access rows into a temporary table on your SQL Server and then do a drop/rename. And of course, you'll have to set it up to run nightly or however often you need it refreshed. – Belize 4/1, 2012 at 17:58

Here is the deal guyz/galz.....he is using Access 2003, all he has to do is do a Linked Table straight to the MS SQL database and be done with the updates have it done directly to the table.

Even if he needs offline available of the table, then he just needs to build in logic to handle "mirroring" on application.open.

Either way, you should really use either JUST Access or MS SQL, but to blend the two for data exchange, is a very Unwise idea.

Yokum answered 22/9, 2012 at 20:38 Comment(2)

In our case, too, we have Access AND SQL Server---we can't just do away with one or other other :( – Seismoscope 27/3, 2018 at 16:24

@PeteAlvin Again...i do not advise anyone setup their environment with Access & SQL....the environment ends up with unknown connections and data outside of SQL...the lack of control of knowing who is, or is not, connecting to the SQL data is a 2nd-level security risk within the organization and should be discussed and resolved as soon as possible. – Yokum 27/3, 2018 at 18:6

If you have access to Access 2010, you can link to a SQL Server table as long as that table has a unique index such as a primary key.

Karyolysis answered 4/1, 2012 at 17:49 Comment(0)

I appreciate your question concerns MS Access first, but as you already have one MS SQL Server, is it not possible to obtain a second and then decommission the Access database, you could then use the purpose built replication manager that goes with SQL Server. If not then you have the option of tackling the issue from both sides, you can either link into you access tables from MS SQL, or visa versa you could link to MS SQL from access, with the second approach your could write a VBA script on a local machine to run as and when desired to complete the sync for you. How often do you need to sync and how much data at a time?

Backwoods answered 4/1, 2012 at 19:29 Comment(0)

I don't know the context in which your application will be deployed, and I'd rather use only the SQL Server database.

Still, if you really need to sync Access and SQL Server, I'd use an Access Project (.adp file format). It's natively connected to a SQL Server database since the moment it's created, and it uses the OLE DB architecture to transmit data, which I consider to be better than ODBC.

You can find more details about ADP files on this Access support page: Create an Access Project.

Hope it might help you.

Uri answered 21/9, 2012 at 23:7 Comment(1)

This won't work in "disconnected" mode since there isn't a local cache of data. Reading the link content seems to imply that an Access project is only a pass-through to a connected SQL Server, rather than a local data store that is synchronized. – Seismoscope 27/3, 2018 at 16:20

Link to the SQL Table using an ODBC Driver.

Set up the ODBC file connection. In Access Ribbon - Get External data

Link to the database and select the table you wish to have a link to.

Make a small form and you are good to go.

Few words of warning on this, MS Access ODBC connections work on an Optimisitic Locking basis so you can have concurrent identical Primary Key additions even if your SQL Database is Pessimistic due to the latency between Access and SQL via ODBC which means in lay terms, if you are not careful ID Corruption can creep in.

I have tested this with a fellow dev using ye olde 3.2.1. GO... countdown test in simultaneous insert and it's not the best at dealing with it so just be aware. You may need to baby sit your SQL backend if you implement this.

Some answered 21/7, 2021 at 16:18 Comment(0)

Recommended topics

Hot tags