Query every table in an Access Database?

Question

All tables in a certain database have the exact columns, so I'm wondering if there is a way I can query all of them at once for a specific few columns that I know every table will have. The reason I want to do this is that the number of tables in the database will constantly be growing, and I don't want to have to every day go and change my query to accommodate the names of the new tables.

Help is appreciated as always

oof - this is a normalization nightmare. had to be said. you can get a list of the tables from the data dictionary views, and then create a dynamic query based on that - or you can maybe fix the table propagation problem. — Randy
– Randy, Commented Jan 14, 2013 at 14:34
I have to agree with @Randy, why are you constantly creating new tables? There must be an easier way. You can use ADO to get field names ( wiki.lessthandot.com/index.php/ADO_Schemas ), but this is not going to get better. — Fionnuala
– Fionnuala, Commented Jan 14, 2013 at 14:51
Instead of cracking your head on this, you rather you use that to design a better schema, normalized, with cross referenced tables and proper referential integrities. :) It will take you less time than going through this mess... growing tables are really scary... — bonCodigo
– bonCodigo, Commented Jan 14, 2013 at 14:52
Alright, the real issue here is that my business bids a lot of jobs, maybe 400+ a year, and there is an excel spreadsheet tied to every bidding process. It holds the estimated work, billed cost, and actual work/cost of the job. Work Orders for employees actually working the job are created off of this bid spreadsheet, since the spreadsheet contains every line item piece of work done during a job. This stuff needs to be stored in a database where work orders would be generated off of and put onto a website. — Hoser
– Hoser, Commented Jan 14, 2013 at 14:58
Continuing on the last comment... If the spreadsheets never changed after a job was bid and eventually awarded to my business, I could just append all this information into a single database and move on, but things are constantly changed/added/removed throughout the process of a job, so I felt the best thing to do was to create an Access tabled that is tied to each excel sheet, so every change in the excel sheet was captured in the access database automatically. Without this, every change in the spreadsheet would need to be manually done in the access database, a huge waste of time. — Hoser
– Hoser, Commented Jan 14, 2013 at 15:00

Fionnuala · Accepted Answer · 2013-01-14 15:14:42Z

2

In that case, try ADO:

Function ListTablesContainingField(SelectFieldName) As String
   'Tables returned will include linked tables
   'I have added a little error coding. I don't normally do that
   'for examples, so don't read anything into it :) 
   Dim cn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strTempList As String

       On Error GoTo Error_Trap

       Set cn = CurrentProject.Connection

       'Get names of all tables that have a column called <SelectFieldName>
       Set rs = cn.OpenSchema(adSchemaColumns, _
       Array(Empty, Empty, Empty, SelectFieldName))

       'List the tables that have been selected
       While Not rs.EOF
           'Exclude MS system tables
           If Left(rs!Table_Name, 4) <> "MSys" Then
               strTempList = strTempList & "," & rs!Table_Name
           End If
           rs.MoveNext
       Wend

       ListTablesContainingField = Mid(strTempList, 2)

   Exit_Here:

       rs.Close
       Set cn = Nothing
       Exit Function

   Error_Trap:

       MsgBox Err.Description
       Resume Exit_Here
   End Function

From: http://wiki.lessthandot.com/index.php/ADO_Schemas

You might like to consider a table of tables, if you have not already got one, that lists the linked Excel tables and holds details of archive dates etc, because you will run into limits at some stage.

answered Jan 14, 2013 at 15:14

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hoser Over a year ago

So just to sort of get my head around this, this is VBSCript to create a table that contains the data in all other tables?

Fionnuala Over a year ago

This is VBA that uses ADO to return a list of tables containing the field that you are looking for. You will need a reference to the appropriate library -- Microsoft ActiveX Data Objects x.x Library

Collectives™ on Stack Overflow

Query every table in an Access Database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related