CODEWITHSUNDEEP
sql-serverms-access
Pankaj
Pankaj

Reputation: 4505

Sync data from MS Access to MS SQL

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.

Upvotes: 9

Views: 13286

Answers (6)

Xyloz Quin
Xyloz Quin

Reputation: 178

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.

Upvotes: 0

GoldBishop
GoldBishop

Reputation: 2861

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.

Upvotes: 1

Márcio Alves
Márcio Alves

Reputation: 52

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.

Upvotes: 0

Matt Donnan
Matt Donnan

Reputation: 4993

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?

Upvotes: 0

Aaron
Aaron

Reputation: 57748

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.

Upvotes: 1

tnktnk
tnktnk

Reputation: 512

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.

Upvotes: 0

Related Questions