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?

 

[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

 

[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