Table Operation Advise

[kannon]

Hello All - appreciate the good advise that comes from the good folks on here.

In my database I have 5 tables or varying size (fields, records). These tables actually come from our organizations shared database. Very good information that I need. At first I just thought of linking to the shared database but the problem is there are many times when I am off the network and need to still use my database.

So - I tried to do just an import and found out Access doesn't copy over but just adds a "1" or "2" to the table name. Ok - so I came up with the idea of deleting the table first and then importing. Bad idea as I discovered (I learn so much from my errors).

When I deleted the original table all of the relationships were gone. While the shared database table data is much needed, the relationships of the table on my local database are critical.

So I am looking for I guess is an Append query. I basically want to delete all of the records in the Table, maintain the relationships, and then append the table with the latest records from the shared database.

Any words of wisdom how best to do this?

Thank you.

Kannon

 

[plog]

I basically want to delete all of the records in the Table, maintain the relationships, and then append the table with the latest records from the shared database.

In general that's the way to do it. In practice I would set up a temporary table for every linked table you want to treat in this manner. Say these are your actual tables:

A
B
C
D

I would create these tables:

tmp_A
tmp_B
tmp_C
tmp_D

I would then set up a series of queries and code to automate the process. These are the steps:

1. Back up your database. If anything goes wrong in the following steps, you will always have a good clean copy of your latest version.

2. Append queries to move data into the 'tmp_' tables. These queries would move data from the linked database to the local 'tmp_' tables in your database.

3. Run delete queries on the main tables (A, B, C, D). Your deleting the old data, but leaving the table structure in place.

4. Run append queries from the 'tmp_' tables to the main tables. This moves the data from the staging tables to the actual tables your database uses.

5. Run delete queries on the 'tmp_' tables. This will free up space and make your database smaller in size. If you have room to spare, you might not do this step, that way if you screw up the main tables when you're not connected to the network, you will have good, clean copy of the network data.

Additionally, you might think about setting up some validation queries between those steps. In my experience, you can never trust data-- a field that should only have numbers will contain letters, dates that haven't occured yet or occured in the 1500's, negative values for units of measurement, etc. Validation queries can even be simple count queries with field ranges which show the highest and lowest values of certain fields so you know how big the data is and what range it covers--so if table A usually has 1700 records but this time has 3400 you know something might be wrong.

If you are constantly finding bad data in one field, set up a query to look for that type of error before you do all your work import work.

 

[jdraw]

Words of wisdom:

Do some reading on Database Principles and work through this tutorial to understand how to identify tables, how to assign attributes, normalization and set up relationships.

Step back and describe your business/opportunity/issue in 5 or 6 lines. It will help you more than you think. Define each of your tables clearly and concisely.

Research normalization; do a few exercises to see how well you understand the concepts.

Learn a little theory before going down the totally trial and error path.

You definitely should know about import/link/append/delete before getting access to any production database.

Happy New Year.