Return VBA ADO recrordset to Access Query

[JTBorton]

Access 2007, ADO Library 6.1
I am trying to use a vba function to build an ADODB recordset based on various inputs. I want to return this data to a query in access. However it does not seem to accept an entire recordset from a userdefined function. Here is a very simple example of what I am trying to do with a much more complex VBA recordset:

VBA User defined Function

Option Compare Database
Option Explicit
Option Base 1

Public Function DetermineUnits() As ADODB.Recordset
    Dim rstRecords  As ADODB.Recordset
    Dim cnnProj     As ADODB.Connection
    Dim strSQL      As String

    Set cnnProj = CurrentProject.Connection
    strSQL = "SELECT [tblUnits].[ID], [tblUnits].[Abbreviation] FROM tblUnits WHERE [tblUnits].[Measurement Type] < 3 ORDER BY [Abbreviation];"
    Set rstRecords = New ADODB.Recordset
    rstRecords.Open strSQL, cnnProj 
    Set DetermineUnits = rstRecords
    
    rstRecords.Close
    Set rstRecords = Nothing
End Function

Query Definition in Access

SELECT * FROM DetermineUnits();

This of course gives me a syntax error in the FROM clause.

Any ideas how to do this??



-Joshua
If it's not broken, it doesn't have enough parts yet.

 

[Golom]

If your Query Definition SQL is stored as a query (as opposed to being run from VBA) then you need to set up another query (lets call it DetU) with some dummy SQL. Then in your DetermineUnits routine.

Public Sub DetermineUnits(Optional ByVal ViewName as String = "DetU") 
    
    Dim cat         As New ADOX.Catalog
    Dim cmd         As New ADODB.Command
    Dim strSQL      As String

    cat.ActiveConnection = CurrentProject.AccessConnection  
    
    [red]' This routine assumes that the query "ViewName" already exists.[/red]

    strSQL = "SELECT [ID], [Abbreviation] FROM tblUnits " & _
             "WHERE [Measurement Type] < 3 ORDER BY [Abbreviation];"

    ' Get the query
    Set cmd = cat.Procedures(ViewName).Command
   
    ' Update the SQL
    cmd.CommandText = strSQL
   
    ' Save the updated query
    Set cat.Procedures(ViewName).Command = cmd
   
    Set cat = Nothing

End Sub

And your Access stored query is just

Select * From DetU

 

[JTBorton]

Golom,

Thanks for the reply. Allow me to clarify my request. The query is not being run from VBA, this is a query from the Query design wizard. I am trying to modify the SQL so that the query pulls from a recordset formed by a VBA function. This query will referenced in many other locations, so it is not a query that is displayed through a VBA command. Does that make more sense?

Thanks

-Joshua
If it's not broken, it doesn't have enough parts yet.

 

[Golom]

That's what I thought ...

A query (which is what you created in the query design wizard) is an SQL statement and it may be used to generate a recordset in VBA by running the query ... HOWEVER ...

Query SQL and a recordset are completely different creatures and you cannot combine one with the other. Until you actually run it in VBA, a query is only the SQL statement. It is [red]NOT[/red] the records that would be generated when the SQL runs.

You can modify the query's SQL from VBA and that's probably what you want to do.

 

[Andrzejek]

I am just wondering….
Since you do not pass anything to the function DetermineUnits, it always returns the same recordset, which then you want to query. And by query I mean: filter. Is that correct?

If so, why don’t you create one global recordset with all the records you will need, and wherever you want the sub-set of it, just open it and filter it.

Just a suggestion, unless I am way off…


Have fun.

---- Andy

 

[JTBorton]

Andrzejek,

This is just a dumbed-down example that I threw together to illustrate what I am trying to accomplish. The actual query that I want to run will have require parameters and will perform other mass balance calculations within it. I didn't want to garble up the question with a bunch of code.

Golom,

So if I understand correctly, your saying I create the query in the design wizard and then use VBA to modify the SQL as needed. Is that correct? So if I do it that way then how can I trigger the vba code to run when data changes, for example if I am using this query to filter options in a Lookup Wizard for a data table? Thanks again for your time and patience.

-Joshua
If it's not broken, it doesn't have enough parts yet.