dbOpenDynaset vs dbOpenTable with index-CodePudding

I just did a split of an Access database and I've got a couple of linked tables that use a line similar to VL2.Index = "ReportKey" after being opened via VL2.Index = db.OpenRecordset("VolunteerDetail", dbOpenTable), which no longer works because they're now linked tables.

So what do I do instead? I can open the recordset by omitting the dbOpenTable part, but it's the index that I don't know how to handle, because the dynaset piece doesn't have an index in the same way. I understand that VL2.Index = "ReportKey" is assigning the name to the index, but what is the equivalent of that with dbOpenDynaset? I've seen references to things like FindFirst, but those don't make sense to me.

What do I replace VL2.Index = "ReportKey"` with, when using a dynaset?

CodePudding user response：

VL2.Index = "ReportKey" after being opened via VL2.Index = db.OpenRecordset("VolunteerDetail", dbOpenTable), which no longer works because they're now linked tables.

correct. So this suggests you are using seek() code in your applcation.

There are two solutions here:

So, in place of this:

Dim rstHotels       As DAO.Recordset

Set rstHotels = CurrentDb.OpenRecordset("tblhotels", dbOpenTable)
    
rstHotels.Index = "PrimaryKey"
rstHotels.Seek "=", 17

If rstHotels.NoMatch Then
   Debug.Print "not found"
Else
   Debug.Print "found it"
End If

rstHotels.Close

Change your code to use .FindFirst:

You would use say:

Dim rstHotels  As DAO.Recordset

Set rstHotels = CurrentDb.OpenRecordset("tblHotels1")
    
rstHotels.FindFirst "ID = 17"

If rstHotels.NoMatch Then
   Debug.Print "not found"
Else
   Debug.Print "fount it"
End If

So, you can use .FindFirst, and you do NOT need to use the .index setting. I suspect the above could be used with "much less" changes to your existing code.

but, you can also consider writing out the sql, say like this:

Dim rstHotels  As DAO.Recordset
Dim strSQL     As String

strSQL = "SELECT * from tblHotels WHERE ID = 17"
   
Set rstHotels = CurrentDb.OpenRecordset(strSQL)
    
If rstHotels.RecordCount > 0 Then
   Debug.Print "found it"
End If

So, one approach is to dump seek, but still use .FindFirst and .nomatch.

However, seek() MAY WELL be desired.

VERY important is HOW are you using seek? (or are you even using seek????).

I ask, since seek() is SUPER DUPER high speed performance option. This goes back to the early days of desktop database systems. seek() does NOT even use SQL, and it is VERY fast - I mean blistering fast.

So, it really depends on how you were using the .index setting. If you were just using .FindFirst, then in fact you don't need to set .index. You ONLY need to set the index WHEN using .seek().

Now, if you ARE using .seek() and you ARE hitting a whole bunch of PK or index values for special processing? If you can build a single query, no problem.

However, if the .seek() is to be used over and over - say in a processing loop?

then you can consider a 3rd alternative, use the data engine object, and DIRECLTY open the back end database, and your existing code and .seek() can still be used.

You would use this:

Public Function OpenForSeek(TableName As String) As Recordset
' Assume MS-ACCESS table
Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase _
                (Mid(CurrentDb().TableDefs(TableName).Connect, _
                11), False, False, "").OpenRecordset(TableName, _
                dbOpenTable)
End Function

Then, you can go:

dim rstHotels   as DAO.RecordSet

set rstHotels = OpenForSeek("tblHotels")

So, the above means you open the back end table directly in a round about way. I only suggest this last option if you REALLY do need .seek(), and you really need super duper performance to pull values.

CodePudding user response：

It is FindFirst and FindNext to use, so if these "don't make sense to me", read up the documentation:

Recordset.FindFirst method (DAO)