Discussion Locked

access 97 updates

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
several excel files (which contains data downloaded from AS400 database, and is updated everyday) are linked tables in Access, tables with exact identical data from the linked tables were created (so that primary keys can be created), relationships were created between these tables, how can Access automatically update the data in these tables without losing the relationship?, or how can access prevent Appending the new data to the existing tables

4 replies

by mslizny

I think you have solved one problem but created another. You linked the Excel files to Acces, and then created matching tables with identical data so that you could have primary relationships in the table structure of Access. At this point you stepped outside the "linked" structure so that you could use features of Access that only apply to Access tables. Once outside the "linked" structure, you cannot go back to an unlinked status very easily.

As I understand it, you are interested in getting updated information into the "unlinked" tables. I believe you could write a VBA procedure that would re-create the "unlinked" tables and their relationships each time new data comes into the Excel files, or perhaps even get it done with a macro. However, there is nothing otherwise available in Access 97 to accomplish the result you are seeking.

by zapeener

found an answer, by automating the imports (via macros) of excel data to already existing table, but first the data that resides on the table must be deleted first before the importing can be done

by mslizny

Here are a few more questions for you:
Are the primary keys you created based on information from the Excel tables? If so, the updating process shouldn't append new information unless there are new entries. This would preserve the relationships you have created.
Are you trying to put the new information in the tables with an Update Query? I believe this would be better for your purposes than using some Append process.

This discussion is closed to new replies.