andyw27
asked on
Best Practice – ADO Function
Hi,
Can somebody give me an example of an ADO Access VBA function that can take SQL string as a parameter return the results?
For the time being the results are likely to be a single value? But it would be interesting to see how multiple results could be accessed.
Thanks.
Can somebody give me an example of an ADO Access VBA function that can take SQL string as a parameter return the results?
For the time being the results are likely to be a single value? But it would be interesting to see how multiple results could be accessed.
Thanks.
ASKER
Thanks that great.
One further question, how can I display the results within a label control?
One further question, how can I display the results within a label control?
you will use the LabelName.caption="Returne dValue"
Gonna need a little more code or a modification to that. The function as it stands returns a recordset, not a field value.
JimD.
ASKER
Okay, still having a little trouble understanding?
So I call the function:
Dim rst As ADODB.Recordset
rst = GetADORecordset ("SELECT requirement FROM T1 WHERE id = '" & GBL_Node & "' ;")
(Need to include a variable within the SQL statement)
The function runs and returns a recordset object. That recordset contains the results and then you interrogate that recordset to get the results?
So I call the function:
Dim rst As ADODB.Recordset
rst = GetADORecordset ("SELECT requirement FROM T1 WHERE id = '" & GBL_Node & "' ;")
(Need to include a variable within the SQL statement)
The function runs and returns a recordset object. That recordset contains the results and then you interrogate that recordset to get the results?
andyw27,
better if you will tell us what you want to display to the label.
are you after a single value coming from the table?
why do you need ADO, where you can simply use a DLookup() function?
better if you will tell us what you want to display to the label.
are you after a single value coming from the table?
why do you need ADO, where you can simply use a DLookup() function?
ASKER
I have a requirement to have lots of SQL statements in my database. I could code all these separately however this would be inefficient. Instead I would like to have one function that I can call and it returns the results that I can then display in various controls.
play with the function posted above and post back when you encounter a problem
<<The function runs and returns a recordset object. That recordset contains the results and then you interrogate that recordset to get the results?>>
Yes, that's it exactly. You can apply a WHERE clause to the SQL so that the recordset returns only one record, but you would then need to look at the record to get the value.
I would keep the routine that cap posted as is, as it is a very usefull building block to have and write another routine to perform the function of a DLookup() (return a field value), which calls it to get the recordset. Something like this:
Public Function myDLookUp(ByVal strField As String, ByVal strDomain As String, _
Optional strCriteria As Variant)
Dim rsLk As Recordset
Dim strSql As String
myDLookUp = Null
If Not IsMissing(sCriteria) Then
strSql = "select " & strField & " From " & strDomain & " Where " & strCriteria
Else
strSql = "select " & strField & " From " & strDomain
End If
Set rsLk = GetADORecordset(strSql)
If Not rsLk.EOF Then myDLookUp = rsLk(0)
rsLk.Close
End Function
JimD.
Yes, that's it exactly. You can apply a WHERE clause to the SQL so that the recordset returns only one record, but you would then need to look at the record to get the value.
I would keep the routine that cap posted as is, as it is a very usefull building block to have and write another routine to perform the function of a DLookup() (return a field value), which calls it to get the recordset. Something like this:
Public Function myDLookUp(ByVal strField As String, ByVal strDomain As String, _
Optional strCriteria As Variant)
Dim rsLk As Recordset
Dim strSql As String
myDLookUp = Null
If Not IsMissing(sCriteria) Then
strSql = "select " & strField & " From " & strDomain & " Where " & strCriteria
Else
strSql = "select " & strField & " From " & strDomain
End If
Set rsLk = GetADORecordset(strSql)
If Not rsLk.EOF Then myDLookUp = rsLk(0)
rsLk.Close
End Function
JimD.
ASKER
Hi thanks for the code.
I can't seem to get it to work. I keep getting an:
Run-time error '13':
Type mismatch
Error message.
Even when I pass it exactly the same SQL string as the previous function
I can't seem to get it to work. I keep getting an:
Run-time error '13':
Type mismatch
Error message.
Even when I pass it exactly the same SQL string as the previous function
it will help if you will post the codes you are using.
If you've already formed the complete SQL statement, then you would want this:
Public Function myDLookUp(ByVal strField As String, ByVal strSQL As String)
Dim rsLk As Recordset
myDLookUp = Null
Set rsLk = GetADORecordset(strSql)
If Not rsLk.EOF Then myDLookUp = rsLk(0)
rsLk.Close
Set rsLK = nothing
End Function
Public Function myDLookUp(ByVal strField As String, ByVal strSQL As String)
Dim rsLk As Recordset
myDLookUp = Null
Set rsLk = GetADORecordset(strSql)
If Not rsLk.EOF Then myDLookUp = rsLk(0)
rsLk.Close
Set rsLK = nothing
End Function
ASKER
Okay I have this:
Public Function myDLookUp(ByVal strSQL As String)
Dim rsLk As Recordset
myDLookUp = Null
Set rsLk = GetADORecordset(strSQL)
If Not rsLk.EOF Then myDLookUp = rsLk(0)
rsLk.Close
Set rsLk = Nothing
End Function
and I call with this:
Dim test As String
test = myDLookUp("SELECT original_requirement FROM Table1 WHERE identifier = '" & GBL_Node_Name & "' ")
But this results in a Type Mismatch error on this line:
Set rsLk = GetADORecordset(strSQL)
Public Function myDLookUp(ByVal strSQL As String)
Dim rsLk As Recordset
myDLookUp = Null
Set rsLk = GetADORecordset(strSQL)
If Not rsLk.EOF Then myDLookUp = rsLk(0)
rsLk.Close
Set rsLk = Nothing
End Function
and I call with this:
Dim test As String
test = myDLookUp("SELECT original_requirement FROM Table1 WHERE identifier = '" & GBL_Node_Name & "' ")
But this results in a Type Mismatch error on this line:
Set rsLk = GetADORecordset(strSQL)
You need it like this:
Public Function myDLookUp(ByVal strField As String, ByVal strSQL As String) As Variant
Dim rsLk As Recordset
myDLookUp = Null
Set rsLk = GetADORecordset(strSql)
If Not rsLk.EOF Then myDLookUp = rsLk(0)
rsLk.Close
Set rsLK = nothing
End Function
And call it like this:
Dim varRet as Variant
Dim strSQL as string
strSQL = "SELECT original_requirement FROM Table1 WHERE identifier = '" & GBL_Node_Name & "';"
varRet = myDLookUp("myFieldNameToRe turn", strSQL)
Replacing "myFieldNameToReturn" with the name of the field you want returned out of the record.
The reason for the mismatch was that myDlookup() was returning a Variant (because it needs to handle null cases) and you had test defined as a string.
I added "As Variant" to the function to make that clear.
JimD.
Public Function myDLookUp(ByVal strField As String, ByVal strSQL As String) As Variant
Dim rsLk As Recordset
myDLookUp = Null
Set rsLk = GetADORecordset(strSql)
If Not rsLk.EOF Then myDLookUp = rsLk(0)
rsLk.Close
Set rsLK = nothing
End Function
And call it like this:
Dim varRet as Variant
Dim strSQL as string
strSQL = "SELECT original_requirement FROM Table1 WHERE identifier = '" & GBL_Node_Name & "';"
varRet = myDLookUp("myFieldNameToRe
Replacing "myFieldNameToReturn" with the name of the field you want returned out of the record.
The reason for the mismatch was that myDlookup() was returning a Variant (because it needs to handle null cases) and you had test defined as a string.
I added "As Variant" to the function to make that clear.
JimD.
ASKER
Thanks for updated code.
I've added it exactly as you have typed it, however I'm still getting a type mismatch error?
Not sure what else to try?
I've added it exactly as you have typed it, however I'm still getting a type mismatch error?
Not sure what else to try?
ASKER
Ah finally figured out what was wrong. Your code was perfect however it would appear that the order of the references matters.
I moved ‘Microsoft ActiveX Data Objects 2.5 Library’ so that it has a higher priority than ‘Microsoft DAO 3.6 Object Library’ and the code works really well.
Thanks everyone for your continued help.
I moved ‘Microsoft ActiveX Data Objects 2.5 Library’ so that it has a higher priority than ‘Microsoft DAO 3.6 Object Library’ and the code works really well.
Thanks everyone for your continued help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
you can use the function to load a combo box with recordset
Open in new window