Importing Delimited Text File Error Help!!

[Henry Smith]

Using Access 2000 SP3 with Windows XP Professional on a PC. Trying to
import a tab delimited text file of records. I first import the text file to
a temporary (tblTempImport) table to perform tests to ensure the user
selected the correct text file related to the project selected on the main
menu and the file contains records. After the target tblTempImport records
pass all the tests the temporary table records are deleted. I then import
the text file source of records to the project specific table (tbl05423 in
this case). The process works for importing the delimited text file to
tblTempImport and all the tests work too. As the procedure continues the
records in the tblTempImport are deleted and then the same command is used
to import the same text file to the working (tbl05423) table. This import
process gives an error. "Run time error 3349" "Numeric field overflow".
Both tables are created from the same template. Sample of the code follows:



Dim myFileName as String

'myFileName is full path and file name

Dim ActiveProj As String

'ActiveProj is a variable that equals the project table such as tbl05423

'The following command (line of code) works just fine.



Access.DoCmd.TransferText acImportDelim, "PAAImportSpec", "tblTempImport",
myFileName, True, , 1252

***********************************

Tests for import data integrity are performed here.

At the end of the tests (a series of If... Then statements)

All records in tblTempImport are deleted

***********************************

'Following line imports selected text file (myFileName)

'PAAImportSpec is the import specification name

'ActiveProj variable is the table name the imported data goes to (For
example tbl05423)

'myFileName is the full path and name of the project text file to be
imported

****************************************

Access.DoCmd.TransferText acImportDelim, "PAAImportSpec", ActiveProj,
myFileName, True, , 1252

*****************************************

The above line of code is where I get

Run Time Error 3349

"Numeric Field Overflow"



Any suggestions and help on this problem will be greatly appreciated.



Cheers,

Henry

 

[Mark]

Hi,

You say that both tables are created from the same template. Is it possible
that the tbl05423 has a different data type on one of the number columns?
i.e. a number is too big to be stored in a particular column.

A way to be sure back up the orignal table and copy the tblTempImport and
rename as tbl05423. Then see if the error still happens.

HTH

--

Cheers
Mark

Free Access/Office Add-Ins at:
http://mphillipson.users.btopenworld.com/

 

[Henry Smith]

Mark,
Thanks for the tip. Unfortunately, I already tried that process and still
get the same error.
More ideas will be greatly appreciated.
Cheers,
Henry

 

[Wayne Morgan]

Since you've already imported the data into the temp table, what happens if
you use an append query and append the data from the temp table to the
working table before you delete it from the temp table? This would probably
be quicker than reimporting.

Numeric Field Overflow definitely sounds as if you've exceeded the size of
the number type defined for the field. Not all of your code is shown, if
there are typos in defining the table name or other problems this may
generate an error. Also, you're using a variable for the table name, which
should work, but what happens if you hard code the table name?