Transfer / Append Table Data Frm Local to Network Db

[Plukey]

Lets just say you have a 5 man team out in the field that isn't on the server. And at the end of the day we use a update/append code that updates their work to a Main/Back End Database on your Network.
Something like the code below, ... I'm open to ideas... or someone convince me its a waist of time, or cant be done. The code below just deletes and copies over...Not good, because there will be a crew in the office working on the network as well.. I only listed 6 Tables but, there's actually 15 total tables.

Sub UpdatePublicDatabase()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
On Error GoTo UpdatePublicDatabase_OpenError
con.Open _
        "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
        "Dbq=C:\Users\SHF00\Desktop\Database27_Backup's\Database271_be.accdb;" & _
        "Exclusive=1;" & _
        "Uid=admin;" & _
        "Pwd=;"
On Error GoTo 0
con.Execute "DELETE FROM SRD"
con.Execute "INSERT INTO SRD SELECT * FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[SRD]"
con.Execute "DELETE FROM SDI"
con.Execute "INSERT INTO SDI SELECT * FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[SDI]"
con.Execute "DELETE FROM SSDR"
con.Execute "INSERT INTO SSDR SELECT * FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[SSDR]"
con.Execute "DELETE FROM MCF"
con.Execute "INSERT INTO MCF SELECT * FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[MCF]"
con.Execute "DELETE FROM TVD"
con.Execute "INSERT INTO TVD SELECT * FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[TVD]"
con.Execute "DELETE FROM WPS"
con.Execute "INSERT INTO WPS SELECT * FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[WPS]"
con.Close
Debug.Print "Done."
Exit Sub

UpdatePublicDatabase_OpenError:
Debug.Print "Exclusive 'Open' failed. Quitting."
Exit Sub

End Sub

 

[Micron]

All that deleting would scare me, especially if not using Transactions. Can you not just run append and update queries?
If you run updates and there are no changes, then nothing changes. If you run appends and records would be duplicated then as long as you have created an index(es) to prevent that, the append fails. How you handle that is determined by the requirements. You might use transactions to undo the entire batch of records. Or you might simply turn off warnings for the append part of the code. However, I would not advise that unless you write a proper error handler to deal with that possibility.

You probably would not notice any speed advantage, but every time you repeat a reference like Application.CurrentDb.Name it has to be resolved. Pass it once to a variable and the value is retained in memory and pulled from there. More efficient.

Not sure where this source db is and why you need to use ADO to connect to it. Can you not just link to its tables?

 

[Plukey]

All that deleting would scare me, especially if not using Transactions. Can you not just run append and update queries?
If you run updates and there are no changes, then nothing changes. If you run appends and records would be duplicated then as long as you have created an index(es) to prevent that, the append fails. How you handle that is determined by the requirements. You might use transactions to undo the entire batch of records. Or you might simply turn off warnings for the append part of the code. However, I would not advise that unless you write a proper error handler to deal with that possibility.

You probably would not notice any speed advantage, but every time you repeat a reference like Application.CurrentDb.Name it has to be resolved. Pass it once to a variable and the value is retained in memory and pulled from there. More efficient.

Not sure where this source db is and why you need to use ADO to connect to it. Can you not just link to its tables?

We defiantly don't want to delete. That scares me as well... as previously stated the source db would be local & the users would be offline, and at the end of the day we'd run the update to the main source that's online over the network. I've seen something like this before where I work. The guy had it setup that way because it was a missive and a lot of traffic. The user would click a update button and I guess it did some sort of transfer. Just checking if anyone here had something in place like that or anything similar.