Microsoft KB Archive/282263

= ACC2002: Ignored MaxScanRows Setting May Cause Improper Data Types in Linked Tables =

Article ID: 282263

Article Last Modified on 11/6/2003

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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
 Create a folder named Test in the root directory of drive C.</li>  In Notepad, create a text file named Test.txt, and enter the following data. Then, save the file in the C:\Test folder. <pre class="fixed_text">  a   b   1 2  3                     </li>  In Notepad, create a second file, and enter the following data: <pre class="fixed_text">  [Test.txt] Format=CSVDelimited ColNameHeader=false MaxScanRows=0 CharacterSet=ANSI </li> 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.</li> Create a new Access database, and then create a new Visual Basic for Applications module.</li>  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(&quot;Test&quot;) tbl.Connect = &quot;Text;DATABASE=c:\Test;TABLE=Test.txt&quot; tbl.SourceTableName = &quot;Test.txt&quot; db.TableDefs.Append tbl db.TableDefs.Refresh End Function </li> Type the following line in the Immediate window, and then press ENTER:

?LinkSchema

</li> Close the Visual Basic Editor, and then return to Access.</li> Open the newly linked table Test.</li></ol>

Note that you see #Num! for the first two records.

Additional query words: pra # num !

Keywords: kbbug kbnofix KB282263

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.