ADODB recordset returns Runtime error 3021 Either BOF or EOF is true...

[AndyMb]

Hello,

I have a UNION query in Access that merges two tables - OLD vehicles and NEW vehicles

I have a form in Excel that brings Vehicle data in from that UNION query.

So when a user searches a vehicle reg number it matches the reg and should return other details about the vehicle.

When I search (using the form) for a reg that originated from the OLD vehicles table, it works fine. When I search a reg that originated from the NEW vehicle table I get the error that's in my title.

If I run the UNION query in Access and search for a reg from the NEW table it's there, so I know it exists.

Can anyone help me understand why I get this error?

"OrderBookMerge" is my UNION query

Here is my code in excel:

Private Sub CommandButton5_Click()

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim SearchX As Variant


Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=C:\Data\QCandWarranty.accdb"
Set rs = New ADODB.Recordset
rs.Open "OrderBookMerge", cn, adOpenKeyset, adLockOptimistic, adCmdTable


SearchX = ComboBox1.Value
Debug.Print SearchX
Set rs = cn.Execute("SELECT [WoNo],[LineX],[Cust],[Vehicle],[BOM], [SpecNo], [Chassis], [VehicleUniqueID] " & "FROM [OrderBookMerge]" & "WHERE [Reg] = '" & SearchX & "'")



TextBox1.Text = rs.Fields("Vehicle")
TextBox2.Text = rs.Fields("Cust")
TextBox3.Text = rs.Fields("BOM")
TextBox4.Text = rs.Fields("SpecNo")
TextBox5.Text = rs.Fields("WoNo")
TextBox6.Text = rs.Fields("LineX")
TextBox7.Text = ComboBox1.Value
TextBox8.Text = rs.Fields("Chassis")

End Sub

 

[Legacy 456155]

This line is redundant. You can comment it out.

'rs.Open "OrderBookMerge", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Is Reg a text field? Beyond that, I don't see any reason why you are not returning records.

Check that Reg is of the same datatype in both tables.

 

[AndyMb]

Reg is a text field and the same for both.

I should have mentioned that the UNION query is actually getting the data from two other queries NOT tables. Each of those queries require data from several tables.

I plumbed in some raw data from each of those queries straight into normal tables and ran it again, this time without an error. It seems there is some issue with the way I am running those queries.

I'll keep digging, thanks for your help, and the redundant line tip.

 

[Legacy 456155]

Blah. I'm having internet issues and somehow double-posted.

 

[Legacy 456155]

I should have mentioned that the UNION query is actually getting the data from two other queries NOT tables. Each of those queries require data from several tables.

Should not typically matter though, I suppose, something is not typical in the case.

If you wish, upload a barebones copy of your db and workbook here or share by some other method and I'll take a look at it.

 

[xenou]

It doesn't really sound related to your error message but I would assume that union queries are not updateable. You probably should be opening this with a read only or snapshot type of recordset (and possibly it could cause the error you are describing even if it doesn't seem to fit the problem ... some errors are like that).

For that matter, adcmdTable also doesn't seem to fit very well ... that can't be a table if its a query.

Although to be fair these things usually get ironed out by ADO and if the parameters don't fit it tries its best (usually successfully) to give you something that works anyway...


Also if it happens that the problem is exactly what it says it is (either BOF or EOF is true) then you just need to move the recordset to the beginning of the file... (i.e., rs.MoveFirst or something like that ... please don't trust my memory though check this ...) ...


Also I may be reading this wrong - it seems you load the recordset, don't use it, then load it again, then try to use it, so my comments above are all about the first time you are loading it. If you aren't getting results the second time then you probably got no results from the search ... noticing you don't have spaces in front of FROM or WHERE, which might be a problem (not sure).

 

[AndyMb]

I have spotted the issue but still can't work out why it would happen?

As explained above, I have two select queries (one for the old table and one for the new) that acquire the info I need from each table. The union query then puts the two queries together.

In one of the select queries I had criteria filtering out certain BOM numbers. Weirdly it seems that if I try and pull a record-set via ADODB from that select query it returns ZERO records, even though I see plenty of records if I run the query in Access??

I tested this by running an append query triggered through excel. It didn't append anything! When I ran the append query in Access, success!

I deleted the criteria in the select query and guess what? It now works when I run the append query through excel. Weird....

Why was the criteria preventing ADODB from seeing the recordset?

 

[Legacy 456155]

Did the criteria include any property only available to Access such as a form control's value or user defined function or a parameter? What was your criteria?

 

[AndyMb]

Did the criteria include any property only available to Access such as a form control's value or user defined function or a parameter? What was your criteria?

so in the BOM field I put a simple - Like"111-*" and not like "*-4444" -

As far as I can see there is no exclusive properties or user defined functions or parameters.

what's also strange is I have criteria on other fields that don't seem to interfere. The only 'difference' I can see is I 'renamed' the BOM field in question;

so in the field section of the query I put : BOM: Item_0

Item_0 is the original field name from the table. Without testing, could this be the issue?

thanks for your on going help.

 

[Legacy 456155]

Try Like"111-%" and not like "%-4444"