?
Solved

Best Practice – ADO Function

Posted on 2011-03-18
17
Medium Priority
?
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 58
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 58
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 58
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 58
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

777 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