Problems with the methods Index and Seek 
Author Message
 Problems with the methods Index and Seek

Hello everyone

I'm working on a Access application that I split in 2 parts:
- the frontend with all the forms, query, macros etc.
- the backend with all the tables
The frontend has its tables linked to the backend

It used to be one database.
The problem I've run into is that in the "old" database the programmer used
the methods Seek and Index a lot.
After I had split the database in two, I discoverd that those methods only
apply to recordsets based on a tabledef. A linked table is interpreted as a
Dynaset, so the Seek and Index won't work anymore.

Does anyone know a solution to get around this problem.
An example of the code is copied below.

Any help is welcome.

Mario

   'open tabellen en query
   Set vlxRS1 = vlxDB.OpenRecordset(vlcSQL0)
   Set vlxFaktuur = vlxDB.OpenRecordset("Faktuur")
   Set vlxFaktuurRegel = vlxDB.OpenRecordset("FaktuurRegel")

   'init faktuurnummer
   vllFaktuurNummer = (Val(Right(ctxFaktuurdatum, 4) & Mid(ctxFaktuurdatum,
4, 2)) * 10000) + 1
   vlxFaktuur.Index = "Primarykey"
   vlxFaktuur.Seek "=", vllFaktuurNummer
   If vlxFaktuur.NoMatch Then
      'faktuurnummer bestaat nog niet
   Else
      vlcSQL0 = "SELECT Max(FaktuurNummer) AS NieuwNummer FROM Faktuur
WHERE FaktuurNummer >= "
      vlcSQL0 = vlcSQL0 & Str(vllFaktuurNummer) & " AND "
      vlcSQL0 = vlcSQL0 & "FaktuurNummer <= " & Str(vllFaktuurNummer +
9999)
      Set vlxRS2 = vlxDB.OpenRecordset(vlcSQL0)
      vlxRS2.MoveFirst
      vllFaktuurNummer = vlxRS2!NieuwNummer + 1
   End If



Tue, 09 Jul 2002 03:00:00 GMT  
 Problems with the methods Index and Seek
You can either open the back end directly in every proc
that uses seek, or (preferably) replace Seek with a
different approach.

Your code example must have bene prefixed by:
        Set vlxDB = CurrentDb()
You can replace that with:
        Set vlxDB = OpenDatabase("C:\SomePath\SomeFile.mdb")
The problem with that approach is hard coding the path to
the mdb. You could read the path from:
        Currentdb().TableDefs("MyTable").Connect
but that's becoming just as messy.

Better to fix the database properly. Instead of opening
the table, create a SQL statement that brings in only
the records and fields you really need, ordered as you
wish. Far more flexible. Almost as fast. Much easier to
maintain.

Quote:

> I'm working on a Access application that I split in 2 parts:
> - the frontend with all the forms, query, macros etc.
> - the backend with all the tables
> The frontend has its tables linked to the backend

> It used to be one database.
> The problem I've run into is that in the "old" database the programmer used
> the methods Seek and Index a lot.
> After I had split the database in two, I discoverd that those methods only
> apply to recordsets based on a tabledef. A linked table is interpreted as a
> Dynaset, so the Seek and Index won't work anymore.

> Does anyone know a solution to get around this problem.
> An example of the code is copied below.

> Any help is welcome.

> Mario

>    'open tabellen en query
>    Set vlxRS1 = vlxDB.OpenRecordset(vlcSQL0)
>    Set vlxFaktuur = vlxDB.OpenRecordset("Faktuur")
>    Set vlxFaktuurRegel = vlxDB.OpenRecordset("FaktuurRegel")

>    'init faktuurnummer
>    vllFaktuurNummer = (Val(Right(ctxFaktuurdatum, 4) & Mid(ctxFaktuurdatum,
> 4, 2)) * 10000) + 1
>    vlxFaktuur.Index = "Primarykey"
>    vlxFaktuur.Seek "=", vllFaktuurNummer
>    If vlxFaktuur.NoMatch Then
>       'faktuurnummer bestaat nog niet
>    Else
>       vlcSQL0 = "SELECT Max(FaktuurNummer) AS NieuwNummer FROM Faktuur
> WHERE FaktuurNummer >= "
>       vlcSQL0 = vlcSQL0 & Str(vllFaktuurNummer) & " AND "
>       vlcSQL0 = vlcSQL0 & "FaktuurNummer <= " & Str(vllFaktuurNummer +
> 9999)
>       Set vlxRS2 = vlxDB.OpenRecordset(vlcSQL0)
>       vlxRS2.MoveFirst
>       vllFaktuurNummer = vlxRS2!NieuwNummer + 1
>    End If

--
Perth, Western Australia
Tips for MS Access users at:
        http://www.wa.apana.org.au/~abrowne/


Tue, 09 Jul 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Index Property/SEEK method Problem with ADO

2. ADO Index property and Seek method

3. Index Property / Seek Method In Recordset

4. problem with indexed seek

5. Problems with OpenTable, Seek Method on ODBC-Database

6. SEEK method problem - Need help

7. VB5 newbie SEEK method Access problem

8. Problem with ADO Seek method

9. SEEK Method/FindFirst Methods

10. using .index and .seek function

11. 2 questions: Code doesn't work when I split database and Seek/Index question

12. How to set controlling index before doing SEEK in Access Basic

 

 
Powered by phpBB® Forum Software