The concept of updating a database

[hect1c]

I have just re-designed an Access database, normalizing the existing database of 2 tables into 10+ tables. Previously updating the database was easy as you could do a direct import from a spreadsheet to append the data to the tables.



What I want to do is the same thing but now I have to update many tables. I am struggling to come to terms with how I can update data in a relational database.

As I understand it one way would be to create an append query and specify exactly where each corresponding column of data needs to be stored in the database. Is this correct?

Using Excel and VBA I was thinking of setting up a template with columns from many different tables to update the database with the click of a button. Is this a good solution, has anyone tried this and/or have any recommendations?

Thank you!

[June7]

How easy this is depends on the primary/foreign key relationships. If you are using autonumber fields in the redesigned database as PK/FK, then the import will be difficult. If you are using a custom identifier as PK/FK, then import should be quite simple.

Is this import a regular event or just a one-time update? Is the import for only new data or is there changes to existing records? Will it be the same Excel file name for each import? Is the Excel simple enough can set a link to the file? Why must Excel be involved?

Can use the Access import wizard to set up saved import specifications.

If the Excel can be linked can then treat the link like a table (except cannot edit data) and build queries with it, including UPDATE and INSERT SELECT actions.

[hect1c]

-I am using Excel, because in all instances data either originates or is exported to an Excel file.
-Some tables contain auto numbers, but could remove them
-This will be a regular update. I like the concept of updating in bulk rather than entering one record at a time through an Ms Access form.
-I have used Excel more extensively and tend find more flexibility with this application.
Would it be better to use MS Access to update the database? How would I approach or plan this out?

[June7]

All depends where the data comes from and how it ends up in Excel. You said 'exported to an Excel file' - from where?

If someone is manually entering data into an Excel file could just as easily enter into Access database.