Upsizing reports based on QueryDef to SQL Server

[jerryk]

Hi,

I am upsizing an application that uses a querydef to drive reports.
The Querydefs are dynamically created prior to launching the report.
The report then uses the querydef as it's data source.

The code is of the form:

SQL = "Select ....WHERE ID = " & passedID & " AND Type = " & PassedType


Set Myqry = db.CreateQueryDef("rptQry", SQL)

Set qr = db.OpenRecordset("rptQry", , dbSeeChanges)
If qr.EOF Then
Msgstr = "No records " & str(passedID )
Exit Function
End If
qr.close
db.close

' Open report based on querydef
DoCmd.OpenReport "MyReport", acViewPreview


Since switching to SQL Server as the database I am getting the error:

"You must use the dbSeeChanges option with OpenRecordset when accessing
a SQL Server table that has an IDENTITY column."

A simple addition of dbSeeChange did not fix the problem. So I am
wondering if there is a better approach. Since there is a bit of code,
I prefer to continue to use the QueryDef if possible.

Thanks,

jerry

 

[SA]

Jerry:

What happens if you add the dbOpenSnapShot to the recordset type. This will
likely eliminate the requirement for dbSeeChanges, since it becomes a static
read only cursor on the server. However, using Jet's dao on a sql server
really has its draw backs. Traditional Jet DAO is totally a local service,
drawing all the data across the network for the Jet engine to parse with
each call. You can speed the app up if you use views on the server (which
parses up the data on the server and only passes back the result set and
use ) or use DAO "sql pass through" queries and TSQL to again keep the
server parsing up the data.

 

[jerryk]

Jerry:

What happens if you add the dbOpenSnapShot to the recordset type. This will
likely eliminate the requirement for dbSeeChanges, since it becomes a static
read only cursor on the server. However, using Jet's dao on a sql server
really has its draw backs. Traditional Jet DAO is totally a local service,
drawing all the data across the network for the Jet engine to parse with
each call. You can speed the app up if you use views on the server (which
parses up the data on the server and only passes back the result set and
use ) or use DAO "sql pass through" queries and TSQL to again keep the
server parsing up the data.

Thanks SA,

In further reading of the code I realize that I could easily split you
the code and send the Select string off th SQL Server via ADO. As you
mentioned, this speed things up.

Can I assume that if I change the QueryDef user by the report to be a
pass-through that the report will still run?

jerry