Query entire Access Database from Excel

Question

I have a database with 80 tables with all the same fields. What i am trying o do is search entire database for specific data. Here is the code i have been previously using to get data

strTable = "Table3"
user = frm1.Label2.Caption

Set con = CreateObject("ADODB.connection")
If Err.Number <> 0 Then
    MsgBox "Connection was not created!", vbCritical, "Connection Error"
    Exit Sub
End If
On Error GoTo 0
 'Open the connection.
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile

SQL = "SELECT* FROM " & strTable & " WHERE [JCI_Con] = '" & user & "' AND Time_out is null"

this is my full actual code i am using

Option Explicit

 Sub RunQuery14()
 Application.DisplayAlerts = False
Dim con         As Object
Dim rs          As Object
Dim AccessFile  As String
Dim strTable    As String
Dim SQL         As String
Dim i           As Integer
Dim user        As String

On Error GoTo Errhandler

Application.ScreenUpdating = False
Sheets("DATA").Cells.Clear
AccessFile = "H:\APPLICATIONS\SEAT AUDIT\DATABASE\trial.accdb"

strTable = "Table1"
user = "????"
'Create the ADODB connection object.
Set con = CreateObject("ADODB.connection")
'Check if the object was created.
If Err.Number <> 0 Then
    MsgBox "Connection was not created!", vbCritical, "Connection Error"
    Exit Sub
End If
On Error GoTo 0
 'Open the connection.
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile
'Create the SQL statement to retrieve the data from table.
'Get the necessary information (first name etc.) for all the Canadian customers.
SQL = ""
SQL = SQL & " SELECT * FROM "
SQL = SQL & " ( "
SQL = SQL & " SELECT 'Zone1' AS MyTableName, * FROM Zone1 WHERE [Auditor_Name] = '" & user & "'"
SQL = SQL & " UNION ALL"
SQL = SQL & " SELECT 'Zone2' AS MyTableName, * FROM Zone2 WHERE [Auditor_Name] = '" & user & "'"
SQL = SQL & " ) AS First2Tables"

    On Error Resume Next
   'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.Recordset")
    'Check if the object was created.
If Err.Number <> 0 Then
    'Error! Release the objects and exit.
    Set rs = Nothing
    Set con = Nothing
    'Display an error message to the user.
    Exit Sub
End If
On Error GoTo 0
'Set thee cursor location.
rs.CursorLocation = 3 'adUseClient on early  binding
rs.CursorType = 1 'adOpenKeyset on early  binding

'Open the recordset.
rs.Open SQL, con

'Check if the recordet is empty.
If rs.EOF And rs.BOF Then
    'Close the recordet and the connection.
    rs.Close
    con.Close
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    'Enable the screen.
    Application.ScreenUpdating = True
    'In case of an empty recordset display an error.
    Exit Sub
End If

'Copy the recordset headers.
For i = 0 To rs.Fields.Count - 1
    Sheets("DATA").Cells(1, i + 1) = rs.Fields(i).Name

Next i

'Write the query values in the sheet.
Sheets("DATA").Range("A2").CopyFromRecordset rs

'Close the recordet and the connection.
rs.Close
con.Close

'Release the objects.
Set rs = Nothing
Set con = Nothing

'Adjust the columns' width.
Sheets("DATA").Columns("A:Z").AutoFit

'Enable the screen.
Application.ScreenUpdating = True
Exit Sub
Errhandler:


End Sub

I highly recommend not creating SQL statements through string concatenation. You open yourself up to SQL injection later. — Tripp Kinetics
– Tripp Kinetics, Commented Aug 5, 2015 at 18:22
If you use the posted code to get data already, then how do you want to modify it for your new requirement? — DiegoAndresJAY
– DiegoAndresJAY, Commented Aug 5, 2015 at 18:49
Why 80 tables all with the same fields? If you added one more field you could just have a single table, and your queries would be more straightforward.... — Tim Williams
– Tim Williams, Commented Aug 5, 2015 at 18:52
i had to add each table as a zone as i am tracking defects in certain zones of a product. with evry zone there was specific info that needs to be added, so at the end i would have needed about 400 fields. Yes i agree i was hoping to have all in same table. So the need to query all my table at once would be ideal — INOH
– INOH, Commented Aug 5, 2015 at 19:19
So is it possible to 'SELECT* FROM [ALL TABLES] WHERE [CAR_PART]=?' or is there another way — INOH
– INOH, Commented Aug 5, 2015 at 20:49

HarveyFrench · Accepted Answer · 2015-08-05 23:03:20Z

Change the SQL to use a UNION query which will get all the data from all tables

SQL = ""
SQL = SQL & " SELECT 'Table1' AS MyTableName, * FROM Table1 WHERE .....
SQL = SQL & " UNION ALL"
SQL = SQL & " SELECT 'Table2' AS MyTableName, * FROM Table2 WHERE .....
SQL = SQL & " UNION ALL"
etc....

Why in gods name do you have 80 tables with identical fields.

I would have expected one common table with all the common fields and a "row type" field (which could store 80 different type values) and then linked to this table from 80 other tables having the extra columns for each row type.

(This has it's benefits, but there are justifiable reasons to do it the way you did)

Anyway, the above query will work BUT you will need to replace * in each query to only get the common fields. (fields in a union query must have the same data type in each column - i think)

If you want to add an order by you would write it using the column position number eg

  ORDER BY 4,10,12,1

IF you exceed the limit on the number of UNIONS allowed try this:

SQL = " SELECT * FROM ("
SQL = SQL & " SELECT 'Table1' AS MyTableName, * FROM Table1 WHERE .....
SQL = SQL & " UNION ALL"
SQL = SQL & " SELECT 'Table2' AS MyTableName, * FROM Table2 WHERE .....
SQL = SQL & " UNION ALL"
etc....
SQL = SQL & " ) AS First10Tables"

SQL = SQL & " UNION ALL"

SQL = SQL & " SELECT * FROM ("
SQL = SQL & " SELECT 'Table1' AS MyTableName, * FROM Table1 WHERE .....
SQL = SQL & " UNION ALL"
SQL = SQL & " SELECT 'Table2' AS MyTableName, * FROM Table2 WHERE .....
SQL = SQL & " UNION ALL"
etc....
SQL = SQL & " ) AS Next10Tables"

If this is a problem:

You might be able to get away with using UNION instead of UNION ALL.

restructure you database, or use named queries or temp tables (ie insert rows into a table from 10 tabels at a time)

Harvey

There is a limit on the number of UNION clauses that Access can handle. I don't know precisely what that limit is, but it is certainly far lower than 80.
I am having trouble understanding how to put this SQL statement into my current code. I tried removing my original SQL=Select....Statement and insert above but no luck. Could you please help me create my statement. I am trying to do the code above doing 10 tables at a time, thanks in advance for taking the time to help
I have added the code to the top of my post, thanks in advance]
@INOH I have fixed your code in your answer. because you have used zone1.* it relies on zone1 and zone2 having an identical number of columns and those that are there having the same data types. Which I have repeated here: SQL = "" SQL = SQL & " SELECT * FROM " SQL = SQL & " ( " SQL = SQL & " SELECT 'Zone1' AS MyTableName, * FROM Zone1 WHERE [Auditor_Name] = '" & user & "'" SQL = SQL & " UNION ALL" SQL = SQL & " SELECT 'Zone2' AS MyTableName, * FROM Zone2 WHERE [Auditor_Name] = '" & user & "'" SQL = SQL & " ) AS First2Tables"

Collectives™ on Stack Overflow

Query entire Access Database from Excel

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related