UtterAccess Discussion Forums > Passthrough Query To Sql Server 2008r2 As Report Recordsource

expatriate

Mar 19 2012, 08:43 PM

I seem to hit an error at every turn trying to do this. I've tried many constructs, currently

CODE

Private Sub Report_Load()
  Dim SQL              As String
  Dim qry            As QueryDef
    On Error Resume Next
    DoCmd.RunSQL "DROP TABLE qTemp"
    On Error GoTo procError

    Set qry = CurrentDb.CreateQueryDef("qtemp")
    qry.Connect = sConnODBC
    qry.SQL = "Exec test"

    qry.ReturnsRecords = True
    Set Me.Recordset = qry.OpenRecordset

This gives an error "only available in ADP". How do you guys set a passthrough query as a report's recordsource?

JuanSoto

Mar 19 2012, 09:17 PM

You almost have it, here's the right code:

CODE

  Dim SQL              As String
  Dim qry            As DAO.QueryDef
    On Error Resume Next
    DoCmd.RunSQL "DROP TABLE qTemp"
    On Error GoTo procError

    Set qry = CurrentDb.CreateQueryDef("qtemp")
    qry.Connect = sConnODBC
    qry.SQL = "Exec test"

    qry.ReturnsRecords = True

    'Use RecordSource not Recordset
    Set Me.RecordSource = qry.OpenRecordset

expatriate

Mar 19 2012, 09:34 PM

QUOTE (JuanSoto @ Mar 20 2012, 09:17 AM)

You almost have it, here's the right code:

CODE

  Dim SQL              As String
  Dim qry            As DAO.QueryDef
    On Error Resume Next
    DoCmd.RunSQL "DROP TABLE qTemp"
    On Error GoTo procError

    Set qry = CurrentDb.CreateQueryDef("qtemp")
    qry.Connect = sConnODBC
    qry.SQL = "Exec test"

    qry.ReturnsRecords = True

    'Use RecordSource not Recordset
    Set Me.RecordSource = qry.OpenRecordset

Hi Juan,

I tried that - gives me an error "invalid use of property"

datAdrenaline

Mar 19 2012, 10:46 PM

First off ...

There is no need to DROP and Re-create your Query object, it promotes bloat and increases risk of ??? needlessly.

Plus ...

You will need to use the Open event instead of the Load event since the Open event is the last opportunity to change the Recordsource property of a Report object.

CODE

Private Sub Report_Open(Cancel As Integer)
    Dim sSQL As String

    sSQL = "Exec test"

    With CurrentDb.QueryDef("qtemp")
        .Connect = sConnODBC
        .SQL = "Exec test"
        .ReturnsRecords = True
    End With

    Me.Recordsource = "qtemp"
End Sub

That should start you in the correct direction.

Edits:
PS> if qtemp is dedicated to the Report object, then you really don't need to set the Recordsource property --- you can set the RecordSource property at design time to qtemp, then just modify the .SQL property accordingly in the Open event.

expatriate

Mar 19 2012, 11:41 PM

Hi,

Tried it both as before and dedicated, on the open event. Same error. If i take out the 'set' as you did, then the error is: Type Mismatch.

Any ideas?

Thanks,

Peter

EDIT: query1 was tested and does work when run independently.

expatriate

Mar 20 2012, 02:44 AM

OK - I had changed it slightly. This does work except this should be querydefs, not querydef .

CODE

With CurrentDb.QueryDef("qtemp")

Thank you very much.

Peter

QUOTE (datAdrenaline @ Mar 20 2012, 10:46 AM)

First off ...

There is no need to DROP and Re-create your Query object, it promotes bloat and increases risk of ??? needlessly.

Plus ...

You will need to use the Open event instead of the Load event since the Open event is the last opportunity to change the Recordsource property of a Report object.

CODE

Private Sub Report_Open(Cancel As Integer)
    Dim sSQL As String

    sSQL = "Exec test"

    With CurrentDb.QueryDef("qtemp")
        .Connect = sConnODBC
        .SQL = "Exec test"
        .ReturnsRecords = True
    End With

    Me.Recordsource = "qtemp"
End Sub

That should start you in the correct direction.

Edits:
PS> if qtemp is dedicated to the Report object, then you really don't need to set the Recordsource property --- you can set the RecordSource property at design time to qtemp, then just modify the .SQL property accordingly in the Open event.

datAdrenaline

Mar 20 2012, 06:30 AM

>> If i take out the 'set' as you did, then the error is: Type Mismatch <<

Set is only used to set object variables, so there is no need for it since the code I presented is setting a property that is expecting a string. I know you likely figured this part out, but I wanted to explain the "why" behind the error you got.

>> OK - I had changed it slightly. This does work except this should be querydefs, not querydef . <<

... Sorry ... AIR CODE can sometimes be that way

-----

I am definately glad to here of your success! Good luck on your project!!

expatriate

Mar 20 2012, 06:37 AM

QUOTE (datAdrenaline @ Mar 20 2012, 06:30 PM)

>> If i take out the 'set' as you did, then the error is: Type Mismatch <<

Set is only used to set object variables, so there is no need for it since the code I presented is setting a property that is expecting a string. I know you likely figured this part out, but I wanted to explain the "why" behind the error you got.

>> OK - I had changed it slightly. This does work except this should be querydefs, not querydef . <<

... Sorry ... AIR CODE can sometimes be that way

-----

I am definately glad to here of your success! Good luck on your project!!

Thanks for everything, Brent, including the explanation. I really appreciate it. Oh, I caught the querydef(s) thing early - that did not cost me any time at all. I just wanted to correct it for the archives.