Friday, September 6, 2013

Get Table Names from Access using VB

Get Table Names from Access using VB


Use Visual basic .NET to get tables names from MS-Access (Microsoft MS Access) with OleDbConnection and “Microsoft.Jet.OLEDB.4.0” or “Microsoft.ACE.OLEDB.12.0”.
Table MS-Access


Here is an example of code in Visual Basic 2010:





    ''' <summary>
    ''' This is a single and simple function.
    ''' You just need to give the full path
    ''' for the MDB or ACCDB access files
    ''' to get all the user tables Names
    ''' </summary>
    ''' <param name="sPath"></param>
    ''' <returns>a list of String = user Tables Names in Access (not system tables)</returns>
    ''' <remarks></remarks>
    Private Function Load_SQL_MDB_AllTables_List(ByVal sPath As String) As List(Of String)
        Dim oOleDbConnection As System.Data.OleDb.OleDbConnection
        Dim index1 As Integer
        Dim temp_collection As List(Of String) = Nothing
        Try
            index1 = 0
            'ConnectionString
            oOleDbConnection = New System.Data.OleDb.OleDbConnection()
            oOleDbConnection.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0; Data Source =" & sPath & "; Persist Security Info =False;"
            oOleDbConnection.ConnectionString = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =" & sPath & "; Persist Security Info =False;"
            oOleDbConnection.Open() 'ATTENTION, FONTIONNE QUE POUR X86 ; ATTENTION ONLY FOR x86
            If oOleDbConnection.State = ConnectionState.Open Then
                Dim restrictionsValues() As String = {Nothing, Nothing, Nothing, Nothing}
                Dim oDataTable As DataTable = oOleDbConnection.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)
                Dim sTableName As String
                temp_collection = New List(Of String)
                For Each oDataRow As DataRow In oDataTable.Rows
                    sTableName = oDataRow("TABLE_NAME").ToString
                    If oDataRow("TABLE_TYPE").ToString = "TABLE" Then
                        'MsgBox(sTableName)
                        temp_collection.Add(sTableName)
                    End If
                    '    MsgBox(oDataRow.ToString)
                Next

            End If
        Catch ex As Exception

        Finally
            Load_SQL_MDB_AllTables_List = temp_collection
        End Try

    End Function



Remark:

Inside the loop, I only want to retrieve the user tables. If I don’t filter for user tables, I’ll get all the systems and hidden tables inside the Access file.  For the rest, is pretty straight forward.
Improve the code if you want for speed or efficiency.
It very important to know that you need to compile this inform Visual Studio Application in x86 and not in x64 or in Any CPU because it won’t work.

x86

I pick an old sample project I made last April 2013. I change it a little bit. You could open a MDB or ACCDB file and view any user table.  So you could download it from here:

[DOWNLOAD PROJECT SAMPLE: OpenMDBSample_TableNames.zip

OpenMDBSample_TableNames

I hope you found this post interesting or useful.

No comments:

Post a Comment