0

I have a form with a button that calls and filters a couple of union queries with about 40 SELECT queries total in between them. It then displays the data in a report. Each SELECT query in the Union query collects records from multiple unique tables in the database. I recently had to add a couple more SELECT queries into the union query to grab records from new tables which is when I got the runtime error. It was opening the report fine before I added these SELECT queries so im under the assumption is there are too many SELECT queries in the UNION query. To resolve this issue, do I simply not use a UNION query and find an alternative way to combine records? or is it something in the VBA code that needs adjustment? Here is my code

Private Sub Command189_Click()
DoCmd.SetWarnings False
DoCmd.Close acReport, "Operator Daily Review"

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("Productivity_WeeklyFinal")
Set qdf2 = db.QueryDefs("qFiller_Names")


   strSQL = "SELECT Info_ME_Employees.ID, gs_1_week_finalUnion.SampleID,
   gs_1_week_finalUnion.Operator, Format$([TestDate],'m/dd/yyyy') AS Test_Date,
   gs_1_week_finalUnion.Test FROM Info_ME_Employees INNER JOIN gs_1_week_finalUnion ON
   Info_ME_Employees.Full_Name = gs_1_week_finalUnion.Operator" & _
   " WHERE Info_ME_Employees.ID IN (4,5,6,7)AND gs_1_week_finalUnion.TestDate Between (Date()-7-
    Weekday(Date(),2)) And (Date()-Weekday(Date(),2)-1) " & _
        " ORDER BY gs_1_week_finalUnion.Operator"
   strSQL2 = "SELECT Info_ME_Employees.ID, Info_ME_Employees.Full_Name FROM Info_ME_Employees" & _
   " WHERE Info_ME_Employees.ID IN (4,5,6,7)"

    qdf.SQL = strSQL
    qdf2.SQL = strSQL2

    DoCmd.OpenReport "Operator Daily Review", acViewReport

   Set db = Nothing
   Set qdf = Nothing
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
st33zygill
  • 23
  • 1
  • 3

2 Answers2

0

I think that there is a limit of tables that can be included in a UNION query - possibly 32. Therefore your options are:

  1. Create several UNION queries, and then UNION them all together as the final step;
  2. Insert the data into a temp table using each individual part of the union query.

Additionally, there may be some way that your database could be re-designed, as it is quite unusual to have to have some many unions needed.

Regards,

Applecore
  • 3,934
  • 2
  • 9
  • 13
  • Thank you for your response. I tried option 1 with no success. Ill give option 2 a go. Will eliminating the use of a UNION query altogether solve the issue? – st33zygill May 27 '20 at 08:42
  • It will if you keep the table count low enough!! The downside is that there will be a time cost involved in doing the inserts. – Applecore May 27 '20 at 08:47
0

Actually, the statement for this "error" is incorrect! “Cannot open any more databases.” What microsoft should have said here is that no more links to a database can be opened. That is why adding more UNIONs caused this error. Because each separate reference to a link to an object (table or query) causes another link (microsoft uses the term "database") to be opened.

Vic
  • 1