Re: Poor Peformance from 2K Version

"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:05EE98F2-2098-4359-9339-0A28FC3E71B2@xxxxxxxxxxxxxxxx
97 and 2K are different formats. The extra time is the 2K front end is
having to convert the data on the fly to use it. I would suggest
converting
the Back End to 2K.

It does not convert the data. JET 4 is able to use the old format. Further,
we have near unlimited processing on a modern machine. The slowness is still
going to be network and other issues, as converting data on the fly would be
near instant anyway if JET had to convert the data....

Two things to check:

1) a2000 and later use a separate IDE for the code. The object model was
changed, and as a result
any sql that uses VBA functions in the sql can run SIGNIFICANTLY
slower. The usual work
around is to remove the VBA expressions from the query..and use them
in the report direclty.
Another approach is to simply try and avoid using functions in the
sql.

2) a2000 and later is more aggressive in terms of dealing with the
locking file. So, try the same code
but ALWAYS FORCE a persistent connection. That means your front end
opens a table from the
back end. (you do this in your startup code long BEFORE you try and
run the report). Try this
idea and post back. This is usually the #1 fix for this problem.

After you check the above two..and post back with your results (it helps..as
then other can learn this too!!!).

The next thing to check is things like track name auto correct, but really,
try the front end as a mde, and see
if that works...

The definitive list of things to check in a2000 and later is here
http://www.granite.ab.ca/access/performancefaq.htm

Go through ALL of the items in the list..

However, the FIRST thing to try is the persistent connection trick...it
might even result in BETTER performance then what you had with a97....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx
http://www.members.shaw.ca/AlbertKallal


.

Relevant Pages

  • Re: Should I worry that my Access to SQL conversion has gone so well?
    ... converting a typical Access application to use SQL Server ... Jet attempts to send all queries to the server ...
    (microsoft.public.access.conversion)
  • Re: Access and Terminal Server????
    ... Jet as a back end; or some version of SQL as the back end. ... to an mde file, and had routines which copied the mde file to the user's ... As far as SQL is concerned, I've been scared shitless about converting from ...
    (microsoft.public.access.modulesdaovba)
  • Re: Question for Joe Fallon--determining Data type
    ... then the data type for the column will be Text. ... means 255 characters max (that's a Jet limit). ... where MSDE or SQL Server is the main database (in these cases Jet ... Is 'Spreadsheet' an MS euphemism for 'Excel' or does it support other ...
    (microsoft.public.access.externaldata)
  • Re: Off Topic - Access to Postgres
    ... In both Jet and Oracle back ends, I always write my SQL dynamically anyway and contrary to what I think you implied in your initial response to Hank, I don't think writing Jet SQL (for Jet BE or c/s linked table approaches) nor PTQ SQL in VBA makes an app more difficult to manage or increses development time significantly. ...
    (comp.databases.ms-access)
  • Re: Use DAO or ADO?
    ... SQL Server is more popular than Jet. ... real world of development, Dot Net", ADO has been superceded by ADO.NET ...
    (comp.databases.ms-access)