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

Hi,

There is an Access database in the network folder and an exact replica in the local drive.  I want to be able to click a button in VB and have the local Access database update all the tables from the network access database.

Thanks
Tony789Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ee_rleeConnect With a Mentor Commented:
if you want to have an exact copy/replica of the database on your local drive, then you should be using database replication.

here is a link with sample codes:
http://msdn2.microsoft.com/en-us/library/aa140026(office.10).aspx

for more info on replication:

http://msdn2.microsoft.com/en-us/library/aa140024(office.10).aspx

http://office.microsoft.com/en-us/access/HP052167961033.aspx

http://www.trigeminal.com/usenet/usenet.asp?1033

0
 
clarkscottCommented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(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.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Tony789Author Commented:
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?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I do not want users accessing date from this database
Define 'date'
0
 
Tony789Author Commented:
sorry....I meant "data"
0
 
Tony789Author Commented:
Thanks.

I was hoping it would be simple such as below.
INSERT INTO local.table 
  SELECT * FROM network.table;

Open in new window

0
 
ee_rleeCommented:
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.
0
 
Tony789Author Commented:
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

Open in new window

0
 
Tony789Author Commented:
If a local copy of the database does not exist, how can I have it create a database?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
Tony789Author Commented:
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
0
 
Tony789Author Commented:
Please let me know if you have any suggestions.
0
 
loquinCommented:
How do you want to handle it if two users have both updated the same record?
0
 
Tony789Author Commented:
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?
0
All Courses

From novice to tech pro — start learning today.