result from sql query in recordset

E

Eric

Hi,

I have searched and tried the examples in Access help and this newsgroup, I
just can't get it to work...

I want to query a table in access. The results must appear in a recordset.
However, I never get any result (error: Item cannot be found in the
collection corresponding to the requested name or ordinal).

----------------------------------------------------------------
Dim conDatabase As ADODB.Connection
Dim strSQL As String

Set conDatabase = CurrentProject.Connectionstr
SQL = "SELECT OrderID FROM Orders WHERE CustomerID = " & CustomerID & "
ORDER BY OrderDate;"

Dim test As Recordset
Set test = conDatabase.Execute(strSQL)
conDatabase.Close
Debug.Print test(0)
test.Close
----------------------------------------------------------------


Any idea's how to get the result in the recordset?

Many thanks,

Eric
The Netherlands
 
D

Dave Patrick

Air code but try something like this.

Dim conDatabase As ADODB.Connection
Dim strSQL As String
Dim rs As ADODB.Recordset

Set conDatabase = CurrentProject.Connection
strSQL = "SELECT OrderID FROM Orders WHERE CustomerID = '" & CustomerID & "'
" _
& "ORDER BY OrderDate;"


Set rs = New ADODB.Recordset
rs.Open strSQL, conDatabase, adOpenForwardOnly, adLockReadOnly
Do While rs.EOF = False
MsgBox rs!OrderID
rs.MoveNext
Loop
rs.Close
conDatabase.Close

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
|
| I have searched and tried the examples in Access help and this newsgroup,
I
| just can't get it to work...
|
| I want to query a table in access. The results must appear in a recordset.
| However, I never get any result (error: Item cannot be found in the
| collection corresponding to the requested name or ordinal).
|
| ----------------------------------------------------------------
| Dim conDatabase As ADODB.Connection
| Dim strSQL As String
|
| Set conDatabase = CurrentProject.Connectionstr
| SQL = "SELECT OrderID FROM Orders WHERE CustomerID = " & CustomerID & "
| ORDER BY OrderDate;"
|
| Dim test As Recordset
| Set test = conDatabase.Execute(strSQL)
| conDatabase.Close
| Debug.Print test(0)
| test.Close
| ----------------------------------------------------------------
|
|
| Any idea's how to get the result in the recordset?
|
| Many thanks,
|
| Eric
| The Netherlands
 
Top