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/