converting linked csv files to tables

[Bobk]

I have several CSV files I have linked to Access. I want to convert these
linked files to tables so I have a make table query that always works on one
of my linked files. It always works the first time I run it. If I run it a
second time I get the error "numeric field overflow". This happens
consistently. I'm wondering why the query runs flawlessly the first time it
is run and thereafter returns an error. I noticed that if I modify the query
by deleting one of the data fields it will run a second time without error.
It is a puzzle considering that the query deletes the existing make table and
re-writes it from scratch. Is there a way to fix this? I want to be able to
re-run the make table query repeatedly.

 

[Piet Linden]

I have several CSV files I have linked to Access. I want to convert these
linked files to tables so I have a make table query that always works on one
of my linked files. It always works the first time I run it. If I run it a
second time I get the error "numeric field overflow".  This happens
consistently. I'm wondering why the query runs flawlessly the first time it
is run and thereafter returns an error. I noticed that if I modify the query
by deleting one of the data fields it will run a second time without error.
It is a puzzle considering that the query deletes the existing make tableand
re-writes it from scratch. Is there a way to fix this? I want to be able to
re-run the make table query repeatedly.  

For each CSV file "type" (file with a specific structure - specific
field names & types), create an import file spec.

Then just append the data to the table that matches. You could create
a really simple form where you use the OpenFile API to get the file
and something as simple as a combobox to choose the filespec, and then
you could just append the data to an existing table...

If you're modifying your database structure at runtime, that's a red
flag. Something's not set up right. you may be missing a field in a
table that differentiates all the records you're importing from these
files. For example... say you have a table like this:

CREATE TABLE ImportHere(
Field1 Text(50),
Field2 Decimal,
etc...
)

if you have several tables with the same structure, but data from
different sources, then all you need to do is something like this:

CREATE TABLE ImportHere(
Field1 Text(50),
Field2 Decimal,
SourceFile Text(250),
ImportDate Date
)

and you can put all the data in the same table just fine. Just make
SourceFile and ImportDate part of the unique index on the table (if
you need one).

 

[Bobk]

I don't understand why the make file query works the first time, but if I run
it again from the exact same linked file I get the error message. For some
reason the make query must not use the same file specs. I don't want to
append, I just want to replace the data. Your suggestion of forcing the file
spec looks good. I will try that. I want to make this happen automatically,
if possible.

 

[Foreswing]

Hi-

I'm experiencing the identical problem. I only have one linked text file
and am simply creating another table from it. I have a spec setup. The
query works once and then never again unless it is changed or just copied as
is and saved as a new query name.

I don't find the one solution offered here as being anything close to a
reasonable explanation. Did you ever fix your problem? If so, how.

Does Microsoft have a more reasonable solution to offer?

Thanks,

 

[AaronM]

I'm seeing the same thing. I have import specs created for my linked table
that points to a csv file and am just trying to pull all of the data into an
existing Access table. It works if I modify the update query that pulls data
out of the csv file in ANY WAY and then it fails every subsequent time. I
have searched all over the place and seen numerous posts that sound similar
and NO solutions. Seems that there is a bug here.

Would sure be nice to find a fix or reasonable workaround for this. I tried
just using the CSV directly as a linked table and the performance is terrible
so I need to get this data into Access easily.

I'm running Office 2003.

Thanks,
Aaron

 

[Mark Harley]

I have several CSV files I have linked to Access. I want to convert these
linked files to tables so I have a make table query that always works on one
of my linked files. It always works the first time I run it. If I run it a
second time I get the error "numeric field overflow". This happens
consistently. I'm wondering why the query runs flawlessly the first time it
is run and thereafter returns an error. I noticed that if I modify the query
by deleting one of the data fields it will run a second time without error.
It is a puzzle considering that the query deletes the existing make table and
re-writes it from scratch. Is there a way to fix this? I want to be able to
re-run the make table query repeatedly.

I had this same problem with a linked .csv used in a 'make table' query, in Access 2003. Th 'make table' executed perfectly the first time after the table was linked, but returned a "Numeric Field Overflow" error on subsequent occasions.

However changing the name of the linked file, and then changing it back again solves the problem.
If you are using a 'make table' query, you'll need to delete the previous version of that table before renaming/unrenaming.

I'm using a simple three-action macro to do this (triggered by a form command button, just before the 'make table'):
1. Delete [table_made_by_make_table_query]
2. Rename [linked_csv -> linked_csv_fix]
3. Rename [linked_csv_fix -> linked_csv]

Hope this helps!