Exporting Data from an offline Access database to SQL Server

Question

Exporting Data from an offline Access database to SQL Server

Dale Corey

SSCrazy Eights

Points: 9019
More actions
February 3, 2005 at 5:22 am

#185050

Hi folks,
We have a new challenge at work. We will now be supporting laptops out in the field which will not be able to access the SQL server back end.
We have been asked to design a front end Access interface which stores data in an Access back end. Then, we need to export the data from the Access back end to SQL server, and at the same time, ensure that locked records are not overwritten on the SQL server.
Has anyone had experience with this type of application? If so, how did you approach the problem and solution?

All the best,

Dale
Author: An Introduction to SQL Server 2005 Management Studio

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

I have done this type of application, but it has been several years ago.

Link to SQL tables from Access. Set up queries that import from the Access tables into the linked SQL tables. You might want to set up a staging database in SQL for the imported records and then import them in a batch after hours. If you are only doing inserts, you shouldn't have any problems with locked records. Give the user a button to press when they are in the office that runs a subroutine that executes the queries.

Another approach that might work is to install SQL personal edition on the laptops and use SQL merge replication. The Access tables would link to the SQL tables. When the Access application is launched, check to see if the network is available. Modify the connection string so that the Access tables are linked to the correct SQL Server instance.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 2

Thanks Kathi,

I appreciate the input!

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

philcart SSC-Forever Points: 47796 More actions · Answer 3

Given that the laptops will not be able to access the SQL Server, what is the definition of a locked record?

I would use MSDE (Is that what you were meaning Kathi?) then use replication to merge changes made in the field with the main database.

http://support.microsoft.com/default.aspx?scid=kb;en-us;324992

--------------------
Colt 45 - the original point and click interface

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

MSDE would work. There is or at least used to be a personal edition. It's mentioned in BOL, but I don't have the latest BOL.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Aldex Mr or Mrs. 500 Points: 594 More actions · Answer 5

We developed a system to do something similar a couple of years ago.

Situation: 100 roving salemen with laptops producing estimates & quotes offline. Data needed to be sent back to a central location and any updates (eg new price lists) sent back to the salesmen.

Solution: Central SQL Server 2000 database, MSDE+VB6 front end (but could have been an Access front end) on the laptop. Bi-directional Merge replication used to keep it all in synch.

Complications: When certain parameters (eg discount levels) are breached then a quote has to be sent back up the chain of command for approval. This required stauts levels being associated with a quote and some fairly sophisticated custom conflict resolvers to be developed to make sure that the correct changes were applied.

A second complication was the need to filter the data so that a saleman only recieved their own data, their area manager got all data for people who reported to them (but not salemen who reported to other area managers, etc.).

After we sorted out the complications it now works well with very few problems.

HTH

David Saville

Aldex Software Ltd.

http://www.aldex.co.uk

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 6

Wow! Great remarks! I appreciate the feedback.

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

joemai Hall of Fame Points: 3496 More actions · Answer 7

I know that Access has an option called briefcase/replication to deal with your issue. I've not tried it yet in Access, but the replication in SQlserver worked for me.

Minh Vu

Dale Corey SSCrazy Eights Points: 9019 More actions · Answer 8

What is the difference between briefcase/replication and merge replication?

All the best,

Dale

Author: An Introduction to SQL Server 2005 Management Studio

Aldex Mr or Mrs. 500 Points: 594 More actions · Answer 9

Dale,

With regards to Briefcase Synchronisation the words "bargepole", "15 foot", "touch" and "don't" all spring to mind.

David Saville

Aldex Software Ltd.

http://www.aldex.co.uk

philcart SSC-Forever Points: 47796 More actions · Answer 10

David

I think that should be "30 foot"

--------------------
Colt 45 - the original point and click interface