If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Replace table or table data in a database; keep relationships in t
I have a table that I built queries and all kinds of reports on. The data is
inventory, and it changes daily. I only need to do reports once in a while; I want to completely wipe out all the data in my table and replace it with new data I have exported from our inventory source (it's a csv file). I know if I replace the entire table, all the relationships and queries etc. that were built on the original table will not work properly. However, I tried opening my CSV in Excel, arranging the columns and data types to be exact, and then paste them over the top of the existing records, and it doesn't work . I also clearing out the source table first, and then pasting, but again I get errors. Isn't there an easy way to reuse all the functions (queries; etc) of a database but easily just swap out the data en mass? |
#2
|
|||
|
|||
"Mack" wrote in message
I have a table that I built queries and all kinds of reports on. The data is inventory, and it changes daily. I only need to do reports once in a while; I want to completely wipe out all the data in my table and replace it with new data I have exported from our inventory source (it's a csv file). I know if I replace the entire table, all the relationships and queries etc. that were built on the original table will not work properly. However, I tried opening my CSV in Excel, arranging the columns and data types to be exact, and then paste them over the top of the existing records, and it doesn't work . I also clearing out the source table first, and then pasting, but again I get errors. Isn't there an easy way to reuse all the functions (queries; etc) of a database but easily just swap out the data en mass? You can delete all the data in your table by running a delete query against. The SQL for such a query would be along the line of DELETE * FROM YoutTableName; However, if there are other tables that are related to this table (with defined, enforced relationships), then a record in this table will only be deleted if (a) there is no record in a another table that depends on this record, or (b) the Cascade Deletes option is enabled for the relationship. You don't say in what way the things you'vetried "don't work". The best way to reload the table from a spreadsheet, IMO, is to link to the spreadsheet (so that it becomes a linked table in your database, and then create and run an append query that selects the desired fields from that linked spreadsheet table and appends them to the appropriate fields in the local table. With such a query, you can designate exactly which fields go where, and can specify data-type conversion explicitly where necessary. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Word 2000/2002 - Proper Mail Merge steps for ODBC? | Tony_VBACoder | Mailmerge | 7 | September 2nd, 2004 09:21 PM |
What is MDE | Charlie | General Discussion | 4 | August 24th, 2004 04:15 PM |
Database Window Gone | DaveB | General Discussion | 2 | July 29th, 2004 12:24 AM |