Listing subset of MS Access Tables in MS Excel with VBA

Question

I have a series of stores Queries/tables in an MS Access 2002 database that starts with "RP_". I would like to list those table names in a listbox menu in an MS Excel 2002 spreadsheet. Any ideas on how to do this?

Answer 1

In the VBA Immediate window, with an Access database open, I do the following:

? access.CurrentData.AllQueries.Count
 11 
? access.CurrentData.AllQueries.Item(1).Name
ATP Underwrites Chinese

So in Access there's a CurrentData coClass, with an AllQueries property that can be counted and itemised.

In Excel define a reference to the Microsoft Access object. Then stick something like this in a VBA module:

Function Queryname(n As Integer) As Variant
    Dim v As Variant
    Dim c As Long
    c = Access.CurrentData.AllQueries.Count
    v = Access.CurrentData.AllQueries.Item(n).Name
    Queryname = v
End Function

then in a sheet you should be able to say

=Queryname(1)

and get something useful.

By the way, Access would have to be running at the time I should think.

Answer 2

In Excel, you can use ADO (ActiveX Data Objects) to work directly with the Access database. Before you can use ADO in Excel, you need to reference the appropriate library (e.g. Microsoft ActiveX Data Objects 2.8 Library) via Tools > References in the Visual Basic editor.This page should get you started on how to use ADO.

You might find this example of using the OpenSchema method of the Connection object to return the names of the tables in the database and this example of connecting to an Access database to be useful

Answer 3

Hello TheObserver,
The issue you are hitting with ODBC errors in Excel is likely caused by a permission problem on the database itself. By default the Admin account (which is what you are likely using) does not have read permissions on MSysObjects. You can fix it by going into user permissions and granting read access to that table for the Admin account. The difficulty with that approach is that the permissions are stored in system.mdw file on your local machine (assuming you haven't altered that). This means that you will have to fix the permissions on every machine that uses the code you are creating.

You can however dodge the whole issue with the ADO Extension Library. The code posted below should "Just Run" assuming you give it a proper db path.

Option Explicit

Public Sub Example()
    EnumerateDBTables Range("A1:A2"), "C:\Test\db1.mdb"
End Sub

Public Sub EnumerateDBTables(ByVal target As Excel.Range, ByVal dbPath As _
    String, Optional ByVal clearSheet As Boolean = True)
    'Requires reference to Microsoft ADO Ext. 2.8 for DDL and Security
    'If you have trouble finding it, look here:
    'C:\Program Files\Common Files\System\ado\msadox28.tlb
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim ws As Excel.Worksheet
    Dim lngRow As Long
    Dim lngCol As Long
    Set ws = target.Parent
    If clearSheet Then ws.UsedRange.Clear
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        dbPath
    'Add Headers:
    ws.Range(target.Cells(1, 1), target.Cells(1, 1).Offset(0, 3)) = _
        Array("Name", "Type", "Created", "Modified")
    lngRow = target.Row + 1&
    For Each tbl In cat.Tables
        lngCol = target.Column
        ws.Cells(lngRow, lngCol) = tbl.Name
        ws.Cells(lngRow, lngCol + 1&) = tbl.Type
        ws.Cells(lngRow, lngCol + 2&) = tbl.DateCreated
        ws.Cells(lngRow, lngCol + 3&) = tbl.DateModified
        lngRow = lngRow + 1&
    Next
    ws.UsedRange.Columns.AutoFit
End Sub

Edit:

Technically you can accomplish this without even writing any code... The most optimum program of the all... 0 lines:) Here is how:

- Open the database you want to query.
- Go to to Tools>Security>User And Group Permissions.
- In the box marked "User/Group Name", select "Admin".
- In the box marked "Object Name" select "MysObjects".
- In the area marked "Permissions" (Just below), checkmark the box named "Read Data"
- Click Ok Close the database.
- Open Excel.
- Go to Data>Import External Data>New Database Query.
- Select MS Access Database.
- Uncheck "Use Query Wizard".
- Click OK.
- Browse to Database.
- Click Options.
- Check "System Tables".
- Click OK.
- Build Your Query.
- Click Close.
- Now click View then SQL.
- Paste your SQL.
- Now close the window via the Red X in the upper-right hand corner and you will be prompted for a destination range. 
- Tada!

Answer 4

There are many ways to achieve this.
Here is one:

• Add an ActiveX ComboBox control to your form (say ComboBox1) and link its LinkedCell property to the cell where you want the result to be stored.

• Add a reference to the "Microsoft DAO 3.6 Object Library" to your Excel workbook (ALT-F11 to open the IDE, then menu Tools > References...).
  These MDAC components should already installed on all up-to-date machines.
  Note that if you use Office 2007, you'll be better off referencing "Microsoft Office 12.0 Access Database engine Object Library" instead as it will allow you to open the newer ACCDB Access 2007 databases as well.

• Insert a new Module to your workbook and put this code into it:

  ' Fill the given combobox with all the tables and query names '
  ' starting with RP_ found in the given database. '
  Public Sub FillWithAccessEntities(PathToAccessDB As String, combox As ComboBox)
      Dim db As DAO.Database
      Dim td As DAO.TableDef
      Dim qd As DAO.QueryDef
      combox.Clear
      ' Open the db in exclusive and read-only mode '
      Set db = DBEngine.OpenDatabase(PathToAccessDB, True, True)
      For Each td In db.TableDefs
          If Left(td.Name, 3) = "RP_" Then
              combox.AddItem td.Name
          End If
      Next
      For Each qd In db.QueryDefs
          If Left(qd.Name, 3) = "RP_" Then
              combox.AddItem qd.Name
          End If
      Next
      db.Close
      Set db = Nothing
  End Sub
  

• To initialise the combobox, we can call this code from the workbooks' Open event for instance.
  In the ThisWorkbook module, insert this code, assuming that the ActiveX combobox ComboBox1 has been added to Sheet1:

  Sub Workbook_open()
      FillWithAccessEntities "c:\mydb.mdb", Sheet1.ComboBox1
  End Sub
  

Now, when you open the file, the combobox will be automatically populated with the tables and query names from the database.