Retreiving a record using ADO

[blakemckenna]

I am seeking to retrieve a record from a listbox where I may edit the entire record. I am not sure what the syntax is for doing this where I am using the key value of the recordset as the lookup value. Using DAO it would be the same as:

rsTable.index = "PrimaryKey"
rsTable.Seek "=", keyValue

What is the syntax for retrieving a record using ADO?

Thanks,
Blake

[Wokawidget]

Private Const CONN_STRING As String = "your DB connection string"

Public Function GetUser(Byval plngID As Long) As Recordset
Dim adoRec  As Recordset
   Set adoRec = New Recordset
   adoRec.Open "SELECT * FROM Users WHERE UserID = " & plngID, CONN_STRING
   Set GetUser = adoRec
   Set adoRec = Nothing
End Function

Is that what you meant?

Woka

[szlamany]

Woka,

Curious - why don't you use...

Private Const CONN_STRING As String = "your DB connection string"

Public Function GetUser(Byval plngID As Long) As Recordset
Dim adoRec  As ADODB.Recordset
   Set adoRec = New ADODB.Recordset
   adoRec.Open "SELECT * FROM Users WHERE UserID = " & plngID, CONN_STRING
   Set GetUser = adoRec
   Set adoRec = Nothing
End Function

[Wokawidget]

Not following you.

WOka

[szlamany]

Your use of a generic RECORDSET object, as opposed to the ADODB.Recordset object. Are they the same? We always use ADODB.Connection, ADODB.Command and ADODB.Recordset

[RhinoBull]

If your application has more than one reference to some db libraries (DAO, ADO - people do that for one reason or the other) then you have to explicitly tell compiler which is what - other than that you don't have to specify DAO or ADODB at all.

[szlamany]

If your application has more than one reference to some db libraries (DAO, ADO - people do that for one reason or the other) then you have to explicitly tell compiler which is what - other than that you don't have to specify DAO or ADODB at all.

So, if someday down the road you needed to add another DB LIBRARY (for one reason or the other) then you would need to correct all those references...

I'm glad I'm being explicit!

[RhinoBull]

So, if someday down the road you needed to add another DB LIBRARY (for one reason or the other) then you would need to correct all those references...

I'm glad I'm being explicit!

Yea, that's a good way ... I personally always use explicit declarations as well.

[Wokawidget]

I understand this, and I do myself sometimes declare caribles like that.

However, I have not come across a situation yet, where we have had 2 objects named the same.
And a quick global replace of "As Recordset" to "As ADODB.Recordset", and New Recordset" to "New ADODB.Recordset" should do the trick.

But yes, I agree with you

While we are on the subject, and before anyone mentions it, yea yea, I know, I missed out "Option Explicit" from the code I posted above

Woka

[szlamany]

And since it's be critical of Woka night

adoRec.Open "SELECT * FROM Users WHERE UserID = " & CStr(plngID), CONN_STRING

Always use datatype conversion functions - coercion is evil!