Search code examples
sqlms-accesslinked-tables

How to execute query only for existing tables?

I'm using MS Access 2003 and I have a "long" query like 

SELECT * FROM [Table 1]
UNION ALL
SELECT * FROM [Table 2]
UNION ALL 
SELECT * FROM [Table 3]
....
SELECT * FROM [Table 100]

Each of table linked to a certain table from HTML file. Sometimes my HTML source file contains diff amount of tables - less than 100, and of course my query on execution return error "3078: Cannot find Table...". To avoid this error I was trying to build this query dynamically, by using VBA code. So I have next logic:

  1. Check if table exist
  2. Build query string (add to string "UNION ALL SELECT * FROM [Table " & i & "]")
  3. On Error = 3078 execute query(based on query string).

It works, but it takes a lot of time to check if table exist(each time database trying to link this table from source). Do I have another way to do it? Is it possible to return like "partial" result from the "long" query(only for those tables which exists and skip another one's) and do not check them separately?

Solution

  • Rather than trying to count how many records you have in a table for each table you have you can access your tables through the TableDef option. This will allow you to build your query faster than querying each table as a means of seeing whether it exists or not. It does require your tables to be currently linked though.

    Dim db as DAO.Database
Dim wrk as DAO.Workspace
Dim tdf as DAO.TableDef

Set wrk = DBEngine.Workspaces(0)
Set db = wrk.OpenDatabase(databasePath, False, False, connection type/password)

For Each tdf in db.TableDefs
    'Add it to your query string here.
Next 

db.close
wrk.close
Set tdf = Nothing
Set db = Nothing
Set wrk = Nothing