ACC2000: Error When You Try to Use Schema.ini to Import or Export with the TransferText Method (241477)

Sub ImportSchemaTable()
    Dim db As DAO.Database
    Set db = CurrentDb()
    db.Execute _
    "SELECT * INTO NewContact FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=C:\My documents;].[Contacts#txt];", _
    dbFailOnError
    db.TableDefs.Refresh
End Sub
				

MORE INFORMATION

The TransferText method carries out the TransferText action in Visual Basic. The syntax for this method is:

DoCmd.TransferText [TransferType][, SpecificationName], TableName, FileName[, HasFieldNames][, HtmlTableName][, CodePage]
				

The SpecificationName argument of the TransferText method is a string expression that is the name of an import or export specification that you have created and saved in the current database.

Access 2000 Help states that when you use the SpecificationName argument for a fixed-width text file, you must either specify an argument or use a Schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. However, when you apply these instructions, you can encounter one of the error messages listed in the "Symptoms" section.

Steps to Reproduce the Behavior

1. Start NotePad, and type the following text. Then, save the file as C:\My Documents\Contacts.txt:

   First     NameLast NameHireDate
   Nancy     Davolio  10-22-91
   Robert    King     10-23-91
   					

2. On the File menu, click New, and then type the following text. Then, save the file as C:\My Documents\Schema.ini:

   [Contacts.txt]
   ColNameHeader=True
   Format=FixedLength
   MaxScanRows=0
   CharacterSet=OEM
   Col1="First Name" Char Width 10
   Col2="Last Name" Char Width 9
   Col3="HireDate" Date Width 8
   					

   NOTE: Make sure that both the Contacts.txt file and the Schema.ini file are stored in the C:\My Documents folder.
   
   
3. : Make sure that both the Contacts.txt file and the Schema.ini file are stored in the C:\My Documents folder.Open a new database, create a new module, and add the following code:

   Sub Test1()
       DoCmd.TransferText acImportFixed, , "Contacts", "C:\My Documents\Contacts.txt"
   End Sub
   					

4. : Make sure that both the Contacts.txt file and the Schema.ini file are stored in the C:\My Documents folder.In the Immediate window, type Test1, and then press ENTER. Note that you receive one of the errors that is mentioned in the "Symptoms" section.
5. : Make sure that both the Contacts.txt file and the Schema.ini file are stored in the C:\My Documents folder.Add the following code to your module:

   Sub Test2()
       DoCmd.TransferText acImportFixed, "C:\My Documents\Schema.ini", "Contacts", "C:\My Documents\Contacts.txt"
   End Sub
   					

6. : Make sure that both the Contacts.txt file and the Schema.ini file are stored in the C:\My Documents folder.In the Immediate window, type Test2, and then press ENTER. Note that you receive one of the errors that is mentioned in the "Symptoms" section.