Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

FIX: Importing Text File Without Schema.ini Can Cause Text Column to Be Defined as Currency


View products that this article applies to.

Symptoms

When importing a text file into a Microsoft Jet database through a Microsoft SQL statement or the Microsoft Access TransferText macro command when there is no Schema.ini file present, the Jet engine can sometimes interpret a column with Text data as Currency.

↑ Back to the top


Cause

The Microsoft Jet database engine examines the column for currency formatting symbols.

↑ Back to the top


Resolution

To resolve this problem, use one of the following methods:
  • Upgrade to Microsoft Jet 4.0 SP4.
  • Use the Access 2000 Import Wizard.
  • Create a Schema.ini file, which can contain a list of data types for each column in the text file.

↑ Back to the top


Status

This bug has been fixed in Microsoft Jet 4.0 SP4.

↑ Back to the top


More information

When determining whether the data type of a column in a text file is Currency, Microsoft Jet checks to see if there are characters that are not allowed in the Currency format. If there are none, it assumes that the data type is Currency. However, a column with a single character, such as the letter "F," can be considered a Currency column because this character is allowed in the Currency format. The fix causes a more extensive check to be made.

Steps to Reproduce the Problem

  1. Using Microsoft Notepad, create a text file with the following data, and save it as: C:\TEST.TXT:
    LastName,Gender
    Smith,F
    Jones,F
    					
  2. In Microsoft Visual Basic 5.0 or 6.0, create a Standard EXE project.
  3. On the Project menu, select References, and make a reference to the following type library: Microsoft DAO 3.6 Object Library

  4. Add a Command button and the following code to the default form:
    Option Explicit
    
    Private Sub Command1_Click()
    Dim db As dao.Database, td As dao.TableDef, F As dao.Field
      Set db = DBEngine(0).OpenDatabase("nwind.mdb")
      db.Execute "SELECT * INTO Table1_CSV FROM [text;hdr=yes;database=c:\].[test#txt]", dbFailOnError
      Set td = db!Table1_CSV
      Set F = td.Fields(1)
      Debug.Print F.Name & ": " & F.Type, "Text: " & dao.dbText, "Currency: " & dao.dbCurrency
      Set F = Nothing
      Set td = Nothing
      db.Execute "DROP TABLE Table1_CSV", dbFailOnError
      Set db = Nothing
    End Sub
    					
  5. Run the code and click the Command button. In the Immediate window, you see the following output if you are using Microsoft Jet 4.0 SP3 or earlier showing that the Gender field was treated as if it contained Currency data:
    Gender: 5 Text: 10 Currency: 5
    If you have Microsoft Jet 4.0 SP4 or later, the output appears as follows, showing it was correctly interpreted as a text column:
    Gender: 10 Text: 10 Currency: 5
NOTE: This problem manifests using any Jet access method, including the Microsoft Access ODBC driver, the Microsoft Jet OLDB provider, and the Microsoft Access TransferText macro command. The Access 2000 Import Wizard does not exhibit the problem because it makes a Schema.ini file and sets the data type to Text.

↑ Back to the top


References

The following sources provide more information on the SCHEMA.INI file:
155512 ACC: How to Create a Schema.ini File Programmatically
The Microsoft Jet Database Engine Programmer's Guide, "Accessing External Data"

↑ Back to the top


Keywords: KB254002, kbjet, kbiisam, kbfix, kbbug

↑ Back to the top

Article Info
Article ID : 254002
Revision : 3
Created on : 7/14/2004
Published on : 7/14/2004
Exists online : False
Views : 350