Querying a stored query in VBA (1 Viewer)

ray147

Registered User.
Local time
Today, 13:46
Joined
Dec 13, 2005
Messages
129
Hi,

I have the following SQL query in VBA ...the VBA code is running without errors...

strSQL = "SELECT PlanDespDate,Planned_Qty,BookingType,ShiftName " & _
"FROM Booking_Compliance_B2;"

rs.Open strSQL, conn, adOpenDynamic, adLockPessimistic

The problem is that when I try to retrieve data from the recordset, I get the error of 'Item cannot be found in the collection'.

Thing is that Booking_Compliance_B2 is a stored query NOT a table...I tried copying that SQL and putting it into a query in SQL view and it returns the records..However when I try via VBA i get this error of 'item cannot be found in the colllection'.

Any news of how can I query a query in VBA?

Thanks :)
 

reclusivemonkey

Registered User.
Local time
Today, 13:46
Joined
Oct 5, 2004
Messages
749
What do you have conn defined as? Also, try using adOpenStatic rather than adOpenDynamic.
 

ray147

Registered User.
Local time
Today, 13:46
Joined
Dec 13, 2005
Messages
129
Thanx for your answer....

I've tried using adOpenStatic but still gives same error..

conn defined as ......... Set conn = CurrentProject.Connection
 

ray147

Registered User.
Local time
Today, 13:46
Joined
Dec 13, 2005
Messages
129
can anyone help me out here? i seem to be stuck :(

To make things clear again, I have the following SQL query in VBA where "Booking_Compliance_B2" is a stored query which I created using the query builder in Access :

Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset

strSQL = "SELECT PlanDespDate,Planned_Qty,BookingType,ShiftName " & _
"FROM Booking_Compliance_B2;"

rs.Open strSQL, conn, adOpenDynamic, adLockPessimistic

This runs and does not give any errors...however I when I try to load anything from the recordset, I get run-time error 3265 error saying "Item cannot be found in the collection corresponding to the requested name or ordinal".

This is the coding to read something from the recordset, which usually works in other cases when I'm querying a table rather than querying a stored query.

If Not rs.EOF Then
rs.MoveFirst
MsgBox rs!Date
End If


Through the forum readings and internet browsing it seems that I have to use some "SET NOCOUNT ON" thing somewhere...but I just can't get it to work..

any help greatly appreciated, thanks.
 

Bat17

Registered User.
Local time
Today, 13:46
Joined
Sep 24, 2004
Messages
1,687
MsgBox rs!Date
If it is bombing on this line it may be because you are using a Reserved word for a field name. (date)

HTH

Peter
 

ray147

Registered User.
Local time
Today, 13:46
Joined
Dec 13, 2005
Messages
129
that sorted my problem...really had a stupid mistake...i should have seen that coz into the recordset i was loading PlanDespDate but i was trying to read Date...but sometimes your eyes just look out for complicated stuff rather than the basics...but thats life!

thanks!
 

Users who are viewing this thread

Top Bottom