Updating local Access database from a network database in VB 6.

There is no Access.Update_All_Tables command.  You would have to open all the tables in both mdbs and update the tables in one from the other.

Scott C

(somewhat redundant, sorry)

Define 'update all the tables from the network access drive'.

If tables in both apps are local, then you'll have to either delete/append from one into the other, or delete one file and copy from the other.
If tables in one of the apps are linked to the other, then when the user opens the app it will reflect all data in the other app.

There is an Access database in the network drive.  I do not want users accessing date from this database due to slowness and network congestion.

I have created an Access database with the exact same table names on the local drive.  I want to create a button in VB application that updates the local Access database with data from the network drive.

Can you provide me with an UPDATE statement that updates tables on the local Access database?

>I do not want users accessing date from this database
Define 'date'

sorry....I meant "data"

Thanks.

I was hoping it would be simple such as below.

INSERT INTO local.table 
  SELECT * FROM network.table;

Select allOpen in new window

there are codes you can use on the first link in my prev post. i just thought that this is the easiest and most efficient way to sync 2 tables. actually, the database replication is designed for such cases.

it might be more complex in your method because you should be able to determine which records are updated and which are added.

Will the below code work?  If so, how can I accurately tell the user the database has been updated?

Private Sub Update_Click()
 
DoCmd.TransferDatabase transfertype:=acExport, databasetype:="Microsoft Access", databasename:="C:\Test.mdb", objecttype:=acTable, Source:="Table1", Destination:="Table2", structureonly:=False
 
End Sub

Select allOpen in new window

If a local copy of the database does not exist, how can I have it create a database?

>Will the below code work?
No.  All DoCmd.TransferDatabase does is copy one mdb and create a new mdb out of it.

>If a local copy of the database does not exist, how can I have it create a database?
Easiest way is just to have 'template' mdb, with the same schema (i.e. tables, queries) with no data, and then just copy that file into another location.

You need to spell out your definition of 'update' in excrutiatingly clear and non-technical terms.
The above experts have commented on linked tables and replication, and unless you paint us a picture that your business needs are substantially different from the norm, either of these should work for you.

Sorry for the late reply.

I have a VB 6 appliation where it pulls data from a LOCAL access database.  The LOCAL access database is a copy of the NETWORK access database.  I want to have a button in the VB 6 application that when clicked will updated the LOCAL access databases with data from the NETWORK access database.  I do not want users to open access and choose replicate each time.  I want it done through the VB 6 application.

Thanks

Please let me know if you have any suggestions.

How do you want to handle it if two users have both updated the same record?

loquin,

I don't know.  I assumed Access will have a way of dealing with it.

When I am updating the local database with the network database, the VB application completely turns to a blank white screen for about 10 seconds.  This is due to the number of records that has to be updated.  How can I prevent this from happening?