Solved

Best Practice – ADO Function

Posted on 2011-03-18
17
329 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 35165072
you will use the LabelName.caption="ReturnedValue"
0
 
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 35165725
play with the function posted above and post back when you encounter a problem
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 119

Expert Comment

by:Rey Obrero
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now