index for rst.seek

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for taking the time to read my question.

If you open a recordset of a table that already has an index, do you have to
create one in the recordset as well?

I have a primary key defined in my table which is on an autonumber field.

Thanks,

Brad
 
Brad said:
Thanks for taking the time to read my question.

If you open a recordset of a table that already has an index, do you
have to create one in the recordset as well?

I have a primary key defined in my table which is on an autonumber
field.
Click to expand...

When you set the Index property of the recordset, you aren't creating an
index, you are telling the recordset what index to use. So you might
use code like this:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenTable)

With rs
.Index = "PrimaryKey"
.Seek 123
If .NoMatch Then
MsgBox "Couldn't find the record!"
Else
MsgBox "Found it."
End If
.Close
End With

Set rs = Nothing
 
If you open a recordset of a table that already has an index, do you
have to create one in the recordset as well?

I have a primary key defined in my table which is on an autonumber
field.
Click to expand...

I am going to get a name for myself as a persistent "downer" on .Index and
..Seek methods, but here goes anyway!

These methods were used in the old dBASE file-management days, when proper
database management was not available on the desktop. Although there are
some people who say they are quicker than using dbms access, you should
know that:-

-- you cannot use .Index and .Seek on linked tables, remote or odbc ones
etc. And if you want to do anything significant in Access, you are going to
be well advised to adopt a Front End/ Back End architecture, which means
linked tables.

-- if you don't know about a suitable index, you'll simply have to crawl
the entire table. Not good for the network or your users, particularly if
the db engine knows about an index it could have used.

-- the purists' objection is that they are very "close to the metal". In my
view, the programming at front-end level should not have to know anything
about names of indexes or the internal structure of tables: just what the
interface makes available. Remember that in a real database, you can't
neccessarily tell if you are accessing a table or a view!

Therefore, if you are coming to db design, it would be better to start off
by using the db to manage your data for you. Get stuff using SELECT
statements, order it using ORDER BY clauses, filter it using WHERE
criteria. And so on. The db engine will find any index that would be
suitable; and will often find better query building stratagems than you
will, especially with complex queries.

So in your case, I would simply drop stuff like

Set rs = db.OpenRecordset("MyTable", dbOpenTable, whatever)
rs.Index = "MyGroupingVariable"
rs.Seek whatever,,,
Debug.print rs!SomeFieldValue

and do it properly like this:

strSQL = "SELECT SomeFieldValue " & _
"FROM MyTable " & _
"WHERE MyGroupingVariable = ""Whatever"" "
' don't need an ORDER BY because the db engine will find it
' anyway. Try googling for Rushmore Technology

set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
debug.print rs!SomeFieldValue

Just a thought...
All the best


Tim F
 
Tim Ferguson said:
I am going to get a name for myself as a persistent "downer" on
.Index and .Seek methods, but here goes anyway!

These methods were used in the old dBASE file-management days, when
proper database management was not available on the desktop. Although
there are some people who say they are quicker than using dbms
access, you should know that:-

-- you cannot use .Index and .Seek on linked tables, remote or odbc
ones etc. And if you want to do anything significant in Access, you
are going to be well advised to adopt a Front End/ Back End
architecture, which means linked tables.

-- if you don't know about a suitable index, you'll simply have to
crawl the entire table. Not good for the network or your users,
particularly if the db engine knows about an index it could have used.

-- the purists' objection is that they are very "close to the metal".
In my view, the programming at front-end level should not have to
know anything about names of indexes or the internal structure of
tables: just what the interface makes available. Remember that in a
real database, you can't neccessarily tell if you are accessing a
table or a view!

Therefore, if you are coming to db design, it would be better to
start off by using the db to manage your data for you. Get stuff
using SELECT statements, order it using ORDER BY clauses, filter it
using WHERE criteria. And so on. The db engine will find any index
that would be suitable; and will often find better query building
stratagems than you will, especially with complex queries.

So in your case, I would simply drop stuff like

Set rs = db.OpenRecordset("MyTable", dbOpenTable, whatever)
rs.Index = "MyGroupingVariable"
rs.Seek whatever,,,
Debug.print rs!SomeFieldValue

and do it properly like this:

strSQL = "SELECT SomeFieldValue " & _
"FROM MyTable " & _
"WHERE MyGroupingVariable = ""Whatever"" "
' don't need an ORDER BY because the db engine will find it
' anyway. Try googling for Rushmore Technology

set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
debug.print rs!SomeFieldValue

Just a thought...
All the best
Click to expand...

In general, I agree with you, Tim. On the other hand, I do have an
application that makes repeated seeks in a lookup table for "closest
match" on multiple key fields, and in that application the Seek method
is indispensable. That's not saying that the vast majority of the time
Seek is used, a simple query criterion would not have been better.
 
Back
Top