.Index = "PrimaryKey" error with SQL BE linked

[Brian62]

I have a database I have the tables linked to SQL Server 2008. As going through my form updating the VBA errors I have two I can not figure out. The Index and Seek (highlighted in green) highligh it's self in yellow in the module. The primary key is set on ID_D.

Have any ideas?


Private Sub Display()
Dim DAO As Database
Dim RS As Recordset
Dim LngCnt As Long

LngCnt = 0

Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.accdb")

Set RS = DAO.OpenRecordset("dbo_TblRDStudyAuditDocument")
LngCnt = RS.RecordCount

With RS
If LngCnt > 0 Then
.MoveFirst
.Index = "PrimaryKey"
.Seek "=", ID_D
If .NoMatch Then
MsgBox ("No Match"), vbInformation, "ERROR: Display()"
Exit Sub

Else

Studyid = .Fields(1)
AuditDate = .Fields(2)
TxtSectionName = .Fields(3)
DocName = .Fields(4)
Comments = .Fields(5)

End If

End If
.Close

End With

Set DAO = Nothing
End Sub

 

[pbaldy]

As posted elsewhere:

If memory serves, Seek is not available on linked tables. In any case, much more efficient would be:

Set RS = DAO.OpenRecordset("SELECT * FROM dbo_TblRDStudyAuditDocument WHERE PrimaryKey = " & ID_D)

If I've got everything right. Then you're not pulling the whole table over the wire.

PS: I would not have a variable with the name "DAO", as it can be confusing. Also, I'd disambiguate your declarations:

Dim DAO As DAO.Database
Dim RS As DAO.Recordset