Using SEEK with ADO and SQL Server engine

[JamesHardiman]

I'm trying to do something which shouldn't be rocket science!

I'm working in an Access Project (.ADP) with a SQL Server database.

I have a table with a compound key (two columns).

I need to find a record. I'm using ADO.

    cn.ConnectionString = "etc"
    cn.Open
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseServer
    rs.Open "aaRecordProperties", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rs.MoveFirst
    rs.Seek Array(obj.Name, prop.Name), adSeekFirstEQ

And what I get is:

Error 3251: Current provider does not support the necessary interface for Index functionality

Can it be some difficult just to find a record in a table?

Help, please,

JamesH@sunsail.com

 

[Norris68]

From ADO Seek method help:

Use the Seek method in conjunction with the Index property if the underlying provider supports indexes on the Recordset object. Determine whether the underlying provider supports indexes with the Supports (adIndex) method.

Reading the help further, the Seek method is for traversing a table index. Also, do you really need a server-side cursor? They are very expensive for the server.

To find a record in a recordset, use the Find method e.g.

rs.Find "ObjName='" & obj.Name & "' AND PropName='" & prop.Name & "'", 0, adSearchForward, 1

See help for the usage of parameters - it may be a lot quicker in certain cases to search backwards from the end of the recordset.

 

[cmmrfrds]

In ADO the Filter method is the most flexible. It functions similiar to the SQL where clause.

rs.Filter = "obj.Name = " & prop.Name & "anotherfld = " & anothervar & etc.

This gives a subset of the original recordset that satisfies the search criteria.

To return to unfiltered recordset use
rs.Filter = adFilterNone

 

[JamesHardiman]

Thanks for those two replies: I tried the FIND like you suggested, but it whinged, and I thought that was because it doesn't like AND.

I'll try that, and the filter: trouble is I'm working my way through 99% of all the records in a 10,500 record recordset... likely to be a bit slow!

I'll give both a try and report back.

James

 

[Norris68]

Using AND within a Find should be OK. Can you post up the code you are using? Also, what are your field names?

 

[JamesHardiman]

It definitely doesn't like AND ... and the help agrees. You get error 3001: Arguments are of wrong type, in conflict, don't agree.

Filter seems to work OK; I think this code is working OK:

Private Sub cmdDoit_Click()
Dim obj As AccessObject, dbs As Object
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim r As Report
Dim prop As Property

    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DevelopmentPoSIC2003;Data Source=YEDIEPOS2MACHIN"
    cn.Open
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "aaRecordProperties", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    Set dbs = Application.CurrentProject
    ' Search for open AccessObject objects in AllReports collection.
    DoCmd.Hourglass True
    For Each obj In dbs.AllReports
        DoCmd.OpenReport obj.Name, acViewDesign
        
        For Each prop In Reports(obj.Name).Properties
            rs.Filter = "ObjectName='" & obj.Name & "' AND PropertyName='" & prop.Name & "'"
            If rs.EOF Then
                rs.AddNew
                rs!Objectname = obj.Name
                rs!PropertyName = prop.Name
            End If
            If prop.Type <> 8209 Then
                rs!OldValue = Left(CStr(prop.value), 500)
            End If
            rs.Update
        Next prop
        DoCmd.Close acReport, obj.Name
    Next obj
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    DoCmd.Hourglass False
End Sub

This is recording the values of all properties in all reports in the ADP, so I can compare two ADPs (because Access crashed and corrupted one, but it was days before I discovered the corruption, which was mainly that it had lost all the Input Parameters from the reports. So I didn't want to revert to the backup, just look at the differences.

James

 

[JamesHardiman]

That's it. It worked. Thanks, all.

I went with the filter option in the end.

If anyone wants the full code to record all properties of all reports from two ADPs into one table, print them, and list the differences, let me know!

JamesH@sunsail.com

 

[cmmrfrds]

A couple of efficiency things. The Find is not only limited to 1 criteria, but also rereads the data source - not very efficient on larger recordsets. The Filter will work on the recordset in memory. Also, the client side cursor is usually forced (coerced) to Static so all the columns in the select list are being returned - best to limit to only columns needed.