How to synchronize data with off-line db

[Janis Rough]

What do you do if you have a project database on a server but you have field
technicians recording data at a site where they are not connected to the
company intranet? So now they record data on an Excel spreadsheet on a hand
held or laptop that is not connected but Isn't there a way to synchronize
the data? Do you write stored procedures or update queries to pull in the
data from a local copy of the access database once they are back on line?

 

[Guest]

Hi Janis

This is taken from an answer I gave to a simaler probelm a while ago.

______________________________________________

DO NOT use a Kill code unless you know what you are doing or you will be
(really) sad. Once it’s gone (your database) it’s gone ??
______________________________________________

I have assumed that you want to keep/upgrade a DB on a drive called "C"
You are taking the upgrade from a drive called "E".
Change the drives to what they areally arte and all the other paths and
sections.


Simply create a form (on the machine you want the upgraded DB to be on) and
a button on it and use this OnClick.


Private Sub ButtonName_Click()
Dim strOldPathName As String
Dim strNewPathName As String


'Delete the old file'
Kill "C:\Documents and Settings\My Documents\DBName.mdb"


'Copy external database to local drive'
strOldPathName = "E:\DBName.mdb"
strNewPathName = "C:\Documents and Settings\My Documents\DBName.mdb"
DBEngine.CompactDatabase strOldPathName, strNewPathName


End Sub


E:\ is a DataStick but this works from/to any drive. Not too sure about
CD/RW or CD/R though.
I have used compact DB at the end as I feel it needs it (not for any other
reason) so this is up to you. Change the path names to what they are and you
up and running.
________________________________
It may be that you want to check that your DB really has transferred across
before you start using it so in this case you could import and use any name
then after you’re happy with it, simply rename the new DB (run the kill code
just before the rename or it will not work)


'Rename the copied DB to another name'
Dim OriginalName, As String
Dim NewName, As String
OriginalName Name = "C:\Documents and Settings\My Documents\DBName.mdb"
NewName = "C:\Documents and Settings\My Documents\SomeNewNameHere.mdb
"Name OriginalName As NewName


Note I have not included a check to see if the DB exists on the laptops as
it always does but you could include this if you feel it ness.

Hope this helps

 

[Larry Daugherty]

Your best bet is probably to continue with an Excel file. Work on an
import routine such that, once in play, you can click a single button
to gather the data and get it into the appropriate Access table(s)

Once you have done that you and that tech agree on the formatting of
the Excel file. If necessary, supply a template that you create.

There's a more complex process available with Access called
Replication. It sounds easy but gets more complex and would require
knowledgeable service. I recommend against it in the case you listed.

HTH

 

[David W. Fenton]

There's a more complex process available with Access called
Replication. It sounds easy but gets more complex and would
require knowledgeable service. I recommend against it in the case
you listed.

The described situation would be perfect for the simplest form of
Jet replication, direct, and requires very little code or
maintenance.

It's when you want to synch across something other than a wired LAN
connection that the difficulties arise.