Populating tables from another Database (1 Viewer)

[Kamala]

I am hoping someone can help guide me in the right direction...

What I want to do is populate the tables of a production database with select records from a master database.

Currently, the tables I needed from the master are linked in the the production side but it is slowing the system down slightly.

The production side of things does not need all the information that is stored on the master side but we are trying to make it so the person that updates the master side of things does not have to update things twice.

We had hoped to just combine the two but the system slowed down a tad to much for comfort for some people here. They instead said they have to be separate because they don't want any slow down.

I know I will have to use vba and recordsets and understand that concept but I can't quite wrap it around my brain how to transfer it from the master tables into the production tables.

Note: The information can change daily on some accounts. The databases store account information and instructions for processing accounts and is used to store volumes and do billing.

Kam

 

[gromit]

Hi Kamala - Welcome!

Sounds like this could get complicated in a hurry. I have some knowledge but am no expert so I may not be able to help. On the other hand, I may know enough to get you into trouble - so please take care and do plenty of backups before modifing any part of your database!

If I am understanding correctly, you have some data that flows from the master side to the production side. Some of the data entry that is done on the master side needs to update some production data. Is this correct?

Do any changes on the production side affect any of the master data? I.e. do data or changes also flow from the production back to the master side?

You can write VBA code to link to some of the master tables and return the data to the local tables. You need to master recordsets as well as SQL statements so that you can read from one table and write to another.

Of course you then need to consider what you are going to do with that table. How will it get updated? Do any changes need to happen? What happens if the master data changes, how will an update get sent to the production table?

If you have specific questions, I can try to answer them, but as I say, you might quickly exceed my knowledge here as well !

- g