DAO to ADO Recordset Options
- From: jamiedora@xxxxxxxxx
- Date: 22 Jun 2006 06:16:08 -0700
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
.
- Follow-Ups:
- Re: DAO to ADO Recordset Options
- From: Stephen Howe
- Re: DAO to ADO Recordset Options
- Prev by Date: Re: Passing parameter from Data Environment to Oracle ?
- Next by Date: Re: DAO to ADO Recordset Options
- Previous by thread: Trasaction and Dataset
- Next by thread: Re: DAO to ADO Recordset Options
- Index(es):
Relevant Pages
|
