Solved

Best Practice – ADO Function

Posted on 2011-03-18
17
337 Views
Last Modified: 2012-05-11
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
Comment
Question by:andyw27
  • 7
  • 5
  • 5
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35164861
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
 

Author Comment

by:andyw27
ID: 35165018
Thanks that great.

One further question, how can I display the results within a label control?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35165072
you will use the LabelName.caption="ReturnedValue"
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 57
ID: 35165121

 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
 

Author Comment

by:andyw27
ID: 35165145
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35165212
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
 

Author Comment

by:andyw27
ID: 35165318
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35165725
play with the function posted above and post back when you encounter a problem
0
 
LVL 57
ID: 35166090
<<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
 

Author Comment

by:andyw27
ID: 35179985
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35179998
it will help if you will post the codes you are using.
0
 
LVL 57
ID: 35180153
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
 

Author Comment

by:andyw27
ID: 35180688
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
 
LVL 57
ID: 35181455
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
 

Author Comment

by:andyw27
ID: 35188211
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
 

Author Comment

by:andyw27
ID: 35188548
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 35189594
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Ms Access VBA Variables 6 27
Access Update Query 1 20
Access 2003 query lost it's only join 7 27
2 IIF's in Access query 25 28
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question