troubleshooting Question

Access VBA/VSTO get column data types for a table

Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVisual Basic.NET
2 Comments1 Solution3319 ViewsLast Modified:

I have a project where I need to find the Access data types for any given table.
So far, I am using the following 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)
            Dim connection As New OleDbConnection(oConnectionString)

            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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros