Is it possible to Synchronise by Exporting and Importing database objects? (1 Viewer)

[slyce]

I'm desperately searching for a way to synchronise different copies of the same access 2003 database i created. I have tried brief case synchronisation, and I have also tried the replication manager that comes with access but they both haven't been much help.
My experience with them leads me to believe that they both work best with a LAN or internet connection.
I'm seeking a solution that will work without a LAN or internet connection. Something that will copy my tables and other required objects to a folder that i can copy and move to another pc where another copy of the database resides. It should then import the same tables and objects and hence synchronise the copy of the database.
I recently saw a visual foxpro database that has the kind of synchronisation solution i'm looking for. I think it works this way;it exports all the tables and necessary objects to a folder that it creates allowing you to copy or move this folder via portable storage device to another computer. It then synchronises by importing the tables and other objects from this folder into the database resident on that computer.
I think there might be a way to do this using macro's or VBA but alas my knowledge of these areas of access is very weak. Is there a way to recreate this functionality in access 2003 using VBA code?

 

[RuralGuy]

If this will happen often and you are talking about synchronizing just the tables, then you really need to explore Replication. That is what Replication was designed for and I believe Michka is the resident guru on the topic.

 

[slyce]

Is it possible without replication like in Visual foxpro?

Hey RG, thanks for the suggestion. I've looked at replication, the methods i've explored both employ replication. I was wondering if anyone knows of a way to export the tables to a folder in dbase or another format, then import them into the target database like in the foxpro database i mentioned?

 

[RuralGuy]

There is an ODBC driver for Visual Fox Pro that might let you deal with it directly in Access.

 

[slyce]

Thanks for responding RG, i'm not too familiar with ODBC will it allow me to reproduce the functionality to export all my tables to a folder and import from that folder?

 

[RuralGuy]

You would have to try it but I believe it allows you to treat VFP tables as linked native Access tables.

 

[dfenton]

First off, your database should be split into front end and back end. The tables should be in the back end and everything else in the front end. The front end is fungible -- that is, you can just copy the new one over top of it, with no need to import objects (there's no data in there that the user needs to keep).

The back end is more complicated.

You are correct that replication simply cannot work in a situation where there is never any possibility of an SMB network connection between the two replicas. The only exception to that is briefcase replication, which was designed specifically to allow you to safely replicate between JUST TWO machines. If that's what your scenario is, then do this:

1. split your database by importing all your non-table objects into a new, unreplicated MDB, and then linking to the tables in the old MDB.

2. delete everything but the tables in the old MDB.

3. unreplicate the MDB. Instructions can be found here:

http://www.dfenton.com/DFA/Replicat...it_to_be_replicated_any_more._What_do_I_do.3F

Once you've done that, check that everything works with the front end, then drag the back end to the Briefcase. It will now become a new replica, and you should be able to then exchange data with ONE other PC by this method.

When you change the front end, DO NOT drag it to the Briefcase -- just copy it to whatever media you're using to copy between machines, and then copy it over top of the old one. However, unless you keep your database in exactly the same location on both machines, you will have to relink the tables from the front end to the other back end (using the linked table manager).

This is the only way you can do this without really extensive programming unless you can utilize a Terminal Server or Remote Desktop setup.

 

[slyce]

Dear David,
profound thanks a lot for responding to my post, i'll go over your response carefully and try out your suggestions and let you know how it goes. Thanks once again.

 

[dfenton]

If this will happen often and you are talking about synchronizing just the tables, then you really need to explore Replication. That is what Replication was designed for and I believe Michka is the resident guru on the topic.

No, it's *not* what replication was designed for. Jet Replication REQUIRES at least an occasional network connection (mere dialup will do the job with indirect/Internet replication). When there is no network connection ever going to be available, you simply cannot use replication, because your only option for synchronizing is carrying copies of your editing replicas via "sneaker net" (on floppies, CDs, USB thumb drives, whatever) to another computer and synching there. This completely breaks replication in a way that can eventually lead to completely loss of your replicated project (see http://groups.google.com/group/comp.databases.ms-access/msg/9f5a84194fa6c653 for an explanation of "dead replicas" and the problems they cause)

 

[RuralGuy]

Hi David,
Thanks for jumping in with a much better explaination of replication and the very important warning. I obviously need to do some additional studying on the topic. Thanks again.