Microsoft KB Archive/259643

= BUG: Number Fields in Linked Oracle Table Changed to Text in Jet =

Article ID: 259643

Article Last Modified on 9/22/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q259643



SYMPTOMS
Oracle number fields with a precision greater than 9 and with a scale of 0 are defined as type Text when they are linked into a Microsoft Access 97 database programmatically through Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX).



RESOLUTION
Use a Microsoft Access 2000 database or change it through the Access 97 user interface directly.



STATUS


Steps to Reproduce Behavior
 Start Visual Basic and create a Standard EXE project. Form1 is created by default. On the Project menu, choose References, and then add references to the following:  Microsoft ActiveX Data Objects 2.x library Microsoft ADO Ext. 2.x for DDL and Security

  Paste the following code into the default form's Load method: Dim str As String Dim cat As New ADOX.Catalog Dim tbl As New ADOX.Table Dim rs As ADODB.Recordset Dim cn As ADODB.Connection Dim cn1 As ADODB.Connection Dim cn2 As ADODB.Connection

'Create the sample Oracle table. Set cn2 = New ADODB.Connection cn2.ConnectionString = &quot;Driver={Microsoft ODBC For Oracle};Server=dseoracle8;Uid=demo;Pwd=demo;&quot; cn2.Open On Error Resume Next cn2.Execute &quot;drop table test&quot; On Error GoTo 0 cn2.Execute &quot;create table test (fld8 number(8,0), fld9 number(9,0), fld10 number(10,0), fld11 number(11,0), fld12 number(12,0))&quot; cn2.Close 'with Access 2000 first... 'Open the Access 2000 MDB Set cn = New ADODB.Connection With cn       .Provider = &quot;Microsoft.JET.OLEDB.4.0&quot; .Properties(&quot;User ID&quot;) = &quot;admin&quot; .Properties(&quot;Password&quot;) = vbNullString 'Specify the appropriate path to a native Access 2000 MDB .Properties(&quot;Data Source&quot;) = &quot;NWIND2000.MDB&quot; .Open End With 'Open the catalog cat.ActiveConnection = cn

With tbl .Name = &quot;aa7&quot; Set .ParentCatalog = cat .Properties(&quot;Jet OLEDB:Create Link&quot;) = True 'Use a DSN-less connection .Properties(&quot;Jet OLEDB:Link Provider String&quot;) = &quot;ODBC;Driver={Microsoft ODBC For Oracle}; Server= ;Uid= ;Pwd= ;&quot; '.Properties(&quot;Jet OLEDB:Link Provider String&quot;) = &quot;PROVIDER=msdaora;DATA SOURCE= ; USER ID= ;PASSWORD= ;&quot; .Properties(&quot;Jet OLEDB:Cache Link Name/Password&quot;) = False 'Include the schema/owner name with the Oracle table name .Properties(&quot;Jet OLEDB:Remote Table Name&quot;) = &quot;test&quot; End With 'Append the table to the collection cat.Tables.Append tbl 'now repeat with an Access 97 MDB... Set cn1 = New ADODB.Connection With cn1 .Provider = &quot;Microsoft.JET.OLEDB.4.0&quot; .Properties(&quot;User ID&quot;) = &quot;admin&quot; .Properties(&quot;Password&quot;) = vbNullString 'Specify the appropriate path to a Native Access '97 MDB .Properties(&quot;Data Source&quot;) = &quot;NWIND97.MDB&quot; .Open End With 'Open the catalog cat.ActiveConnection = cn1

With tbl .Name = &quot;aa7&quot; Set .ParentCatalog = cat .Properties(&quot;Jet OLEDB:Create Link&quot;) = True 'Use a DSN-less connection .Properties(&quot;Jet OLEDB:Link Provider String&quot;) = &quot;ODBC;Driver={Microsoft ODBC For Oracle}; Server= ;Uid= ;Pwd= ;&quot; .Properties(&quot;Jet OLEDB:Cache Link Name/Password&quot;) = False .Properties(&quot;Jet OLEDB:Remote Table Name&quot;) = &quot;test&quot; End With

'Append the table to the collection cat.Tables.Append tbl 'Print out the Column information for both Linked tables Debug.Print &quot;From Access 2000 -&quot; Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, &quot;aa7&quot;)) While Not rs.EOF Debug.Print rs!COLUMN_NAME & &quot;( &quot; & rs!numeric_precision & &quot;, &quot; & rs!NUMERIC_SCALE & &quot; ) - &quot; & rs!Data_Type rs.MoveNext Wend cn.Close Debug.Print &quot;From Access 97 -&quot; Set rs = cn1.OpenSchema(adSchemaColumns, Array(Empty, Empty, &quot;aa7&quot;)) While Not rs.EOF Debug.Print rs!COLUMN_NAME & &quot;( &quot; & rs!numeric_precision & &quot;, &quot; & rs!NUMERIC_SCALE & &quot; ) - &quot; & rs!Data_Type rs.MoveNext Wend cn1.Close </li> Run the form, and note the output in the Immediate window.</li></ol>

<div class="references_section">