CODEWITHSUNDEEP
excelms-accessvba
INOH
INOH

Reputation: 385

Query entire Access Database from Excel

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

Upvotes: 0

Views: 81

Answers (1)

HarveyFrench
HarveyFrench

Reputation: 4568

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

Upvotes: 1

Related Questions