Backend Database

[Gerry]

I have put my data tables into another access database which resides on a
server.
After splitting the database. When I use seek, it tells me that it is an
invalid function.
I have linked the tables in the backend database to the current database.
my code is as follows :

Dim dbs1 As dao.Database
Dim rst1 As Recordset

Set dbs1 = CurrentDb
Set rst1 = dbs1.OpenRecordset("Customer Forecast")

rst1.Index = "Key"
rst1.Seek "=", estkey

before splitting the database, it worked fine. Why would this stop working.
The table in the backend database is indexed on "Key"

Thank you,
Gerry

 

[tina]

from the Seek Method topic in VBA Help:

"You can't use the Seek method on a linked table because you can't open
linked tables as table-type Recordset objects."

suggest you use the Find method instead. or, base the recordset on a SQL
statement that includes criteria to return the desired records only, instead
of basing the recordset directly on a table.

and btw, suggest you declare the recordset type as DAO.Recordset, rather
than simply Recordset.

hth

 

[Ken Snell MVP]

Seek cannot be used on a linked table.

See these articles for more information:
http://support.microsoft.com/kb/208379
http://support.microsoft.com/kb/210266
http://support.microsoft.com/kb/249683

Another workaround is to use the .FindFirst method of the Recordset.

 

[John W. Vinson]

I have put my data tables into another access database which resides on a
server.
After splitting the database. When I use seek, it tells me that it is an
invalid function.
I have linked the tables in the backend database to the current database.
my code is as follows :

Dim dbs1 As dao.Database
Dim rst1 As Recordset

Set dbs1 = CurrentDb
Set rst1 = dbs1.OpenRecordset("Customer Forecast")

rst1.Index = "Key"
rst1.Seek "=", estkey

before splitting the database, it worked fine. Why would this stop working.
The table in the backend database is indexed on "Key"

Thank you,
Gerry

Correct. The SEEK method only works on a *LOCAL* table. You need to either
open a recordset in the backend database, or use the FindFirst method instead.
Seek is a bit more efficient but FindFirst is easier to implement and in most
cases should be just as fast for the user:

rst.FindFirst "[Fieldname] = " & estkey

The name of the index is irrelevant, the FindFirst argument is just a valid
SQL WHERE clause without the word WHERE.

 

[David W. Fenton]

suggest you use the Find method instead. or, base the recordset on
a SQL statement that includes criteria to return the desired
records only, instead of basing the recordset directly on a table.

and btw, suggest you declare the recordset type as DAO.Recordset,
rather than simply Recordset.

Your advice is contradictory. Suggesting using Find indicates an ADO
recordset, but then you suggest using a DAO recordset, which uses
FindFirst.

 

[tina]

oops, my bad. i should have said FindFirst. thanks for the catch, David.
tina

 

[Douglas J. Steele]

In addition to the advice you've already gotten, take a look at
http://www.mvps.org/access/tables/tbl0006.htm at "The Access Web" for how
you can continue to use Seek (although there's really no reason to)

 

[David W. Fenton]

In addition to the advice you've already gotten, take a look at
http://www.mvps.org/access/tables/tbl0006.htm at "The Access Web"
for how you can continue to use Seek (although there's really no
reason to)

That latter is the real point. There are only a handful of scenarios
where .Seek is useful, and that involves a circumstance where for
some reason you need to jump around a rather large recordset a large
number of times (10s of thousands of records or more, 1000s of jumps
or more). This was discussed at length with some (questionable)
benchmarking results in the thread where I made this post:

http://groups.google.com/group/comp.databases.ms-access/msg/585927e7e
a5ed7dc?dmode=source

That post shows how I tested optimizing .FindFirst. The reason
..FindFirst is slower is because each time you call it, it starts
from the top of the index. Thus, the time a .FindFirst takes is
roughtly proportional to how far down the index the value you're
looking for is.

Thus, in my test, I optimized the Find operations to check the value
of the current position versus the requested new position and chose
FindNext or FindPrevious appropriately. This cut the time needed by
half (which is statistically exactly what you've expected). And that
testing convinced me to do this in all large recordsets where I'm
jumping from record to record with the Find functions.

 

[Jeff Boyce]

Gerry

You've received some fine advice about alternatives to "Seek" if you use
linked tables. Now I'm curious why you are "seeking" in the first place.

You have apparently decided that "Seek" is the solution ... but you haven't
described the problem it is supposed to solve.

If you'll provide a bit more description of the business need/problem/issue
that you are trying to solve using "Seek", folks here may be able to offer
alternate approaches.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP