How To Use the ADOCE 3.1 Seek Method with SQL Server CE (279761)

MORE INFORMATION

To use the Seek method you must have an index to match the seek expression on the base table and your recordset Open method must use the adCmdTableDirect flag.

The Seek method locates a record in an indexed recordset and makes that record the current record.

Seek takes an array of Variant values as its first parameter. Each element of the array must contain a value to compare against each corresponding column in the index. For example:

Dim cn As ADOCE.Connection
Dim rsCust As ADOCE.Recordset

Set cn = CreateObject("ADOCE.Connection.3.1")
Const strConnect = "Provider=microsoft.sqlserver.oledb.ce.1.0;Data Source=\ssce.sdf"
cn.Open strConnect

cn.Execute "Create Table mytable (col1 int NOT NULL)"
cn.Execute "Create Unique Index idx1 On mytable (col1)"
cn.Execute "Insert mytable values (70)"
cn.Execute "Insert mytable values (71)"
cn.Execute "Insert mytable values (72)"
cn.Execute "Insert mytable values (73)"

Set rsCust = CreateObject("ADOCE.Recordset.3.1")
Dim a(1)
' Set the index to seek in
rsCust.Index = "idx1"
a(0) = "72"
rsCust.Open "mytable", cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
rsCust.Seek a(0), adSeekFirstEQ
MsgBox rsCust.Fields("Col1")
MsgBox "Done"
				

The following code demonstrates how to do a multi-column Seek on a SQL Server CE table:

Private Sub Seek_Test()
    Dim cn As Connection
    Dim rs As Recordset
    
    Set cn = CreateObject("ADOCE.Connection.3.1")
    Set rs = CreateObject("ADOCE.Recordset.3.1")
    cn.Open "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;data source=\ssce.sdf;"
    On Error Resume Next
    cn.Execute "drop table testtbl;"
    On Error GoTo 0
    cn.Execute "create table testtbl (col1 int, col2 int, col3 int);"
    cn.Execute "insert into testtbl (col1, col2, col3) values (3, 3, 1);"
    cn.Execute "insert into testtbl (col1, col2, col3) values (3, 3, 2);"
    cn.Execute "insert into testtbl (col1, col2, col3) values (4, 3, 3);"
    cn.Execute "create index testtblindex on testtbl (col1, col2 DESC);"
    
    rs.Index = "testtblindex"
    rs.Open "testtbl", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    
    rs.Seek Array(3, 3), adSeekFirstEQ
    MsgBox "FirstEQ " & rs.Fields(2)    ' 1
    rs.Seek Array(3, 3), adSeekLastEQ
    MsgBox "LastEQ " & rs.Fields(2)     ' 2
    rs.Seek Array(3, 3), adSeekBeforeEQ
    MsgBox "BeforeEQ " & rs.Fields(2)   ' 1
    rs.Seek Array(3, 3), adSeekAfterEQ
    MsgBox "AfterEQ " & rs.Fields(2)    ' 1

    rs.Seek Array(4, 3), adSeekFirstEQ
    MsgBox "FirstEQ " & rs.Fields(2)    ' 3
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub