Numeric Field Overflow

[Guest]

I am importing Excel files into Access. Occasional I get an error:

Run-time error '3349':
Numeric Field overflow

The Access table I am importing to has text and number fields but I have
experimented with the Excel file and found the problem is with data that is
going to a text field. I can narrow the cause to one or two cells in the
Excel spreadsheet and click the delete field when I am in those cells and
avoid the error message.

I am doing the import from code and the failure occurs on this line:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblImportRawDataFromComponentSubmittalSpreadsheet", Path & "\" & Filename,
False, TabName & "!" & Range

Path, Filename, TabName and Range are stored in a table and define what to
import. The line above is in a loop that repeats for all the necessary
imports and most of the time it works successfully.

I don't control the Excel spreadsheets so I need to apply a fix in Access to
avoid the error. What should I do?

Thanks.

 

[Jeff Boyce]

One approach would be to create a text-only (temporary) table in Access and
import everything Excel into that. Then you could create "parsing" queries
to append the converted (CDate(), CInt(), ...) fields into your (permanent)
tables. This has the added advantage of letting you normalize your data,
rather than simply copying the data structure from Excel into Access. If
you only use Access as a spreadsheet, you will not get the benefit of many
of the features/functions.

 

[Guest]

Hi Jeff,

Thank you very much for helping me.

For the moment I have commented out the lines of code where the Access
import table is used. That should leave only the import routine (with the
error) in the loop. Per your suggestions I changed all the number fields in
the import table to text so that all the fields are text. I still get the
same error on the same line. What else can I try?

Thanks,

David

 

[Jeff Boyce]

My suggestion was that the import routine might not be necessary. I suspect
you could do what you described using only queries.

Jeff Boyce
<Office/Access MVP>

 

[Guest]

How do I use a query to read Excel?

In the meantime I tried a couple of things. To be sure there wasn't
anything in the code that was adding complications I created a macro with one
action; TransferSpreadsheet. The macro also produced the Numeric Overflow
error.

I also tried to import the Excel file using menus; File>Get External
Data>Import... I got a different error:

Method 'Columns' of object "IImexGrid' failed

Thanks,

David

 

[Jeff Boyce]

You wouldn't need to "read" the Excel in the query. You'd link to the Excel
data from the Tables tab, using File|Get External Data|Import...

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Jeff,

As I mentioned, I found I am getting a different error message when I try
File|Get External Data|Import... The message is: "Method 'Columns' of object
"IImexGrid' failed" After I get this message I can't proceed with the link
process. I suspect that whatever is causing the numeric field overflow in
code and macro is also causing this error in "File|Get External Data|Import.."

Thanks for bearing with me,

David

 

[Jeff Boyce]

David

Back to the top ...

From what you've described, there's no way to clean up the Excel data before
attempting to import it, and there's no way to import it.

I've run through the things I know about that might be at issue!

Hopefully one of the other 'group readers has some experience with something
like this. Have you tried searching at Google.com with the error number
and some of the error message?

Regards/Good luck!

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Jeff,

I searched Google for the IImexGrid error and found a chat that led back to
a Microsoft hotfix at Article 841365. The symptoms he had are a little
different but the error message is the same so I am encouraged.
Unfortunately this is not my computer so I have to wait for official
installation.

David