Solved

Best Practice – ADO Function

Posted on 2011-03-18
17
316 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
Comment Utility
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
Comment Utility
Thanks that great.

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

Expert Comment

by:Rey Obrero
Comment Utility
you will use the LabelName.caption="ReturnedValue"
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
play with the function posted above and post back when you encounter a problem
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
Comment Utility
it will help if you will post the codes you are using.
0
 
LVL 57

Expert Comment

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

Expert Comment

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

743 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

13 Experts available now in Live!

Get 1:1 Help Now