[RESOLVED] [Excel 2003] ADO - Recordset with parameters?

[Ecniv]

Can I open it using the recordset object? If so, how?

(been a while since I've used ado - been coding in Accesss dao and got used to extra stuff ado doesn't support....)


I vaguely recall that I'd need a command object masquerading as a recordset object to use parameters...?

[Hack]

been coding in Accesss dao and got used to extra stuff ado doesn't support....)

I'd sure like to know what that might be.

Anyway, what kind of parameters are you referring to? Command parameters for parametized queries maybe?

If you can give me an example of what you are looking for I can probably give you an example of how to do it.

[Ecniv]

Yeah parameters for predefined queries.


I meant that within access dao you can use the function calls (month, year plus custom calls) whereas Excel to Access via ADO seems to error saying something wrong with my from clause.

The I found i cannot put the parameters in (sigh).

Any ideas?

-2147217900 Syntax error in FROM clause.

SELECT
 sq.Brand , sq.TheYear, sq.TheMonth
 , Sum(sq.DaysDiff) AS TotalDays, Count(sq.DaysDiff) AS NumberOfParts
 FROM 
 (SELECT Max(TB_PartsApp_PartsEscalationDataArchive.ArchivedDate) AS MaxOfArchivedDate
 , TB_PartsApp_PartsEscalationDataArchive.RefNo
 , TB_PartsApp_PartsEscalationDataArchive.Brand
 , Month([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]) AS TheMonth
 , Year([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]) AS TheYear
 , [TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]-[TB_PartsApp_PartsEscalationDataArchive].[DateIssued] AS DaysDiff
 , TB_PartsApp_PartsEscalationDataArchive.DateIssued
 FROM TB_PartsApp_PartsEscalationDataArchive
 WHERE
  ((Not TB_PartsApp_PartsEscalationDataArchive.ArchivedDate Is Null)
  And (TB_PartsApp_PartsEscalationDataArchive.RefNo Like 'chire%'))
 GROUP BY
 TB_PartsApp_PartsEscalationDataArchive.RefNo
 , TB_PartsApp_PartsEscalationDataArchive.Brand
 , Month([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate])
 , Year([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate])
 , [TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]-[TB_PartsApp_PartsEscalationDataArchive].[DateIssued]
 , TB_PartsApp_PartsEscalationDataArchive.DateIssued
 ) AS sq
 WHERE
 (sq.theyear=2009 AND sq.themonth<=3)
 OR (sq.theyear=2008 AND sq.themonth>3)
 GROUP BY
 sq.Brand, sq.TheYear, sq.TheMonth
 ORDER BY
 sq.Brand, sq.TheYear, sq.TheMonth

Note : I have cut n pasted the sql above into access and it runs... apart from changing the % to *

[dee-u]

Your sql works or not? Could you elabore on the problem?

[Ecniv]

Dee-u
I open a connect (that works) from Excel via ADO.
I open the sql pasted earlier (with the %) and get the error pasted.
I open the mdb direct in Access, paste in the Sql and change the % to *
It runs.

So I thought I would pre-define a query and put in parameters to filter it. But an ADODB.Recordset doesn't have parameters...

Command does, but I would need to change a lot of code to get that working without messing up existing code.

[dee-u]

It may not be due to those function calls, I tried this in Excel VBA and it worked.

Private Sub Test()
    Dim adoR        As ADODB.Recordset
    Dim strConn     As String
    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source= G:\Basic DataReport\db1.mdb;"
              
    Set adoR = New ADODB.Recordset
    adoR.Open "SELECT ID,a,b,c,month(d) as X FROM test", strConn, adOpenForwardOnly, adLockReadOnly
    
    Do While Not adoR.EOF
        Debug.Print adoR.Fields("X")
        adoR.MoveNext
    Loop
    'close and clean-up
    adoR.Close
    Set adoR = Nothing

End Sub

[Ecniv]

Ok... hmmm

Well I tried just the middle section:

 SELECT Max(TB_PartsApp_PartsEscalationDataArchive.ArchivedDate) AS MaxOfArchivedDate
 , TB_PartsApp_PartsEscalationDataArchive.RefNo
 , TB_PartsApp_PartsEscalationDataArchive.Brand
 , Month([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]) AS TheMonth
 , Year([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]) AS TheYear
 , [TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]-[TB_PartsApp_PartsEscalationDataArchive].[DateIssued] AS DaysDiff
 , TB_PartsApp_PartsEscalationDataArchive.DateIssued
 FROM TB_PartsApp_PartsEscalationDataArchive
 WHERE
  ((Not TB_PartsApp_PartsEscalationDataArchive.ArchivedDate Is Null)
  And (TB_PartsApp_PartsEscalationDataArchive.RefNo Like 'chire%'))
 GROUP BY
 TB_PartsApp_PartsEscalationDataArchive.RefNo
 , TB_PartsApp_PartsEscalationDataArchive.Brand
 , Month([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate])
 , Year([TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate])
 , [TB_PartsApp_PartsEscalationDataArchive].[ArchivedDate]-[TB_PartsApp_PartsEscalationDataArchive].[DateIssued]
 , TB_PartsApp_PartsEscalationDataArchive.DateIssued

This errors the same...

In Access... (with the % replaced) it... ...runs.



Could be a problem with the drivers / office / ms

[Ecniv]

Hello again..


recoded the opening to change from recordset to command. However, even though this works, how do i get the number of records returned (there is no recordcount) ?

Feels like MS forgot something here... one way or the other...

[dee-u]

How about COUNT?

[Ecniv]

Right uh...

<blank look>

I am feeling very thick... count? Is that like DCount function (I vaguely recall it as being a function not to use)?

So to recap...

- Command uses parameters, recordset doesn't
- Recorset can have a count, command you loop until EOF but dont know how long/many you will have. Without counting first. With parameters?

Doesn't sound like a solution, sorry.

Thanks for suggesting though.

[techgnome]

Wait... When you do a Command.Execute it returns a RECORDSET..... so how are you dealing with the data if not with a recordset? Maybe you should post the code you are using to set up and execute the command.

-tg

[Ecniv]

Due to network issues (ok problems) and lag sometimes the shared database files are locked...

So I wrote the following (but for recordsets) which is now adapted to commands plus parameters...

Public Function OpenCommandWithCatch(ByVal strSql As String, ByRef con As ADODB.Connection, ByRef cmd As ADODB.Command, ByVal strDB As String, Optional blnStatic, Optional blnQuery, Optional aryParams, Optional lngParams) As Boolean

' aryParam has to be x,y ...
'lngParams holds the number of parameters

    Dim lngAttempts As Long
    Dim blnOpened As Boolean
    Dim blnCancelled As Boolean
    Dim dteToTryAgain As Date
    Dim prm As ADODB.Parameter
    Dim lngOpStat As Long, lngOpType As Long
    Dim lngLoop As Long

'v1.0.8
'trying to fix the locking problem
    On Error Resume Next
    
    blnOpened = False
    Application.Cursor = xlWait
    
    Do Until blnOpened Or blnCancelled
        
        Set con = New ADODB.Connection
        con.ConnectionString = strDB
        con.Open
        
        lngOpStat = IIf(IsMissing(blnStatic), adOpenDynamic, adOpenStatic)
        lngOpType = IIf(IsMissing(blnQuery), adCmdText, adCmdTable)
        
'---- if the recordset object hasnt been initialise then start it
        If cmd Is Nothing Then Set cmd = New ADODB.Command
        
        cmd.ActiveConnection = con
        cmd.CommandText = strSql
        
        If Not IsMissing(lngParams) Then
            For lngLoop = 0 To lngParams - 1
                Set prm = New ADODB.Parameter
                prm.Name = (aryParams(lngLoop, 0))
                prm.Type = adInteger
                prm.Value = aryParams(lngLoop, 1)
                cmd.Parameters.Append prm
            Next
        End If
        
        Set prm = Nothing
        
        cmd.Execute
        
        
        If Err.Number = 0 Then
            blnOpened = True
        Else
'            If Err.Number = 3218 Then
'            End If
            If lngAttempts > 8 Then
                Debug.Print lngAttempts, "Errored: Main Chaps gui: " & vbCrLf & Err.Number & " - " & Err.Description
            End If
            Err.Clear
            lngAttempts = lngAttempts + 1
        End If
        
        If Not blnOpened Then
            If lngAttempts > 10 Then
                If MsgBox("I have tried for ten attempts to open the database (to open/save)." & vbCrLf & "Do you want me to try again?", vbYesNo + vbQuestion) = vbNo Then
                    Application.Cursor = xlDefault
                    blnCancelled = True
                End If
                lngAttempts = 0
                dteToTryAgain = Now + CDate("0:0:5")
                
                'wait for a bit - might clear up?
                Do Until Now > dteToTryAgain
                    DoEvents
                Loop
            End If
            Set con = Nothing
        End If
        
        DoEvents
    Loop
    Application.Cursor = xlDefault
    
    OpenCommandWithCatch = blnOpened
    
    On Error GoTo 0

End Function

Now, this returns a command object holding a recordset - however the recordset options such as .recordcount do not exist on the command object and it errors if i try to use them.

[techgnome]

Umm..... Okay.... first... I see where you execute the command.... but you're not getting the recordset.... and now that I've read your last statement twice more, I think I see where things have gone wrong here.... command.Execute is a FUNCTION... it RETURNS a recordset... but you're not grabbing it... you're allowing VB to throw your results away...

Set myRecordset = cmd.Execute <--- THAT will grab the recordset that is returned... from there you can use myRecordset to access your data.

The command object does NOT hold a recordset... it RETURNS it... it's up to you to set it to a variable so that you can use the recordset.

-tg

[Ecniv]

Ahh I see. A small but crucial detail

Right, let me put that in the code and see what happens, will post back later.



how did I not see/remember/know this...?

[Ecniv]

Right,

that partially worked. At least it ran and returned a recordset. However the recordset is empty (EOF set) when it should have some data. I think the parameters are adding correctly so this is very annoying :|

new code

Public Function OpenCommandWithCatch(ByVal strSql As String, ByRef con As ADODB.Connection, ByRef rst As ADODB.Recordset, ByRef cmd As ADODB.Command, ByVal strDB As String, Optional blnStatic, Optional blnQuery, Optional aryParams, Optional lngParams) As Boolean

' aryParam has to be x,y ...
'lngParams holds the number of parameters

    Dim lngAttempts As Long
    Dim blnOpened As Boolean
    Dim blnCancelled As Boolean
    Dim dteToTryAgain As Date
    Dim prm As ADODB.Parameter
    Dim lngOpStat As Long, lngOpType As Long
    Dim lngLoop As Long

'v1.0.8
'trying to fix the locking problem
    On Error Resume Next
    
    blnOpened = False
    Application.Cursor = xlWait
    
    Do Until blnOpened Or blnCancelled
        
        Set con = New ADODB.Connection
        con.ConnectionString = strDB
        con.Open
        
        lngOpStat = IIf(IsMissing(blnStatic), adOpenDynamic, adOpenStatic)
        lngOpType = IIf(IsMissing(blnQuery), adCmdText, adCmdTable)
        
'---- if the recordset object hasnt been initialise then start it
        If cmd Is Nothing Then Set cmd = New ADODB.Command
        
        cmd.ActiveConnection = con
        cmd.CommandText = strSql
        
        If Not IsMissing(lngParams) Then
            For lngLoop = 0 To lngParams - 1
                Set prm = New ADODB.Parameter
                prm.Name = (aryParams(lngLoop, 0))
                prm.Type = aryParams(lngLoop, 2) 'adInteger
                prm.Value = aryParams(lngLoop, 1)
                cmd.Parameters.Append prm
            Next
        End If
        
        Set prm = Nothing
        
        Set rst = cmd.Execute
        
        If Err.Number = 0 Then
            blnOpened = True
        Else
'            If Err.Number = 3218 Then
'            End If
            If lngAttempts > 8 Then
                Debug.Print lngAttempts, "Errored: Main Chaps gui: " & vbCrLf & Err.Number & " - " & Err.Description
            End If
            Err.Clear
            lngAttempts = lngAttempts + 1
        End If
        
        If Not blnOpened Then
            If lngAttempts > 10 Then
                If MsgBox("I have tried for ten attempts to open the database (to open/save)." & vbCrLf & "Do you want me to try again?", vbYesNo + vbQuestion) = vbNo Then
                    Application.Cursor = xlDefault
                    blnCancelled = True
                End If
                lngAttempts = 0
                dteToTryAgain = Now + CDate("0:0:5")
                
                'wait for a bit - might clear up?
                Do Until Now > dteToTryAgain
                    DoEvents
                Loop
            End If
            Set con = Nothing
        End If
        
        DoEvents
    Loop
    Application.Cursor = xlDefault
    
    OpenCommandWithCatch = blnOpened
    
    On Error GoTo 0

End Function

Edit:
I even amended the code and query to use only one parameter and it returns nothing. Running the query in Access and entering the same value returns records.

[dee-u]

Are you sure the parameters are going to their correct positions?

[Ecniv]

Hi,

On the amended query there is only one filter (on the year).
The parameter is being added without an error and when executed it is not complaining about not having enough parameters/missing data fields.

There isnt a way to see what it is sending/using... at least as far as I know.

[techgnome]

Two things:
1) On the command object, before you execute it, set the cursor location to client side
2) Right after the .Execute set the rst.ActiveConnection = Nothing - this will disconnect your recrordset and allow you to pass it back

What I think is happening is that the cursor is defaulted to Serverside, which means that the data isn't actually returned, but a pointer to it is. So each time you do a .MoveNext, it's a trip out to the server and back. When you closed the connection though, you severed the link, making the recordset invalid. But if you set the cursorlocation to Clientside, it'll actually return ALL of the data back to the client. At which point you can diconnect the recordset (by setting active connection to nothing) and close the connection (suggestion close the connection BEFORE setting it to nothing.)

-tg

[dee-u]

For testing purposes perhaps you can try a very simple query like the following if it will return any record.

Private Sub Test()
    Dim adoR As ADODB.Recordset
    Dim adoConn As ADODB.Connection
    
    Set adoConn = New ADODB.Connection
    adoConn.ConnectionString = "string"
    adoConn.Open
    
    Set adoR = New ADODB.Recordset
    With adoR
        .Open "SELECT * FROM Table", adoConn, adOpenForwardOnly, adLockReadOnly
        Do While Not .EOF
            Debug.Print .Fields(0)
            .MoveNext
        Loop
        .Close
    End With
    Set adoR = Nothing
    MsgBox "Done"
End Sub

[Ecniv]

Hiya,

I copied the code from Excel VBA to Access VBA. Access mdb being the one I want to run the query from.

I tweaked the connection etc to do that. Had to close the mdb file and reopen it as it locked itself (thanks windoze).

Then it ran..... exactly like the excel. ie it adds the parameter, the parameter matchs so the query runs but it returns not records. However running as a query and entering the same data (ThisYear = 2009) it returns records.

I have a suspicion its the ADODB part... just wondering if the company i work for (which IT doesn't seem to be set up right) might have older drivers in...

Wouldn't surprise me.

As a test I am going to write a short bit of code that runs the parameter on a DAO connection in the same db and see if that works....

post back shortly...

[Ecniv]

Hiya,

Public Sub s()
    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim p As DAO.Parameter
    Dim ps As DAO.Parameters
    
    Set qdf = DBEngine(0)(0).QueryDefs("qryRptParts")
    
    Set p = qdf.Parameters("ThisYear")
    p.Value = 2009
    
    Set rst = qdf.OpenRecordset
    Debug.Print rst.EOF
    rst.Close
    
    Set rst = Nothing
    Set qdf = Nothing
    Set p = Nothing
End Sub

The above code is what I used in Access VBA. It opens a recordset and has data.

I've changed the ADO references to v2.8 (was on v2.1). Re-ran the connection adodb function with the command and it runs but returns no records.

I can only therefore point an accusing finger at ADO ...

... or I can point at me for missing something crucial. Cant see what tho

I will have to do a long way around of extracting data, procesing it and putting the results into a new table. Then run reporting off that table. Longer way around, but at least the customer will be more happy...



Thanks for looking at this. If something comes to mind, please post on the bottom, or pm me