Numeric Field Overflow from Excel File to new Table in Access

[Beverly76]

I have a linked Table in Access "02TBL" which is linked to an Excel
spreadsheet.

Then I have a make table query "02TBLmtq" which takes the entire linked
spreadsheet and makes a new table in another Access Database, removing the
link to Excel.

Even though it is a new table, with no predefined field properties that I
know of, I am getting an error message (sometimes) that is numeric field
overflow. The first thing I did was remove some errors from Excel #N/A,
which helped that field, but for the other fields I can't find any problem
areas. Also, the query has no problem displaying the data in the datasheet
view section, but when it comes time to perform the make table query, that
error is coming up. I have not been able to isolate the error to a single
field, because it seems like when I remove one field, I end up getting an
error in another field.

Any ideas?

 

[Golfinray]

If you have field datatypes set wrong access will think you are trying to
delete files and give you a message. Go into design view of the table and see
if maybe you have a date field set to text or a text field set to date, or
some other datatype error.

 

[Beverly76]

Since it is a linked table from Excel, where do the datatypes come from? All
the numbers say they are double, but I can't change that property in the
Access table.

"This property cannot be modified in linked tables"

 

[Ron2006]

I also have encountered this error message and it did not always
happen.
It was frustrating. I did not have any control over the creation of
the spreadsheet. It was being automatically created from a mainframe
so I had to take what I got, like it or not.

What I ended up doing is:

Using Automation, I opened the excell spreadsheet, went to row 2 and
inserted a row, then I stepped across all of the cells and placed in
them a "X" for alphabetic, a "0" for numeric and date() for a date
field.
then saved/closed the spreadsheet and performed the import.

Then ran a query to delete the Xed record. Never had a problem after
that.

Messy and not the ideal, but it solved the problem.

Ron

 

[John Spencer]

I would not use a make table query unless absolutely necessary.

If at all possible I would create the target table and then append records to
it. One method that I have used is to define the target table structure as a
master table and then copy the master table. Then I populate the copy using
an append query.

You can use DoCmd.CopyObject to copy the master table to the second database
and then use an append query instead of the make table query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County