Access 97 - Automating TransferText not working

Posted on 2001-06-13
Last Modified: 2008-03-06
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.
Question by:kaldrich
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 6186800
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:



Expert Comment

ID: 6742511
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

Expert Comment

ID: 6758939
Points to JDettman.
community support moderator

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question