General discussion

Locked

access 97 updates

By zapeener ·
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

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

access 97 updates

by mslizny In reply to access 97 updates

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.

Collapse -

access 97 updates

by zapeener In reply to access 97 updates

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

Collapse -

access 97 updates

by mslizny In reply to access 97 updates

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.

Collapse -

access 97 updates

by zapeener In reply to access 97 updates
Back to Windows Forum
4 total posts (Page 1 of 1)  

Operating Systems Forums