A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Backend Database



 
 
Thread Tools Display Modes
  #1  
Old May 10th, 2009, 05:53 PM posted to microsoft.public.access.tablesdbdesign
Gerry
external usenet poster
 
Posts: 112
Default Backend Database

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



  #2  
Old May 10th, 2009, 06:11 PM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default Backend Database

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


"Gerry" wrote in message
...
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





  #3  
Old May 10th, 2009, 06:17 PM posted to microsoft.public.access.tablesdbdesign
Ken Snell MVP
external usenet poster
 
Posts: 275
Default Backend Database

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.

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"Gerry" wrote in message
...
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





  #4  
Old May 10th, 2009, 06:31 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Backend Database

On Sun, 10 May 2009 09:53:00 -0700, Gerry
wrote:

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.
--

John W. Vinson [MVP]
  #5  
Old May 10th, 2009, 11:14 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Backend Database

"tina" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #6  
Old May 11th, 2009, 02:13 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default Backend Database

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


"David W. Fenton" wrote in message
36.91...
"tina" wrote in
:

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/



  #7  
Old May 11th, 2009, 10:07 PM posted to microsoft.public.access.tablesdbdesign
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Backend Database

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Gerry" wrote in message
...
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





  #8  
Old May 12th, 2009, 12:02 AM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Backend Database

"Douglas J. Steele" wrote in
:

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..../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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #9  
Old May 12th, 2009, 04:08 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Backend Database

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


"Gerry" wrote in message
...
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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.