SEEK method with linked tables

[dchendrickson]

I am using Access2002/XP-Pro. I am writting VBA using
primarily ADO.

In the application I have been creating, I started
running into trouble using the seek method on recordsets.
I would look into it a little, get frustrated and then
work around it using the find method. Now I find that I
have some modules developed early in the project that no
longer work.

A little deeper research showed that splitting my
database and linking the tables from the backend makes
the seek method go "tilt". The help document said I need
to open an external conection to the backend and then use
the seek method directly on the table.

I am not sure how to do this - or more specifically where
to do this. When I open a recordset, I typically set the
connection to currentproject.connection. Do I make a
change here?

As a matter of good practice/code design, should I always
open an external connection for recordsets if the tables
are linked?

My database - although split into FE and BE, is on a
local machine harddrive, single user, no security. It
will probably expand in the future, but not any time soon.

As always, thank you for your time, insight, and
willingness to nurse along those of us who know just
enough to be dangerous!

-dc

 

[Larry Linson]

The SEEK method does not work on linked tables, and has never worked on
linked tables, only on tables in the open DB (and, if in DAO, opened in
dbOpenTable mode). But, it tends to be more efficient if you open on a Query
using the SEEK information as Criteria, anyway.

Either open the database containing the tables directly instead of using the
linked tables, use a Query, or use the FIND operands.

Larry Linson
Microsoft Access MVP

 

[Albert D. Kallal]

As the wonderful Larry has pointed out, you can't use seek on a linked
tables.

However, there is a very nice workaround at the access web.

Check out:

http://www.mvps.org/access/tables/tbl0006.htm

 

[dchendrickson]

Not knowing that it can't be done and deciding to dig
around while waiting for a response, I came up with a
method that seems to work just fine - at least in my
application. I have no idea if it will grenade in other
applications with other conditions.

My procedure previously looked like this and caused the
crash with the seek method:

Public Sub .....
Dim rst as New ADODB.Recordset
Dim cnn as ADODB.Connection

set cnn = currentproject.connnection
rst.Index = "....
rst.open "tbl...",cnn,....
rst.seek ....

The fix is to simply change the connection string. The
updated procedure looks something like this:

Public Sub .....
Dim rst as New ADODB.Recordset
Dim cnn as ADODB.Connection
Dim strProv as String
Dim strPath as String

strProv = "Provider=Microsoft.Jet.OLEDB.4.0;"
strPath = ""Data Source=C:\...\myBackEndDB.mdb"
cnn.ConnectionString = strProv & strPath
cnn.CursorLocation = adUseServer
cnn.Open
rst.Index = "....
rst.open "tbl...",cnn,....
rst.seek ....

This returns the result expected to the imediate window
without crashes. Any monsters in the shadows waiting for
me if I use this approach?

Thanks again.

-dc

 

[david epsom dot com dot au]

As a matter of good practice/code design, should I

If you use a special feature of a particular database
engine, by connecting directly to a database instead
of using a linked table, then

1) It's more work (I see 4 or 5 extra lines in your
example)

2) You have to change it if you change providers.

I don't think there are any other considerations.

It is good coding practice to adopt a consistent
approach, and it is good coding practice to adopt
an approach that is appropriate for your project(s)
given consideration of those two factors.

For some of us, that means we never SEEK. For
some of you, that means you do it that way all the
time...


(david)

 

[dchendrickson]

Dave,

Thanks for your perspective... I like it.

I am a Mechanical guy trying to pass off as a Computer
guy. I am sure my code would horrify the masses, but I am
making it work. I am up around 6000 lines of code and six
months ago I had never given Access as much as a cursory
glance let alone program in VBA. Some days are good, some
weeks are ... can't use that language here!

Thanks again.

-dc

 

[Tim Ferguson]

I am sure my code would horrify the masses, but I am
making it work.

It is also likely to horrify your network admins. Most people who use Seek
come from a background in file managers like dBase, when there wasn't
anything better. What you are doing here is loading an entire table --
quite possibly many hundreds of megabytes -- and then looking through it
locally for the one value (many two bytes?) you want. There is virtually no
excuse for doing that: modern dbms's allow you to shift the whole of the
work to the engine. Although it's not a true client-server setting, even
Access will work better sending over one page at a time rather than huge
volumes.

It is (practically) always better to use the database tools:

' be specific!
strSQL = "SELECT MyValue FROM MyTable WHERE MyKey=""This One"""
' ... or use ADO if you prefer ...
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)


Some of the MVPs still claim that Seek is noticeably faster than WHERE, but
even then only on huge recordsets, and I still wonder if the same thing
should not be better addressed by having a faster server <g>


All the best


Tim F

 

[david epsom dot com dot au]

anything better. What you are doing here is loading an entire table

Not at all: SEEK does the opposite to what you have described.

SEEK specifically loads a specific index locally, and uses only
that index. It is an extremely efficient method of identifying
Jet Records. Yes, it is very fast, but it also minimises record
locking, index loading, and data traffic.

(david)