none
GetOleDbSchemaTable DATA_TYPE AutoNumber

    Question

  • Hi guys, I'm finding myself in a little trouble. Hopefully its something simple I'm missing and someone will be able to help me.
    I'm writing a little program that will synchronize two access database schema's. It works, to a point.

    The purpose of this program is this to create the missing table/columns and make sure the datatype and field sizes match. I'm not working with any data. I just want to sync the schema of both Databases.

    My problem is, I'm unable to identify whats an AutoNumber and whats a Long Integer.

    Per example.

    Database A,

    It has a table called Table1.

    This table has the following columns

    TableValue1 = Long Integer NOT NULL [Its DATA_TYPE = 3, COLUMN_FLAGS = 90]
    TableAutoNumber = AutoNumber [Its DATA_TYPE = 3, COLUMN_FLAGS = 90]

    I used this code to get the DATA_TYPE and COLUMN_FLAGS

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim str As String = "Provider=Microsoft.jet.oledb.4.0;Data Source=C:\Temp\Master.mdb"
      Dim conn As New OleDb.OleDbConnection
      conn.ConnectionString = str
      ' Open a connection
      conn.Open()
      'Call GetOleDbSchemaTable
      Dim schemaTable As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, "TableTest"})
      ' Attach data row to the grid and close the connection
      DataGrid1.DataSource = schemaTable
      conn.Close()
      
      End Sub
    


    I'll add some of my code to give a better overview.

    Here I send along a table name, then go though all the columns and add it to a Collection.  The second Function is just to return the correct datatype I will use later in my query strings.

    Public Function GetTableColumnInfoForTable(ByVal TableName As String) As Collection
      Dim colTableInfo As New Collection
      If oCon.State = ConnectionState.Closed Then oCon.Open()
      Dim dt As DataTable = oCon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, TableName})
      Dim dtPrimary As DataTable = oCon.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object() {Nothing, Nothing, TableName})
      
      Dim totalPosition As Integer = dt.Rows.Count
      Dim currentPosition As Integer = 1
      
      Try
      
      If DoesTableExist(TableName) = True Then
      'Count number of columns
      For i As Integer = 0 To dt.Rows.Count - 1
      
      Dim TCI As New TableColumnInformation
      Dim dr As DataRow = getRow(i, dt)
      
      TCI.ColumnName = "[" & dr("COLUMN_NAME") & "]"
      If Not dr("CHARACTER_MAXIMUM_LENGTH") Is Convert.DBNull Then
      TCI.ColumnWidth = dr("CHARACTER_MAXIMUM_LENGTH")
      Else
      
      Select Case dr("COLUMN_FLAGS")
      Case 122
      TCI.ColumnWidth = -1
      Case 90 'AutoNumber
      TCI.ColumnWidth = -2
      Case Else
      TCI.ColumnWidth = -3
      End Select
      
      End If
      
      If dr("COLUMN_HASDEFAULT") = True Then
      TCI.ColumnDefault = dr("COLUMN_DEFAULT")
      End If
      
      If Not dr("character_octet_length") Is Convert.DBNull Then
      TCI.Character_Octet_Length = dr("character_octet_length")
      End If
      
      If Not dr("NUMERIC_PRECISION") Is Convert.DBNull Then
      TCI.NUMERIC_PRECISION = dr("NUMERIC_PRECISION")
      End If
      
      If Not dr("NUMERIC_SCALE") Is Convert.DBNull Then
      TCI.NUMERIC_SCALE = dr("NUMERIC_SCALE")
      End If
      
      TCI.IsNullable = dr("IS_NULLABLE")
      TCI.TableName = dr("TABLE_NAME")
      TCI.ColumnPosition = dr("ORDINAL_POSITION")
      TCI.Datatype = GetDataType(dr("DATA_TYPE"), TCI.ColumnWidth, TCI.NUMERIC_PRECISION, TCI.NUMERIC_Scale)
      TCI.CollectionKey = TCI.TableName & ":" & TCI.ColumnName
      
      For Each dr1 As DataRow In dtPrimary.Rows
      If dr1("COLUMN_NAME") = dr("COLUMN_NAME") Then
      If Not dr1("COLUMN_NAME") Is Convert.DBNull Then
      TCI.PrimaryKey = dr("COLUMN_NAME")
      TCI.IntegerPrimaryKey = 1
      End If
      End If
      Next
      
      If Not colTableInfo.Contains(TCI.CollectionKey) Then
      colTableInfo.Add(TCI, TCI.CollectionKey)
      End If  
      
      Next
      
      End If
      
      Catch ex As Exception
      Return Nothing
      End Try
      oCon.Close()
      Return colTableInfo
      End Function
    
      Private Function GetDataType(ByVal OLEDataType As Integer, ByVal ColumnSize As String, ByVal NumericPrecision As Integer, ByVal NumericScale As Integer) As String
      
      Select Case OLEDataType
      
      Case 2
      Return "Integer"
      
      Case 3 'Long
      Select Case ColumnSize
      Case -1
      Return "Long"
      Case -2 'AutoNumber
      Return "counter(1,1)"
      Case Else
      Return "Long"
      End Select  
      
      Case 4
      Return "Single"
      
      Case 5
      Return "Double"
      
      Case 6
      Return "Money" 'CURRECY
      
      Case 7
      Return "DATETIME"
      
      Case 11 'Yes/No fields
      Return "BIT"
      
      Case 17
      Return "BYTE"
      
      Case 72
      Return "MEMO"
      
      Case 130
      If ColumnSize = 0 Then
      Return "MEMO"
      ElseIf ColumnSize = "-1" Then
      Return "MEMO"
      Else
      Return "VARCHAR(" & ColumnSize & ")"
      End If
      
      Case 131
      Return "decimal(" & NumericPrecision & "," & NumericScale & ")" 'decimal
      
      Case 128
      If ColumnSize = "-1" Then
      Return "MEMO"
      ElseIf ColumnSize = "0" Then
      Return "MENO" 'OLE Object
      Else
      Return "VARCHAR(" & ColumnSize & ")"
      End If
      
      Case Else
      If ColumnSize = "-1" Then
      Return "MEMO"
      Else
      Return "VARCHAR(" & ColumnSize & ")"
      End If  
      
      End Select
      Return Nothing
      End Function
    

    TableColumnInformation looks like this

    Public Class TableColumnInformation
    
      Friend ColumnName As String
      Friend ColumnWidth As String
      Friend TableName As String
      Friend Datatype As String
      Friend ColumnPosition As String
      Friend CollectionKey As String
      Friend PrimaryKey As String
      Friend IntegerPrimaryKey As Integer
      Friend Character_Octet_Length As Integer
      Friend Numeric_Precision As Integer
      Friend Numeric_Scale As Integer
      Friend IsNullable As Boolean
      Friend ColumnDefault As String
    
    End Class
    
    

    Now, following this code, any Long Integers that is NOT NULL will be seen as AutoNumber.

    This is problematic when I iterating though my collections and start building my Query Strings. Since a table can only have 1 AutoNumber field.

    My Apologizes if this was posted in the wrong section.

     

    Thanks for any help in advance too!

    Regards

    Tuesday, July 26, 2011 7:45 AM

Answers

  • I don't believe that ADO.NET supports the ability to determine whether an MS Access column is Autonumber, however; it can be accomplished using ADOX (COM library Microsoft ADO Ext. 2.x for DDL and Security).

        Dim cnn As ADODB.Connection = New ADODB.ConnectionClass
        Dim cat As ADOX.Catalog = New ADOX.CatalogClass
        Dim tbl As ADOX.Table
        Dim col As ADOX.Column
    
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data 
    Source=C:\db3.mdb;Persist Security Info=False"
        cnn.Open()
        cat.ActiveConnection = cnn
        tbl = cat.Tables("Table1")
        For Each col In tbl.Columns
          If col.Properties("AutoIncrement").Value = True Then
            MessageBox.Show("AutoNumber")
          End If
        Next
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Marnus Kruger Tuesday, July 26, 2011 1:59 PM
    Tuesday, July 26, 2011 12:54 PM

All replies

  • I don't believe that ADO.NET supports the ability to determine whether an MS Access column is Autonumber, however; it can be accomplished using ADOX (COM library Microsoft ADO Ext. 2.x for DDL and Security).

        Dim cnn As ADODB.Connection = New ADODB.ConnectionClass
        Dim cat As ADOX.Catalog = New ADOX.CatalogClass
        Dim tbl As ADOX.Table
        Dim col As ADOX.Column
    
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data 
    Source=C:\db3.mdb;Persist Security Info=False"
        cnn.Open()
        cat.ActiveConnection = cnn
        tbl = cat.Tables("Table1")
        For Each col In tbl.Columns
          If col.Properties("AutoIncrement").Value = True Then
            MessageBox.Show("AutoNumber")
          End If
        Next
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Marnus Kruger Tuesday, July 26, 2011 1:59 PM
    Tuesday, July 26, 2011 12:54 PM
  • Thanks.

     

    This will resolve my issue, but at the moment I'm trying to steer clear from ADOX. Is there no other alternatives?

    Tuesday, July 26, 2011 1:59 PM
  • The only other method I'm aware of would be to use DAO, but once again you working with COM and not managed code.

    One suggestion is to put "AutoNumber" in the Description property of the column in Microsoft Access. This column property can be read using GetOleDbSchemaTable.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, July 26, 2011 4:05 PM