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
Re: Retreiving a record using ADO
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
Re: Retreiving a record using ADO
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
Re: Retreiving a record using ADO
Re: Retreiving a record using ADO
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
Re: Retreiving a record using ADO
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.
Re: Retreiving a record using ADO
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!
Re: Retreiving a record using ADO
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.
Re: Retreiving a record using ADO
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
Re: Retreiving a record using ADO
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!