Using one table to update many

[WillM]

Not sure how to even ask the appropriate question (don't know what I don't know), but I need help.

We have three text files that are pushed to us every night. We currently have 8 different databases (some are significantly smaller than others) using the same the three sources tables in addition to other db specific information.

What we would like to do is update these databases with local copies of the files every day.

We have brainstormed and "think" that we want to export these files into the backend of these various databases, but we run into problems with relationships between the tables.

We also thought about possibly trying to either just use an append query to change the data or import to a temp table and insert the data via macro into the regular tables.

I have searched and searched and have not been able to find an answer to this problem.

1. Is it possible to macro/code the breaking and re-establishing of relationships via the visual basic/sql side of Access 2010? For example, when the export macro ran from the sourcedb, it would open database1, save the relationships, break them, update the information, re-establish the relationships, close the db, and so on until it was through with all 8 databases.

2. Is there a better/easier way to do what we are trying to do? Or are we better off just linking to the 3 files? As a side note, we never, ever, change the data of those three files. They are only for look up.

3. Right now, we are stuck with Access 2010, so our options are limited.

Thank you in advance for any help!

 

[vbaInet]

I don't think I understand your question fully but here goes.

First of all breaking/re-establishing relationships shouldn't be an option. There are parts or fields that should meet the databases' requirements (i.e. the Primary keys), identify and upload those keys first then upload the rest of the file.

 

[raider34]

Agree^; don't start jacking w/ relationships.
I am certainly no expert, but here's my advice: establish a unique identifying field within your 3 daily datatables, such that they relate to your pre-existing dB fields.

Then you can repeatedly import the data via linked-table, write append & update SQL queries to link to your master tables, then house them inside an AutoExec macro so your tables are instantaneously sync'd.

 

[WillM]

Right, the tables are the exact same, only with more (rows) information as the new files are pushed to us every night. The issue we are running into is that the database throws an error when we try to overwrite any of the fields due to the relationships already in place in the database.

We have many databases that use these same three files, but they are all different databases so consolidating them down, for now, is not a possibility.

Our first inclination was to delete the old copy and replace it with the new copy every morning, but the relationships of those tables in their current database causes that to not work. That is why I was asking about a macro/code to save, break, and re-establish the relationships between tables in their current dbs.

We then thought about just automating append queries to the tables in their current dbs as well, and that looks like it would work. I just don't know if there is a "downside" to doing an append query that I don't know about or just haven't seen yet. Any advice on that would be appreciated as well.

The last thing we thought of was removing the relationships from the tables and establishing them through queries and then using a make table query that would replace the tables, but would avoid the relationship issue at the table level. Again, are there any consequences to doing that?

We want to move these databases into SQL in the near future, so best practice toward that end going forward is what we want to have in mind while we make these changes.

Thanks again.

 

[pbaldy]

Not sure why but raider34's post was moderated. I just approved it.

 

[vbaInet]

@pbaldy: Strange Paul. Perhaps some sort of rules setup to capture new users who post in old threads without creating one of their own first.

@WillM: What exactly is the set up of the relationships? Are you trying to update a table that has one or more parent dependencies? If that's the case then it will be a matter of performing some data cleansing and in which case I can suggest the following three options:

1. If you don't have Cascade Update setup between the parent and child table: start from the root table, find those records in the parent table containing the IDs, update those IDs and insert the new ones, update the primary table IDs, update the records matching those IDs, and finally insert the new records.
2. If you have Cascade Update setup: start from the root table, find and update those record IDs, insert new ones, update your primary table with records matching those IDs and insert the new records.
3. If it's a full replacement you're doing and Cascade Delete/Update is setup: start from the root table, find and update the matching record IDs and delete them, then insert the records into your primary table.

For any of these options you will need some staging tables that you'll simply import into then create queries to perform the checks, updates and inserts as explained.

Relationships are there to ensure and enforce data integrity. You can't guarantee that the records contained in the new file meet all the requirements.

 

[WillM]

Thank you for the advice!

 

[vbaInet]

Let us know how you get on and if you come up with any other ideas!

 

[WillM]

We ended up importing the text files into tables in a stand alone database. We then use Windows Scheduler to append the file to the tables. We saved our import specifications so that we could preserve the table names and it seems to be working very well.

So far, so good!

 

[vbaInet]

That's good to hear WillM.