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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
ee_rleeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.