• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

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.
0
andyw27
Asked:
andyw27
  • 7
  • 5
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
test this sample code, will return recordset

Public Function GetADORecordset(ByVal strSQL As String) As ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim rst As ADODB.Recordset
    
    Set cn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cn
    
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    rst.LockType = adLockReadOnly
    rst.Open strSQL
    
    Set rst.ActiveConnection = Nothing
    
    Set GetADORecordset = rst
    
End Function

Open in new window



you can use the function to load a combo box with recordset


Set Me.comboName.Recordset = GetADORecordset("SELECT * FROM NameOfTable")

Open in new window



0
 
andyw27Author Commented:
Thanks that great.

One further question, how can I display the results within a label control?
0
 
Rey Obrero (Capricorn1)Commented:
you will use the LabelName.caption="ReturnedValue"
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 Gonna need a little more code or a modification to that.  The function as it stands returns a recordset, not a field value.

JimD.
0
 
andyw27Author Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
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?
0
 
andyw27Author Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
play with the function posted above and post back when you encounter a problem
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
andyw27Author Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
it will help if you will post the codes you are using.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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

0
 
andyw27Author Commented:
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)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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("myFieldNameToReturn", 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.




0
 
andyw27Author Commented:
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?
0
 
andyw27Author Commented:
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.  
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Sorry, I should have realized you had both in your project.

This line:

Dim rsLk As Recordset

Should be:

Dim rsLK As ADO.Recordset

  When ever you have both ADO and DAO in a project, you should always be explicit about declaring variables.  ie.

  Dim db As DAO.Database
  Dim rst1 As DAO.Recordset
  Dim rst2 As ADO.Recordset

  By doing this, the order of references don't matter.

JimD.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now