Access 97 - Automating TransferText not working

Okay, this is rather annoying.  I am trying to import data from a text file, fixed field format into my Access 97 database.  There are three files - File1.txt, File2.txt, and File3.txt (File3.txt is not always there).  I have written a macro that does a TransferText using an import specification (Shiva Import Specification) for File2.txt & File3.txt, which are in the same format.  File1.txt is also brought in using a TransferText with an import specification, and it works fine (it is formatted differently).

If I do this by hand using the same specification into the same table(File,Get External data,  Import, Select the text file, Advanced, Specs, Shiva Import Specification, and keep clicking Okay) it works fine.

Put the same information into a TransferText line on a macro, and I get this error:

Numeric Field Overflow, Which it associates with a BTrieve table.

Not sure why I'd get the error doing with a macro what I do by hand.  I do some screening on it to ensure the file exists (and that is working), but the error is driving me nuts.  Any suggestions?  Thanks.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
It's hard not to think of Access as one big program, but it's really made up of three: Access UI, VBA, and JET.

You get a difference because you really are doing it two different ways.  One is through the Access UI, the other through VBA.

I'd try setting up a schema.ini file for the import file. Here's a MSKB link:


kaldrich hasn't logged in since early November. Points will be awarded to JDettman in about 7 days unless kaldrich returns with a viable objection.

community support moderator
Points to JDettman.
community support moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.