Update records in another database with VBA

[Andre B]

Dear all,

I've 2 databases, 1 on the C-drive and the other on a networkdrive.
Both databases have the same tables.
Now I want to update the tables of the databaase on the networkdrive with the data in the tables which are stored in the database on the C-drive.

I want to do this with VBA, but I don't know how to do that.

Is this possible?

thanks in advance,

regards,

André

 

[isladogs]

Welcome to AWF.

Link the network tables to your local database. Run an update query.

But why do you have 2 sets of identical tables. One you've synchronised them, suggest you delete the local tables and just save data to the network tables

 

[vba_php]

I've 2 databases, 1 on the C-drive and the other on a networkdrive.

agree with Colin. why do you have duplication like that between 2 files? is this a contingency or security measure of some kind you've implemented due to internal issues or moves being made at your company?

 

[Andre B]

Hi,

the database on the C-drive is on a laptop which is used by field employees.
So when they came back in the office, they have to synchronize the data on the laptop with the database on the server, so then the database on the server has the most updated data.

I know the possibility to link the tables and use an update query, but I want to solve it via VBA without linking the tables in the local database.

regards,

André

 

[theDBguy]

Hi,

the database on the C-drive is on a laptop which is used by field employees.
So when they came back in the office, they have to synchronize the data on the laptop with the database on the server, so then the database on the server has the most updated data.

I know the possibility to link the tables and use an update query, but I want to solve it via VBA without linking the tables in the local database.

regards,

André

Hi. You can use VBA to temporarily link the server tables or do it directly in your queries. Here's the syntax:

INSERT INTO LocalTable (FieldName) SELECT FieldName FROM "X:\NetworkShare\DatabaseName.accdb"

Hope that helps...

 

[Deleted member 147267]

I presume you want the network db's tables to be stand-alone, so link the laptop tables to the network?

Whichever way you do this would require making a connection to the source data. If not via linked tables, then you'll have to use ADO but it seems unnecessary work. You can still use code to run the updates regardless of which way the link is formed, but that also seems like more work than is necessary (as opposed to using stored queries). Not sure if the real question is how to perform the updates when it gets to that point or not. If it is, then you'll have to use a mix of append sql if there can be new records and update sql to ensure changes are propagated. Since replication is no longer, the easiest would be to update every field regardless of whether or not there is anything to update.