Importing Tables

[Guest]

I have 5 tables I need to bring over into Access for a report done every two
weeks. This data starts in Impromptu and I've been using Excel to import them
into Access. I have found that in order to update the data(re import it) I
have to delete the existing relationships and reset up the indexes, captions
and primary keys. Is there a better way to do this? I've tried to Link the
tables but can't change the indexes in the linked table so I can build
relationships. Thanks

 

[Guest]

In a relational model, deleting related records can be problematic.

Rather than importing directly into your "live" tables, a better solution
would be to import to an existing table so you can define all the field types
and sizes, then create some append queries to read from that table and add to
the "live" tables.

If it is necessary to delete the old data prior to loading new data into the
"live" tables, you will need to ensure you have Cascading Deletes and Updates
set up correctly in your relationships.

 

[John Vinson]

I have 5 tables I need to bring over into Access for a report done every two
weeks. This data starts in Impromptu and I've been using Excel to import them
into Access. I have found that in order to update the data(re import it) I
have to delete the existing relationships and reset up the indexes, captions
and primary keys. Is there a better way to do this? I've tried to Link the
tables but can't change the indexes in the linked table so I can build
relationships. Thanks

It's not necessary to index the Excel spreadsheets - which is a good
thing, since Excel spreadsheets do not HAVE indexes!

You can link to the spreadsheet and base an Append query on the linked
data. It is not necessary to change any indexes in order to do so.

If you need to *update* existing records based on the data in Excel,
then you may indeed need to import into a temp table (by deleting all
records and then appending, rather than by deleting and re-importing
the table) and run the update query from that temp table. But in
general, this should not be required.

John W. Vinson[MVP]