Update one database from another (1 Viewer)

[vanzie]

Hi everyone

If this is a repost, please redirect me to the relevant one.

I have 2 databases that are on different network share drives. With the one database, we use every day, I want the details of the person (first name, last name, date of birth, etc.) I save to get carried over to the other network drive database so that I don't need to do double work. Is there a possible way to use VBA when I click save or another less complex method?

Both tables have different required fields for the purpose of the database/project but the personal details are very much the same.

Any help will be appreciated

 

[Ranman256]

Can't you link the external table? Linking across servers.
In db2 see db1.tEmployee

 

[Uncle Gizmo]

Or have a third dB containing just the person's details and have that person information table shared between the other two databases.

This way all of your information is in one single table and much easier to maintain.

 

[vanzie]

Or have a third dB containing just the person's details and have that person information table shared between the other two databases.

This way all of your information is in one single table and much easier to maintain.

@Uncle Gizmo - What you said makes a lot of sense. I was actually just more concerned regarding the database used on a daily basis to send that information through to the other database.

Will have a look at linking them. Thanks

 

[vanzie]

@Uncle Gizmo - What you said makes a lot of sense. I was actually just more concerned regarding the database used on a daily basis to send that information through to the other database.

Will have a look at linking them. Thanks

Another quick question...Since these DB's are on a network share drive, if I would like to refer to the DB on the other share drive, how do you write the VBA to find the share drive but each computer will have the drive mapped with a different drive letter?

 

[Ranman256]

do NOT use drive letters.
link using the full UNC path: \\server1\folder1\folder2\myDb.mdb

 

[Pat Hartman]

Saving data in two places is the wrong approach. It is far better to use a single database that is shared by both applications. If you must use two separate databases, make sure that only ONE is updateable. You can never manage the data if it can be updated from both databases.

 

[vanzie]

Saving data in two places is the wrong approach. It is far better to use a single database that is shared by both applications. If you must use two separate databases, make sure that only ONE is updateable. You can never manage the data if it can be updated from both databases.

It's basically one database that updates certain data or records to the other database. The other database only updates its own tables and records. For example, if I save a new record of a persons details from database 1, the second database also gets updated with the same details but if the second database is in use and they save a persons details, it updates that database alone.

If however, I had multiple databases that are dependent on one database information, I would link those databases to the main source.

 

[Pat Hartman]

It's basically one database that updates certain data or records to the other database.

It is only one database if it is actually one database so your statement makes no sense.

If you have offline users, just make a new copy of the database for them every day and they can work with yesterday's data.