result from sql query in recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Back
Top