Anyway to update queries and forms after changing table structure?

[TSimonick]

Hi,

I'm working on someone else's database and trying not to change the tables, but they are not normalized and I need to regularly import .csv files. I can use an append query to add the new records into a temp table, but I need to copy the values from the the temp table into the the correct fields of the existing table. Can someone tell me how to do that?

If I create a new table and pull fields out out of the existing table it makes it easy to append newly imported data, but then all references for the forms and queries are wrong. Is there a utility that will run through forms, queries and reports and update them since fields have been moved to the new table?

Thanks for any help.

Tom

 

[JeremyNYC]

Tom,

It's probably best to shift your strategy a bit here. Constantly creating and deleting new tables will definitely lead to file bloat, which you'll have to fix by compacting the database regularly. As you've seen, it also leads to some other problems.

Why not link to the csv file instead of importing it? That way you don't bloat your file nearly as much, and you should be able to do what you need from there.

I don't understand this statement: "If I create a new table and pull fields out out of the existing table it makes it easy to append newly imported data". Why do you have to do this in a new table, and what do you gain by pulling fields out of the existing one (and does that mean you delete some fields?)?

It should be fine to append to a table that has more fields than the one from which you're appending, as long as your append query puts data in the PK and any other required fields.

Jeremy

=============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[TSimonick]

Jeremy,

I can't link to the file because it is may be deleted and it is always being overwritten with new data. Also, I need to clean up the raw data with some parsing, which I do in the append query stage. The concept I can't grasp is if I keep the current table and use an append query I'm just adding hundreds of additional rows with lots of empty fields. Resulting in:

Customer#1, a,b,c,d,x,y,z,e,f,g,h

where fields x,y,z are empty, then append a new record:

Customer#1, x,y,z, which have data

The only link between the records is the Customer#. So now I have two records with the same fields in the same table. How would I query the two records to create a report based on:

Customer#, a,c,x,y,z,f,g

If the imported data were in its own table, then the link would be Customer# and I could use the data from x,y,z.

I would appreciate more of your help with my lack of understanding.

Thanks for your time!
Tom