Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access VBA/VSTO get column data types for a table

Hi

I have a VB.net project where I need to find the Access data types for any given table.
So far, I am using the following VB.net code, which pulls schema information into a grid,
where a column called DATA_TYPE holds an integer value relating to data types. This isn't ideal. Surely there is a way to get the data types of the columns via the primary interop assemblies? Does anyone have a better way. An Access VBA example would help

    Sub Fill_Schema_DGV(ByVal oConnectionString As String)
        Try
            Dim connection As New OleDbConnection(oConnectionString)
            connection.Open()

            Dim mySchema As DataTable = TryCast(connection, OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New [Object]() {Nothing, Nothing, Nothing})

            'Dim SchemaColumn = connection.GetOleDbSchemaTable(schema:=OleDbSchemaGuid.Columns, _
            'restrictions:=New Object() {Nothing, Nothing, oTableName, Nothing})

            'Dim DataRowArray() As DataRow = SchemaColumn.Select(Nothing, "ORDINAL_POSITION", DataViewRowState.CurrentRows)

            'Me.DataGridView1.DataSource = mySchema

            mySchema.DefaultView.Sort = "Ordinal_Position"
            Me.DataGridView1.DataSource = mySchema.DefaultView

        Catch ex As Exception
            MsgBox("Failed to retrieve Access database schema information ! " & ex.Message)
        End Try

    End Sub
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Murray Brown

ASKER

Thanks very much for the help