Retreiving a record using ADO

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:

Code:

---

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

---

What is the syntax for retrieving a record using ADO?

Thanks,
Blake

Code:

---

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

Woka,

Curious - why don't you use...

Code:

---

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

---

Not following you.

WOka

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

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.

Quote:

---

Originally Posted by 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.

---

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!

Quote:

---

Originally Posted by szlamany

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.

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

And since it's be critical of Woka night

Code:

---

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

---

Always use datatype conversion functions - coercion is evil!