GetOleDbSchemaTable DATA_TYPES

[Techyon]

Hi guys, I'm finding myself in a little trouble. I've tried to google this problem, but without much success. 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.

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

Both DATA_TYPES are 3, same with COLUMN_FLAGS, both are 90.

a Long Integer that doesn't allow nulls COLUMN_FLAGS will be 90 and its DATA_TYPE will be 3

an AutoNumber will also be COLUMN_FLAGS = 90 and DATA_TYPE = 3

all other fields I can test by are also identical. etc NUMERIC_PRECISION

Here same code to populate datagrid that lists DATA_TYPES

(just fix the connection string and make sure you point to the correct table)

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

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

How do I identify whats an AutoNumber field and whats a Long Integer that doesn't allow nulls?

any and all help will be appreciated!

Regards.

[G_Waddell]

Hi

WOAWW!! if you want to sych two databases DO NOT USE Auto Numbers for Primary KEYs
Actually do not use Access, SQL Server is far more sophisticated at handling this ( SQL Express is free too)

If you've no choice but to use Access then you will need to assign unique primary keys in your code - no auto numbers

The following example illustrates why:

Two access databases DB1 and DB2 they know nothing about each other...

Each has a table say Customers with an Auto Increment primary key...

DB1 gets a new record and gives it a primary key of 1, DB1 gets an other new record and gives it a primary key of 2. DB2 gets a new record so it's primary key is 1 because DB2 doesn't know about DB1

You go to merge the tables and yikes! duplicate primary keys.... are these old records that need updating or current records from the other DB? You don't know as you just have auto incrementing numbers...

So there are a couple of things you can try:
1. Have a composite Primary key made up of customerNo and DatabaseNo - but then each customer number is not unique...
2. Have a single primary key but encode it yourself so it will be unique to each Database SQL has a special Field type ROWGUID that gives a special ID that I think uses the IP Address of the server with the date and time the new field was added parsed through an algorthym to give a unique ID.

Anyway you want to have unique records generated on both databases then you have all the fun of handling data updates etc carried out on each one to handle.

By that I mean a single record is on both databases User A modifies on DB1 as at the same time, User B modifies on DB2 - which record is correct?

[Techyon]

Hey! Thanks for the reply! But I think I didn't explain my problem clearly.

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.

Per example.

Database A has 1 new table, its called Process.

In this table it has 3 columns

ProcessID < Primary Key
ProcessValue1 < Long Integer NOT NULL
ProcessAutoNumber < AutoNumber

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

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 colums
                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

[Techyon]

Ok, I've coded a work around.

If TCI.ColumnName.Contains("AutoNumber") Then
TCI.ColumnWidth = -2
End If

But this is a bad fix =p

I'd still like to know how I can differentiate between a Long Integer and AutoNumber using GetOleDbSchemaTable

Or a better fix =)

Thanks in advance!