Access 2007 - OpenRecordset(CustomSQLString, dbOpenDynaset, dbReadOnly) same speed

[Rx_]

Not a problem - just an observation
Access 2007 - kind of interesting that the Read Only for this did not seem to speed things up when adding a timer at the beginning and Set myQDF = Nothing.
My test was using a single user, where perhaps when the database is being hit by a dozen users, it might make a difference.

Back in Access 97 days, there was some idea that read only was much faster.

Private Sub cmd_EntireBusiness_Click() 
Dim myQDF As DAO.QueryDef 
Dim myDBS As DAO.Database 
Dim rstMgr As DAO.Recordset 
Dim CustomSQLString As String, strTemp As String, strMgr As String 
Const strFileName As String = "CustomerID" 
Const strQName As String = "QueryToExport" 
Set myDBS = CurrentDb 
strTemp = myDBS.TableDefs(0).Name 
CustomSQLString = "SELECT * FROM [" & strTemp & "] WHERE 1=0;" 
Set myQDF = myDBS.CreateQueryDef(strQName, CustomSQLString) 
myQDF.Close 
strTemp = strQName 
CustomSQLString = "SELECT DISTINCT [CustomerManager].[Store Billing] FROM qry_EntireBusiness;" 
Set rstMgr = myDBS.OpenRecordset(CustomSQLString, dbOpenDynaset, dbReadOnly) 
If rstMgr.EOF = False And rstMgr.BOF = False Then 
rstMgr.MoveFirst 
Do While rstMgr.EOF = False 
strMgr = DLookup("[CustomerManager].[Store Billing]", 
"[CustomerManager]", "[Store Billing] = '" & rstMgr![Store Billing].Value & 
"'") 
CustomSQLString = "SELECT * FROM qry_EntireBusiness WHERE " & "[Store Billing] = " & strMgr & ";" 
Set myQDF = myDBS.QueryDefs(strTemp) 
myQDF.Name = strMgr 
strTemp = myQDF.Name 
myQDF.SQL = CustomSQLString 
myQDF.Close 
Set myQDF = Nothing 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _ 
strTemp, "C:\Documents and Settings\Rx\Desktop\StudentID DB\" & strFileName & ".xls" 
rstMgr.MoveNext 
Loop 
End If 
rstMgr.Close 
Set rstMgr = Nothing 
myDBS.QueryDefs.Delete strTemp 
myDBS.Close 
Set myDBS = Nothing

 

[Galaxiom]

The Dynaset has big overheads because it maintains an active connection and monitors changes. ReadOnly is about whether the recordset can accept changes and that won't affect this.

Maybe you are thinking of dbOpenSnapshot. This is an alternative to dbOpenDynaset and it makes a considerable speed difference.

The fastest is dbOpenForwardOnly but as sugested by the name it can only be browsed in the forward direction.

 

[Rx_]

FYI: Tried this on my final report. It is a complex report with 14 K records returned.
Just making a comment that on Access 2007, there does not seem to be a difference.
Using a VBA timer to measure the start and stop:
A dynaset takes 63 seconds
A Snapshot takes 63 seconds
Avoided a Forward Only snapshot because the Recordcount property is not available and this is used in the Excel automation later to format the data area.

In this case, the entire query must be run, then the result is copied into a new variable instacne of Excel:

Set rsDataSundries = CurrentDb.OpenRecordset(strSQLSundries, dbOpenSnapshot, dbReadOnly)

 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsDataSundries

This is after a compact and repair of both the Front End and the Back End (to improve indexing)
This is one of many good articles about the speed. Part of what it discusses is the query return inside of Access itslef - a snapshot waits until completed to show the first record so it could be slower.

http://www.fmsinc.com/tpapers/faster/#Microsoft Access Module/VBA Performance Tips

Use Snapshots When Appropriate

Don't open Dynaset type recordset object on SQL database tables unless you need to add or edit records, or need to see the changes made by other users. Instead, consider using Snapshot recordsets which can be faster to scroll through. Of course, Snapshot recordsets can take longer to open since they require a full read of the source data.
Use Dynasets for Large Record Sets

If you need to retrieve a large number of records, use a Dynaset instead of a Snapshot. Snapshot type recordsets must load all records from the data source before becoming available, whereas Dynasets are available as soon as the first 20 or so records are retrieved. Also, when using a Snapshot against large ODBC data sources, you run the risk of running out of disk space on your local computer. This is because all data is downloaded into RAM until the amount of RAM is exhausted. Then, the database engine creates a temporary database to store the contents of the snapshot. In a nutshell, when you open a snapshot, you need at least as much disk space as the largest table you are opening.

 

[Galaxiom]

Well there you go. Timed results tell the true picture. I guess the snapshot is more about saving resources at the back end then?

Great link too, once the http duplication is removed. You might want to fix that.

 

[Rx_]

Opps, thanks the hyperlink is fixed now.

Since this application is using an Access 2007 Linked Table and my last decade of Access used ODBC, Native, or ADO to SQL Server 2000 or later, the article does discuss the advantages of ODBC or ADO.

And, I seem to remember on the old Microsoft Access 97 Programming certification exam (it's retired now, so I shouldn't be giving away any propritary secrets) specifically had questions on the snapshot vs dynaset.

It is possible this is a uniquely Access linked to Accesss DB issue.
Those using SQL Server should be encouraged to use Snapshots.