Replace table or table data in a database; keep relationships in t

M

Mack

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?
 
D

Dirk Goldgar

Mack said:
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?
Click to expand...

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.
 
Top