Access make table query Error "Numeric Field Overflow"

I have a linked table that comes from a text file. When I try to use a make table query to generate a local table the query fails with “Numeric Field Overflow” this does not happen every time. I have tried deleting the table first and then running the make table. I have even run compress repair and although it usually run OK after this it is not consistent. I have looked up this error on the web but not found anyone with exactly the same error. Any help appreciated.

Detail:

 

I import a file [a1.asc] from the server as a fixed length file linked to the Access mdb.

I then delete the onl local file [tblA2] from the mdb.

I then run a maketable query to produce a new [tblA2] in the mdb, this is when the error reports.

 

Question Info


Last updated September 3, 2018 Views 3,249 Applies to:

There is some data value in the file that causing the blowup. Are the file structures the same all the time? If so, try deleting the contents of the table and appending the new data rather than a make Table.

 

 


Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries
Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks for the reply Scott.

 

The file is from an IMB iSeries [AS400] I receive the file from an FTP site and convert the file from EBCDIC to ASCII the file is structure is always the same.

My first run was to delete the contents and append the data, as this worked some times and not others I changed to this method of deleting the file and using a make table to rebuild it. My problem is that it works most of the time. I find that if I do a compact and repair  9 times out of 10 [when this does not work I need to run the compact and repair again] it will work in the end with no other changes.

 

Is there away to identify the record when this error occurs?

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

That's why I recommended the Append, it should give you an indication of which record is causing the problem. If it doesn't. Then, since its a text file, you can try reading the file in line by line.

My company also uses an iSeries, we ODBC into the tables to get data for use in Access. Is that an option?


Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries
Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Microsoft Access MVP since 2007

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks Scott, I ran the make table this morning and the file was created with no problem. I can not use ODBC as the file is supplied to me via a VPN and FTP. I have now created a backup of the table layout so when the problem occurs I can try the append or read the file line by line to see what happens. If my memory serves me correctly I started with a delete and then append and then switched to make table when I had problems with it. I will post back here when I have done some more testing. Regards Jock.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

One way this can happen is if you have a large (over 2 billion odd) "number" in the input file, which is really an identifier of some sort: a phone number (area code 201 will work, area code 212 won't!), Social Security number, etc. Such fields must be loaded into Text datatype fields, not Number. If you're using the import wizard, Access will look at the first few rows of data and guess at the appropriate datatype, and may well guess wrong!

Normally in this kind of situation you're better off creating a local table with appropriate datatypes and field sizes, linking to the .csv file, and running an Append query rather than a MakeTable. This gives you better control over datatypes, indexes, etc.

 


John W. Vinson/MVP
John W. Vinson/MVP

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks John, The append query works well, with no problem in running it 10 times.

Regards,

Jock

Update sorry John although this did work for a while it stopped and I had to go back to compressing the database before running the append to get it to work.

I suggest the problem is not to do with "Numeric Field Overflow" but something else that causes this error to be thrown up.

I'm now using a SQL Select to copy the records into the new file and this works so far. It has made the job a little slower to run.

VBA Code.

With docmd

With DoCmd

               .SetWarnings False

               .RunSQL "SELECT * into VMEMF01 FROM VMEMF00"

               .SetWarnings True

End With

Regards,

Jock

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

If your first select query appends the data into a table where all the fields are text data type ( like an intermediate table in the process) it will probably solve that "Numeric Overflow" error.
You can then use another query to place particular text fields into a number data type field in a different table - the (real) table used by your database.
Jeanette Cunningham MVP(Access) Pakenham, Victoria Australia

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.