Create ADO recordset problem

[glgcag]

I have used DAO exclusively and I'm trying to learn ADO, but I'm having a bit of trouble simply creating a recordset based on a SQL statement. Here's the public function I created to test:

Public Function TestUser()
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Source = "SELECT * FROM tUsers WHERE tUsers.UserID = 5"

rst.Open Options:=adCmdText

If rst.RecordCount > 0 Then
Do Until rst.EOF
Debug.Print rst.Fields("Username")
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
End Function

The error message I receive at the "rst.open" line in the code is "No value given for one or more required parameters."

Any suggestions?

THANKS!

 

[bboffin]

Keep it simple to start with.

The Open method will default most parameters to sensible values so don't bother setting up the parameters separately, just try:

rst.Open "SELECT...", CurrentProject.Connection

In addition after opening a recordset the RecordCount property is generally not valid until all of the rows have been read.

The normal check for making sure that you have some records is:

If not (rst.BOF and rst.EOF) then   ' there are some records
.
.

Actually your Do Until loop will work fine if you remove the enclosing If statement

 

[sfreeman]

Try:

rst.Source = "SELECT * FROM tUsers WHERE tUsers.UserID = 5;"

HTH

 

[sfreeman]

OK, this is weird. I copied your original code and ran the function, getting exactly the same error message that you got. I thought you were missing the ';" at the end of the SQL, added it and re-ran the code, AND IT WORKED.

Just for laughs, copied your original code a second time, WITHOUT the ';', and it still works.... ???

 

[glgcag]

I have tried the most basic way of getting the recordset as you suggested bboffin, but that isn't working either. Also, sfreeman, I have tried it with a ; and without and it still doesn't work. I CAN however get it to return every record in the table as long as I don't include the WHERE clause in the SQL statement that is the source of the ADO recordset. (ie: "SELECT * FROM tUsers") Could I be using the wrong reference to the ADO library? I'm using Microsoft Active X Data Objects 2.7 library- is this OK and/or should I have something else?

This is really bugging me because it is so basic for retrieving data!

 

[sfreeman]

Strange, but this code works just fine for me. I am using Library 2.8. Tried it with 2.7, still works fine.

 

[RoyVidar]

What happens if you alter the syntax slightly, for instance:

[tt]with rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.open "SELECT * FROM tUsers WHERE tUsers.UserID = 5",,,,adcmdtext
...
end with[/tt]

or if you only need a forwardonly, readonly recordset:

[tt]set rst=currentproject.connection.execute( _
"SELECT * FROM tUsers WHERE tUsers.UserID = 5")[/tt]

And yes, the code should work on ADO 2.1 thru 2.8

But this error can also occur amongst others if you have a typo in one of the field names...

Roy-Vidar

 

[sna1maa14]

In case someone out there was wondering the soultion to this error as I was. Here it is "SELECT * FROM tUsers WHERE tUsers.UserID=5") It should look like this "select yourfields From yourtable Where yourfield= '5'"

There are some good examples of ADO at

http://www.w3schools.com/ado/ado_examples.asp

Sna1maa
"There is no such thing as a stupid question, just stupid people!"