Union query intermittently causing error 3307 (1 Viewer)

diberlee · Nov 7, 2018

Hi all,

I've used a Union query in Access for the first time recently and I'm encountering some strange behaviour. The query is set up correctly, same number of columns, same data types etc and runs fine when I click it in the navigation panel.

However, this query is the record source for a couple of subforms, and is referenced in some VBA modules. I'm finding that sometimes when I open the forms that use it as record source it generates an error ("The number of columns in the two selected tables or queries of a union query do not match"), same goes for using it in a recordset in code, it generates a 3307 error sometimes. Is this a known bug with union queries, if so what can I do to get it to work consistently?

Regards

Query for reference:

Code:

SELECT tbl_customers_triggered.id, 
tbl_customers_triggered.DT, 
tbl_customers_triggered.CUST_ID, 
tbl_customers_triggered.ACCT_NO, 
"Flags Report - Score: " & [Ratings] AS monitor_text, 
"N/A" AS dismissal_reason_text, 
qry_flags_and_interaction_count.interactions,
tbl_customers_triggered.id AS flag_id

FROM 
tbl_customers_triggered 

INNER JOIN qry_flags_and_interaction_count 
ON tbl_customers_triggered.id = qry_flags_and_interaction_count.id

UNION ALL SELECT tbl_monitors_triggered.monitor_PK AS 'id', 
tbl_monitors_triggered.dt, 
tbl_monitors_triggered.cust_id, 
tbl_monitors_triggered.acct_no, 
lst_monitors.monitor_text, 
lst_dismissal_reason.dismissal_reason_text, 
qry_monitors_and_interaction_count.interactions,
0 as flag_id


FROM ((tbl_monitors_triggered 

INNER JOIN qry_monitors_and_interaction_count 
ON tbl_monitors_triggered.monitor_PK = qry_monitors_and_interaction_count.monitor_PK) 
INNER JOIN lst_monitors 
ON tbl_monitors_triggered.monitor_fk = lst_monitors.monitor_PK) 
LEFT JOIN lst_dismissal_reason 
ON tbl_monitors_triggered.dismissed_reason_fk = lst_dismissal_reason.dismissal_reason_pk
ORDER BY dt DESC;

isladogs · Nov 7, 2018

Answering on my phone as we have no power today
Just a hunch which I can’t test.
When it fails do one of more of the union parts have no records, particularly the first part which controls the query field names?

diberlee · Nov 7, 2018

Hi isladogs,

There should be no time that this could happen. It's joining data from 2 sources that only get added to. It will generate the error one second, and then be fine the next without any data being changed.

isladogs · Nov 7, 2018

Power back on briefly...
Don't think my hunch was correct after all .... though there is a method of forcing a record involving a cartesian join

Error 3307:

The number of columns in the two selected tables or queries of a union query do not match.

So when it fails you need to check the output from each part of the union and fix the issue
Perhaps one or more fields is read as a different datatype in those cases?????

diberlee · Nov 7, 2018

I'll see if converting values to a specific datatype helps. I'll prepare an "un-unioned" version of the 2 query parts too and see if I can catch a discrepancy.

One of the places I get the error is on a form where the user enters a Customer ID. The union query is loaded into a recordset with that value as a filter to see if I can auto-populate some other fields on the form with details entered previously. Sometimes that fails with a 3307 error, but then the next attempt (triggered by deleting and retyping the ID) it will work fine. Nobody else is currently connected to the backend as I'm working on an update so it seems unlikely that it's the actual query since it has the exact same data available to it on each of these attempts.

diberlee · Nov 7, 2018

diberlee said:
I'll see if converting values to a specific datatype helps. I'll prepare an "un-unioned" version of the 2 query parts too and see if I can catch a discrepancy.

One of the places I get the error is on a form where the user enters a Customer ID. The union query is loaded into a recordset with that value as a filter to see if I can auto-populate some other fields on the form with details entered previously. Sometimes that fails with a 3307 error, but then the next attempt (triggered by deleting and retyping the ID) it will work fine. Nobody else is currently connected to the backend as I'm working on an update so it seems unlikely that it's the actual query since it has the exact same data available to it on each of these attempts.

I should add to this, the same form where I'm seeing this behaviour also contains a subform where the same union query is the recordsource. This never fails to display the records, even when the code in the form that references it simultaneously states that there is an issue with it.

Could the problem be related to having multiple calls to this query at the same time?

isladogs · Nov 7, 2018

Possibly.
I'd test that by temporarily removing the subform then repeating the test with the same criteria where the issue occurs.

In a form / subform situation, counter intuitively the subform loads first.
So perhaps building in a short delay before the main form loads the record source may solve your issue???

diberlee · Nov 7, 2018

No joy with removing the subform. I also tried making it an unbound form to eliminate any weirdness re partial records but I'm still seeing this fail roughly 10% of the time.

I haven't seen it fail as a recordsource on a form recently, it's just this function now.

I don't know if it helps, but here's the code I'm calling

Code:

Private Sub CUST_ID_AfterUpdate()
    
    Dim rst As Recordset2
    
    If Nz(Me.ACCT_NO, "") <> "" Then Exit Sub
    
    On Error Resume Next
    Set rst = CurrentDb.OpenRecordset("SELECT acct_no from qry_flags_and_monitors_union WHERE cust_id = '" & Me.CUST_ID & "'")
    
    If Err.Number <> 0 Then
        Debug.Print Err.Number & " - " & Err.Description
        Err.Clear
        Exit Sub
    End If
    
    On Error GoTo 0
    
    If Not rst.EOF Then
        rst.MoveFirst
        Me.ACCT_NO = rst.Fields(0)
    End If
    
    rst.Close
    Set rst = Nothing
    
End Sub

As far as replicating the issue, I just need to keep clearing/retyping a known customer ID to see if it fails to bring back the result. Nothing else is being changed, which is what made me think initially that this would be a known bug with a possible workaround.

diberlee · Nov 7, 2018

I've managed to get around this, in a less than ideal way but it's working...

Rather than creating a recordset from the saved query I've put the actual SQL for the query into the code as the source for the recordset. I've yet to come across a 3307 error with this method, so it would appear that the errors were caused by the way I was referencing the query?

For future reference, are there better way to reference the query than OpenRecordset("QueryName") ?

isladogs · Nov 7, 2018

You don't need any of the recordset code.
Scrap all of it and replace with one line of code

Code:

 Me.ACCT_NO = DLookup("acct_no","qy_flags_and_monitors_union","cust_id ='" & me.cust_id  & "'")

diberlee · Nov 7, 2018

I've tested that and it's working just fine, I thought you could only use DLookup on tables, good to know for future reference.

I'll never know why the other approaches didn't work consistently but I'll worry about that another day. Really baffling how I can get 2 different outcomes without changing a thing.

Thanks for your help on this, hope your power is sorted now

isladogs · Nov 7, 2018

You're welcome.

I would recommend you use shorter names for database objects.
The query name was a bit OTT.
Although an underscore is allowed, I prefer the use of CamelCase.

And thanks, power was restored after about 3 hours after maintenance work nearby

Union query intermittently causing error 3307 (1 Viewer)

diberlee

Registered User.

isladogs

MVP / VIP

diberlee

Registered User.

isladogs

MVP / VIP

diberlee

Registered User.

diberlee

Registered User.

isladogs

MVP / VIP

diberlee

Registered User.

diberlee

Registered User.

isladogs

MVP / VIP

diberlee

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread