Import Export Functions with VBA

[Access_Newbie_2]

I have a dB that will be stored on 2 computers....Main computer #1 and a backup copy on Computer #2... These computer are not networked but are located in the same office. Now On Computer #1 the dB will be located [C:/OZRentals/OZRentals.mdb] AND at [C:/Temp/OZTemp.mdb] Users will access the OZRentals.mdb to search and/or add records and make changes to the dB. I need a code statement that will Export 3 tables [Main, Properties, & Landlords] from the Main db to the Temp dB. Then i will use a batch file [copy C:/Temp/OZTemp.mdb A:/OZTemp.mdb] then the user will take the floppy to computer #2 and on there they will have a batch file that [copy A:/OZTemp.mdb C:/Temp/OZTemp.mdb] then i will need a code that will import those same 3 tables to the Main dB replacing the current tables information so the backup copy will then be current with the front office copy. Anyone can think of an "Easy" way to accomplish this as I am not fluent with coding? I have heard about Briefcase Synchronization or something like that that only records the changes but have been unable to find much information on it... Thanks for any help you can offer!

 

[Access_Newbie_2]

Oh forgot to give specs on computers.. Both are running Windows 98 and Access 2000 if this helps..

 

[Opengrave]

Here is a start...

Create a form and add a command button to open notepad. Make sure the comand button is named Command0 (it should be the default). Open the code window and delete the code that is there and paste in the code below.

Option Compare Database

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

    Dim strUpdateDatabase As String
    
    strUpdateDatabase = "C:\Temp\OZTemp.mdb"

    'Transfer the Main table
    DoCmd.TransferDatabase acExport, "Microsoft Access", _
    strUpdateDatabase, acTable, Main, Main
    'Transfer the Properties table
    DoCmd.TransferDatabase acExport, "Microsoft Access", _
    strUpdateDatabase, acTable, Properties, Properties
    'Transfer the Landlords table
    DoCmd.TransferDatabase acExport, "Microsoft Access", _
    strUpdateDatabase, acTable, Landlords, Landlords

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

This should get the files from the current database to C:\Temp\OZTemp.mdb. All you have to do is copy your new form to the C:\Temp\OZTemp.mdb and change the file name in the form and it will then copy from the .mdb on the floppy to the second machine. If you have any problems post them here and I'll try to help resolve them.

 

[Travis]

1. Two network cards, Network cables and a hub

2. Place these three tables into a seperate mdb file and link them to the Main/Front Office MDB file. This would allow you to do a simple copy of the MDB, you won't have to export/import.

3. Look into replication of the Main MDB.

 

[Access_Newbie_2]

Opengrave...

Thanks a bunch for the code.... With a direct copy/paste (took out your comment lines) and on click i get err msg "The Microsoft Jet Database Engine could not find the Object ". Make sure the object exists and that you spell the name and the pathname correctly." Any idea what is causing this?

 

[Rich]

What will happen if both the back and front office have added new Landlords/tenants?

 

[Opengrave]

Re: Opengrave...

Thanks a bunch for the code.... With a direct copy/paste (took out your comment lines) and on click i get err msg "The Microsoft Jet Database Engine could not find the Object ". Make sure the object exists and that you spell the name and the pathname correctly." Any idea what is causing this?

Does it throw you into debug mode and highlight a line? If so post which line is highlighted. I'm not sure but I think the form you created has to be in the backend that has the tables, not the linked front end.

Travis solution #2 above seems like the best solution based on the details you have given, it is simple and pretty foolproof. The only potential problem I can see is with table relationships - I just don't know how that works with multiple back-ends. IMO his idea is way better that the code idea I posted but if you really want the code to work I'll see what I can do to help you debug.

 

[Access_Newbie_2]

Yes it was....Im having the 3 main tables with dynamic data in a seperate empty dB then i have a batch file that adds that db to a .zip then copies the zip to the a:/ drive.. .Thanks all for the help