Max Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 97 app split into a FE and BE. I have the following code in
a module to get the next available ID number.

Dim db as DAO.Database
Dim rs as DAO.Recordset

set db = CurrentDb()
set rs = db.OpenRecordset("SELECT Max(ID) AS MaxNumber FROM
tblCategoryDetails WHERE ID <= 1000000;")

It takes approx 40 secs for this recordset to open. If I remove the WHERE
clause, the recordset opens in 1 sec. Why does adding the WHERE clause add
so much time? I really need this on the query because I have another one
that uses WHERE ID > 1000000 depending on different circumstances.

Thanks for your help!
 
Assuming the ID field is indexed and is a Number field, Access is not taking
40 sec to run this query, so there must be something else going on.

For example, it may be opening a network connection to the back end. You
could test that idea, by declaring a public database variable in the General
Declarations section of a standard module. OpenDatabase(). While that is
still open (which forces the network connection to be held open), run your
code again.

Try dbEngine(0)(0) in place of CurrentDb(). Although dbEngine(0)(0) is not
kept up to date where there are structural changes going on, it's generally
adequate for opening recordsets in a stable schema.

How long is the path to the BE? If it's more than 128 characrters, try
shortening the path name and relinking the tables.

What machine holds the BE? If it's a WinXP machine, there was an issue with
the time it took to process the connection. IIRC that has been addressed, so
make sure it has SP2 for WinXP.

You could also try:
SELECT TOP 1 ID FROM tblCategoryDetails
WHERE ID < 1000000
ORDER BY ID DESC;

Let us know how you go with this.
 
Back
Top