Using query to make a table

[Jael]

I have a table that's linked to a xxx.cvs file. I use a query to make a new
table with modified data in it. The first time I use the query, it works
fine. The second time, it delets the table then gives me an numeric overflow
message. If I open the query and and modify any of the fields (e.g. delete
the xxx* and then restore it), it Runs OK. I have two other querys, one that
makes a table and another that appends to this table. No problem.
I'm using access 2003 with vista ultima.

I understand (I think) what the numeric overflow is, but I can't find it.
I've tried one filed at a time (there are 40 fields) but individually, or in
small groups, no problem.

The fields are text, currency, long int, date/time, etc. which I set up in
the linked table.

I can work around this but I'd rather understand it.

Thanks,
Jeff
Any ideas?

 

[PieterLinden via AccessMonster.com]

Why are you reusing the make table query? Doesn't the table already exist?
If you want to delete the data from the existing table and append new data,
run a delete query first. The make table query is the wrong thing to run in
this instance.

 

[Jael]

OK. The xxx.cvs tables are updated several times a week. When the db is
entered, the linked tables will be updated to the current xxx.cvs. The make
table query allows me to update a working table in a controlled fashion.
If this is the wrong thing to run, wht is the right thing to run.
Eventually the opening form will programatically update this tables. I
supposed I could delete the tables and then generate a new table using an
docmd.runsql. If not that, what is the best way to xtfr an existing table
into a new table while modifying some of the data in the process.

Thanks,
Jeff

 

[PieterLinden via AccessMonster.com]

OK. The xxx.cvs tables are updated several times a week. When the db is
entered, the linked tables will be updated to the current xxx.cvs. The make
table query allows me to update a working table in a controlled fashion.
If this is the wrong thing to run, wht is the right thing to run.
Eventually the opening form will programatically update this tables. I
supposed I could delete the tables and then generate a new table using an
docmd.runsql. If not that, what is the best way to xtfr an existing table
into a new table while modifying some of the data in the process.

Thanks,
Jeff

Oh, okay. Makes sense now. The table is just a staging table... you use it
to hold the data while you clean it up. If the temp table structures are
consistent, I would just delete the contents of the table (DELETE FROM
[tableX]), and not the entire table object (DROP TableX

so your sequence would be something like:
1. relink to new source CSV file.
2. delete contents of work table
3. import data from CSV to work table
4. run [canned] updates or do cleaning.
5. append to final table(s).

If you do it this way, you can do things like:
1. process all the files in a directory at once. (look up DIR in the VBA help)

 

[John W. Vinson]

If this is the wrong thing to run, wht is the right thing to run.

Just what Pieter suggested. Have a static, permanent, local table with all the
appropriate field types and sizes; *link* to the .csv file, and run an Append
query to migrate the data from the .csv file into the static table.

You may need to run a Delete query to empty the static table first.

In any case, compact your database regularly - the space occupied by tables is
not recovered if the table is deleted or emptied!

 

[Jael]

My thanks to you and Pieter. The delete temptable data only and append the
current data should work. There is only one field that is added to the temp
table and it's a combination of two field from the linked table. Should be a
slam-dunk!

Can the compression be done programatically?

Jeff