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

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).

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

Steps to Reproduce Behavior

1. Start Visual Basic and create a Standard EXE project. Form1 is created by default.
2. 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
   
   
3. 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 = "Driver={Microsoft ODBC For Oracle};Server=dseoracle8;Uid=demo;Pwd=demo;"
   cn2.Open
   On Error Resume Next
   cn2.Execute "drop table test"
   On Error GoTo 0
   cn2.Execute "create table test (fld8 number(8,0), fld9 number(9,0), fld10 number(10,0), 
   fld11 number(11,0), fld12 number(12,0))"
   cn2.Close
           
   'with Access 2000 first...
   'Open the Access 2000 MDB
       Set cn = New ADODB.Connection
       With cn
           .Provider = "Microsoft.JET.OLEDB.4.0"
           .Properties("User ID") = "admin"
           .Properties("Password") = vbNullString
           'Specify the appropriate path to a native Access 2000 MDB
           .Properties("Data Source") = "NWIND2000.MDB"
           .Open
       End With
       'Open the catalog
       cat.ActiveConnection = cn
   
       With tbl
           .Name = "aa7"
           Set .ParentCatalog = cat
           .Properties("Jet OLEDB:Create Link") = True
           'Use a DSN-less connection
           .Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver={Microsoft ODBC For Oracle};
              Server=<server name>;Uid=<uid>;Pwd=<pwd>;"
           '.Properties("Jet OLEDB:Link Provider String") = "PROVIDER=msdaora;DATA SOURCE=<server name>;
              USER ID=<uid>;PASSWORD=<pwd>;"
           .Properties("Jet OLEDB:Cache Link Name/Password") = False
           'Include the schema/owner name with the Oracle table name
           .Properties("Jet OLEDB:Remote Table Name") = "test"
       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 = "Microsoft.JET.OLEDB.4.0"
           .Properties("User ID") = "admin"
           .Properties("Password") = vbNullString
           'Specify the appropriate path to a Native Access '97 MDB
           .Properties("Data Source") = "NWIND97.MDB"
           .Open
       End With
       'Open the catalog
       cat.ActiveConnection = cn1
   
       With tbl
           .Name = "aa7"
           Set .ParentCatalog = cat
           .Properties("Jet OLEDB:Create Link") = True
           'Use a DSN-less connection
           .Properties("Jet OLEDB:Link Provider String") = "ODBC;Driver={Microsoft ODBC For Oracle};
              Server=<server name>;Uid=<uid>;Pwd=<pwd>;"
           .Properties("Jet OLEDB:Cache Link Name/Password") = False
           .Properties("Jet OLEDB:Remote Table Name") = "test"
       End With
   
       'Append the table to the collection
       cat.Tables.Append tbl
          
   'Print out the Column information for both Linked tables
   Debug.Print "From Access 2000 -"
      Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "aa7"))
      While Not rs.EOF
         Debug.Print rs!COLUMN_NAME & "( " & rs!numeric_precision & " , " & rs!NUMERIC_SCALE & " ) - " & rs!Data_Type
         rs.MoveNext
      Wend
       cn.Close
   Debug.Print "From Access 97 -"
      Set rs = cn1.OpenSchema(adSchemaColumns, Array(Empty, Empty, "aa7"))
      While Not rs.EOF
         Debug.Print rs!COLUMN_NAME & "( " & rs!numeric_precision & " , " & rs!NUMERIC_SCALE & " ) - " & rs!Data_Type
         rs.MoveNext
      Wend
       cn1.Close 
   					

4. Run the form, and note the output in the Immediate window.