CODEWITHSUNDEEP
databaseexcelms-accessadodbvba
Floben Moro
Floben Moro

Reputation: 56

Recordset does not support adseek

I am planning to use MS Access as the database for my program. I have no knowledge of MS Access and at this point I am doing trial and error and tons of research about it. On my research I have found out that Seek method can quickly locate the row that matches the specified values. However it seems like the provider "Microsoft.ace.OLEDB.12.0" does not support adSeek method. I found this out using the code below.

Sub Test3()


Dim Cn As ADODB.Connection
Dim sConnString As String
Dim rs As New ADODB.Recordset

'Set a connection
Set Cn = New ADODB.Connection

'Set connection string
sConnString = "Provider=Microsoft.ace.OLEDB.12.0; Data Source=" & ThisWorkbook.Path & "\Database21.accdb" & ";"
Cn.Open ConnectionString:=sConnString

'open recordset
rs.Index = "ID"
rs.CursorLocation = adUseServer
rs.Open "Table1", Cn

'Message if the recordset supports adSeek
MsgBox rs.Supports(adSeek)



Cn.Close

End Sub

Given that I have no experience in any database or MS access, I don't know if conclusion is correct or I'm just missing something. Can I use the Seek method using MS Access?

Upvotes: 3

Views: 416

Answers (1)

Alex K.
Alex K.

Reputation: 175776

Seek: This method can only be used when the Recordset object has been opened with a CommandTypeEnum value of adCmdTableDirect.

So you need to change to:

rs.Open "Table1", Cn, , , CommandTypeEnum.adCmdTableDirect

Personally I have rarely found seek/find/filter to be particularly useful, executing an SQL SELECT statement is the most perfomant way to fetch records.

Upvotes: 1

Related Questions