Access make table query Error "Numeric Field Overflow"

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.

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?

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?

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.

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.

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