I need help with Synchronise by Exporting and Importing database objects?

[slyce]

I'm an access newbie desperately searching for a way to synchronise different copies of the same access 2003 database i created. I have tried brief case synchronisation and replication, and I have also tried the replication/synchronisation 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?

[Access_Issues]

Are you saving them to a blank database then inserting them back into your other database copy? If so, is there any reason you aren't just deleting the tables and importing them straight from the original copy?

[Access_Issues]

When looking in Macro's today, there is an Action called "Transfer Database." This is defined as: Imports data from another database into the current database, exports data from the current database into another database, or links a table in another database to the current database. That might be your best option.

[slyce]

Many thanks to you Access Issues for responding to my post. I came across the transfer database macro after my initial post and it looks very promising, it lead me to discover the transferdatabase method with which I can use Visual basic code instead of the macro. I'm now looking at automating the transfer/export of my tables into a blank database in a drop folder location on pc1 then copying that folder to a similar location in pc2 and then importing the objects into the database on pc2. I hope this is workable? Then my other concern now is how to import and export the changes only, and then being able to substitute other pcs for pc2.

[Access_Issues]

Well, some of our bigger downloads come from an outside ODBC source. We run macros in our database that go something like this...

SetWarnings - No
OpenQuery - Name: The outside ODBC that we have linked tables to...maybe a linked table to the original database for you?, View: Datasheet, DataMode: Edit
RunCommand - SelectAllRecords
RunCommand - Copy
Close - Save: No
OpenTable - Name: A table we have setup just for "DataDrop" View: Datasheet, DataMode: Edit
RunCommand - SelectAllRecords
RunCommand - Delete
RunCommand - PasteAppend
Close - Save: Yes
OpenQuery - Name: An update query, View: Datasheet, DataMode: Edit
Close - ObjectType: Query, ObjectName: Find duplicates, Save: Yes
OpenTable - Name: table you want to update, View: Datasheet, DataMode: Edit
RunCommand - SelectAllRecords
RunCommand - PasteAppend
Close - ObjectType: Table, ObjectName: table you want to update, Save: Yes
OpenTable - Name: The table we have setup just for "DataDrop" View: Datasheet, DataMode: Edit
RunCommand - SelectAllRecords
RunCommand - Delete
Close - Save: Prompt

Not sure how helpeful this is, but perhaps it gives you an idea on how to setup for just new records. Each update I do is based on a specific query of people for specific information such as address (this macro was for our address update). So when it runs this macro it is pulling in address updates just for those records, not everyone.

One approach could be to set something up a "record update" field. Any time you query for new records it would search for the last record update in the old and download anything with newer information? Hope this helps inspire something that will work for you!

[slyce]

Hi, i just saw your post, profound thanks for responding yet again.
This is my first look at a macro with this many lines but i get the drift and I think it's pretty nifty,your suggestion about the "record update" field is brilliant. I'll mull everything over to see what changes i may need to make, i'll be sure to let you know how it goes.
However i'm wondering if using code isn't safer than using a macros, can you shed any light on this? Thanks once again. I'm looking forward to your reply.

[Access_Issues]

I don't know about code being safer than macros. You would need to be careful when testing either way or test in a database copy first. I don't think it would matter in the end which one you use, the trick is just to remember to Open the macro to edit it instead of double clicking on it as I have the tendency to do (since this runs the procedure)!

I have recently eliminated a lot of code that was driving buttons/reports and replaced each with a macro. We've been having performance issues and one of the suggested changes I read was to use macros instead of command buttons with event procedures. When one form has 10 pages of code, macros seemed to make sense to speed it up to eliminate that code running every time the main form was opened or refreshed.

[slyce]

Thanks for replying my post, and for the tips about using macros instead of code. I can't tell you how encouraging your replies to my post have been, I tried other forums before coming here and this is where i got the most helpful response and suggestions,i really appreciate. Like i said i'll give it a try and let you know how it goes in case there's something that might be of interest.