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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|