ACC2002: Ignored MaxScanRows Setting May Cause Improper Data Types in Linked Tables (282263)



The information in this article applies to:

  • Microsoft Access 2002

This article was previously published under Q282263
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

After you run Visual Basic for Applications code to create a linked table from a text file, you notice #Num! in one or more records.

CAUSE

One or both of the following are true:
  • The following registry key is set to zero:

    HKey_Local_Machine\Software\Microsoft\Jet\4.0\Engines\Text\MaxScanRows

  • You have a Schema.ini file with the following entry:

    MaxScanRows=0

If you have MaxScanRows set to zero, either in the registry or in the Schema.ini file, any tables that you link programmatically may contain fields with improper data types. This is because setting MaxScanRows to zero causes the entire table to be scanned in order to determine the data type of the fields. When this happens, the data type is set according to the majority type in the field. Consequently, if most of the fields contain numbers and some contain text, for example, the field is defined in Access as having the data type of Number, making the text entries show #Num!.

RESOLUTION

This issue does not occur if you link by using the Text Import Wizard. However, if you must create the link in code, you can define the data type for one or more fields in a Schema.ini file. In the following example, the first column is set to a data type of Char:
   [Test.txt] 
   Format=CSVDelimited 
   ColNameHeader=false 
   MaxScanRows=0 
   CharacterSet=ANSI
   Col1=ID Char Width 3
				

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Steps to Reproduce the Behavior

  1. Create a folder named Test in the root directory of drive C.
  2. In Notepad, create a text file named Test.txt, and enter the following data. Then, save the file in the C:\Test folder.
       a
       b
       1
       2
       3 
    					
  3. In Notepad, create a second file, and enter the following data:
       [Test.txt] 
       Format=CSVDelimited 
       ColNameHeader=false 
       MaxScanRows=0 
       CharacterSet=ANSI
    					
  4. On the File menu of Notepad, click Save As. In the Save as type box, click All Files, and then save the file as C:\Test\Schema.ini.
  5. Create a new Access database, and then create a new Visual Basic for Applications module.
  6. Enter the following function into the module:
    Function LinkSchema() 
      Dim db As DAO.Database, tbl As DAO.TableDef 
      Set db = CurrentDb() 
      Set tbl = db.CreateTableDef("Test") 
      tbl.Connect = "Text;DATABASE=c:\Test;TABLE=Test.txt" 
      tbl.SourceTableName = "Test.txt" 
      db.TableDefs.Append tbl 
      db.TableDefs.Refresh 
    End Function
    					
  7. Type the following line in the Immediate window, and then press ENTER:

    ?LinkSchema()

  8. Close the Visual Basic Editor, and then return to Access.
  9. Open the newly linked table Test.
Note that you see #Num! for the first two records.

Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbbug kbnofix KB282263