SQL against query fails

[TinkerMan]

HI

I'm having a strange problem. I found no posts on this, so I guess I'm doing a real rookie mistake.

I have this function (simplified):

Function someFunction(aDate As Date)

    Dim rst As New ADODB.Recordset
    Dim rstSQL As String
    rstSQL = "SELECT col FROM [query];"
    MsgBox rstSQL
    
    rst.Open rstSQL, CurrentProject.Connection , adOpenForwardOnly, adLockPessimistic
    MsgBox "Rader= " & rst.RecordCount
    
End Function

This runs fine if it selects from a table or a simple query, but it fails to run against my union query. It gives the error message:
Run-time error '-2147217904 (80040e10)': No value given for one or more required parameters.

The query I want to do a select against is getting its data from a hierarchy of queries:
Query1 gets data from two tables
Query2a and Query2B is a crosstab of Query1 (one for each type)
I then need to do an outer join between the two:
Query3a is a left join of Query2a and Query2b
Query3b is a right join of Query2a and Query2b
Query4 is a union of Query3a and Query3b. (phew)

In addition Query1 uses a date as a constraint, that is coming from a form (that is open when this executes). In addition Query2a and Query2b have this defined as a parameter, so it's passed down to Query1.

I know this query works (Query4) as I can open it manually, and just for kicks I added a subform to my form that was selecting from the same query, and it is showing the data perfectly.

Is there some initialisation I need to do before selecting from this query?

Any help is appreciated

 

[modest]

Try opening a connection first.

Function someFunction(aDate As Date)
    Dim con As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rstSQL As String

    rstSQL = "SELECT col FROM [query];"

    With con
        .ConnectionString = CurrentProject.Connection
        .CursorLocation = adUseClient
        .Attributes = .Attributes Or adXactCommitRetaining
        .Open
    End With
    
    rst.Open rstSQL, CurrentProject.Connection , adOpenForwardOnly, adLockPessimistic

    If (rst.BOF and rst.EOF) Then
        Exit Function
    End If

    rst.MoveLast
    rst.MoveFirst
    MsgBox "Rader= " & rst.RecordCount

    con.Close
    Set con = Nothing
    
End Function

If this doesn't help... then change the parameters of your .Open Just remember that ADO isn't as good with joins and recordsets as DAO is.

 

[TinkerMan]

Solved

Thanks modest for your suggestion. unfortunately it did not work, as the connection already was open(!).

I have twisted and turned this in all angles, but fortunately I found the problem: It turned out to be the parameter to the underlying queries. The parameter query was refering to a date combo in a form (open in background), but for some reason did not make it down the chain of queries. I used Pat's DAO recepie as it handles parameters explicitly. It is mentioned there that ADO does not expose the parameters directly, so DAO must be used. The following works:

function x(dato as Date)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!query ' name of query
qd.Parameters![Dato] = dato
Set rst = qd.OpenRecordset
MsgBox "Rader=" & rst.RecordCount
end function

Thanks for an excellent forum
Any commercial support department would NOT get up to its ancles, neither for response time, the amount of knowledge nor expertise