ADO: Open Recordset Optimization

[jamiedora]

Hello,

I am in the process, and also a 'newbie' to ADO/DAO programming, of
converting our backend database to SQL server and linking tables to a
front end Access mde file (with the VBA automation code and forms). In
doing so, and after reserching the many postings, it seems that I need
to migrate much of my DAO code to ADO. Mainly, it seems that this
greatly improves performance. However, although I have migrated much
code, from DAO to ADO, there is still a performance hit. From the
research on these forums I have several options (with the objective of
opening a recordset for viewing, adding/deleting records), from which I
wish to ask advice from the experts as to which choice is optimal:

1.) Use strict SQL commands vs. ADO for retrieving data.
2.) Don't migrate to ADO, continue using DAO.
3.) Use the following method for opening a recordset:

Dim rst As New ADODB.Recordset 'New for SQL server
Set db = CurrentProject.Connection
sqlCode = "SELECT * FROM [PRODUCTCONFIGURATIONS]"
rst.Open sqlCode, db, adOpenStaticOnly, adLockOptimistic 'New for SQL
server

4.) Use adForwardOnly vs. adOpenStaticOnly for retrieving data only.
Also, becuse I will be opening multiple RS. I need to close and reopen
each recordset to avoid errors???

5.) Imbeding some sort of SQL codeing in the Open RecordSet command.
Although, if this can be done is there an example.

6.) Use another type of connection vs. Set db =
CurrentProject.Connection?

7.) Other suggestions???

Any help would be appreciated,


Thanks

 

[Cindy Meister]

You might get a better discussion going on this in the data.ado newsgroup (on
this same server). Something I get the feeling you may not have looked at is
using Stored Procedures in SQL Server (predefined queries).