General information
Category:
Database DAO/RDO/ODBC/ADO
After experimenting with many possible combinations and permutations of RecordSet Source, CursorType, CursorLocation, LockType and CommandType, I finally found the the right combination that yielded a record set with an index.
The key to opening a record set with an index is to open it with Source = Table (rather than an SQL SELECT * command), and to set the CursorLocation = adUseServer, and to set the Open Options parameter to adCmdTableDirect
Any CursorType and LockType is OK. Here is the code that will successfully utilize the built in indexes in an Access table:
Dim RS As ADODB.RecordSet
Set RS = New ADODB.RecordSet
RS.ActiveConnection = adoMyConnection
RS.CursorType = (any CursorType is OK)
RS.LockType = (any LockType is OK)
' Use table name - Don't use "SELECT * FROM MyTable"
RS.Source = "MyTable"
RS.CursorLocation = adUseServer
RS.Open , , , , adCmdTableDirect
I knew that Microsoft had to provide some way support indexes since we are talking about VB's native database and engine.
Tony Lin
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only