Access performace help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mohan - 27 Jun 2007 19:50 GMT

Hi
I develped a simple Access application to track projects. Application has a
fron-end file (with forms, queries, modules) and a back-end file with data
only. All tables are linked tables from the front-end file.

The application works fine when both files are on the local drive. But when
I move the data file to the network folder, the application is very slow
(about 10 - 15 seconds to open each screen).

I need suggestions for increasing performance
- Should I remove the linked tables and use DAO / ADO?
Which one is better DAO or ADO ?
If using ADO, how do I assign the record set created with ADO to the form?
(I don't want to fill each filed thru code)
(Note: I have done ADO programming before in VB / I need to know how to
assign the forms record set to the records set created with the ADO code
(instead of the linked) if this is possible
- Is there any thing esle I should try (whitout removing the linked tables) ?
- I did some research and set the subdatasheet name property to none (if
its auto)

Reply to this Message

Douglas J. Steele - 27 Jun 2007 20:00 GMT

First thing to do is check the Access Performance FAQ Tony Toews has at
http://www.granite.ab.ca/access/performancefaq.htm

Answering your specific questions:
- Removing linked tables and replacing it with VBA code is unlikely to solve
the problem.
- For a Jet backend (i.e.: an MDB file), DAO is almost always better
- You can create an ADO recordset and set the form's Recordset property to
that recordset. See
http://msdn2.microsoft.com/en-us/library/aa173231(office.11).aspx for an
example. (I believe this capability was added in Access 2000)

Tony's FAQ should answer the rest of the questions.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi
> I develped a simple Access application to track projects. Application has
[quoted text clipped - 20 lines]
> - I did some research and set the subdatasheet name property to none (if
> its auto)

Reply to this Message

Mohan - 28 Jun 2007 20:38 GMT

Thanks for the info..

The late biniding also did the trick ... from 12-15 seconds to open the form
to 2 to 3-4 seconds now. I still need to furhter fine tune this with the
ideas on your link..

Thanks

> First thing to do is check the Access Performance FAQ Tony Toews has at
> http://www.granite.ab.ca/access/performancefaq.htm
[quoted text clipped - 34 lines]
> > - I did some research and set the subdatasheet name property to none (if
> > its auto)

Reply to this Message

Daniel - 27 Jun 2007 20:08 GMT

If your network is overloaded, or simply slow, this happens. When I was
working for a multinational company I had problems continuously. One day
they upgraded the network to a gigbit.... fiberoptics... and everything else
and then all was well.

Now for tips...
1. Place any and all invariable tables in the front-end (tables used for
references only)
2. Preload the forms in the background as soon as the db is opened. You
open them in a hidden state and when the user selects to open a form you need
only make it visible rather than actually loading it.
3. Place your db in the highest folder possible on your network. by
burrying your db, security checks have to be run against every
folder/sub-folder... By placing it in the 1st or 2nd level you can have a
noticeable performance improvement (sometimes).

You might also want to have a look at:
http://www.granite.ab.ca/access/performancefaq.htm

Signature

Hope this helps,

Daniel P

> Hi
> I develped a simple Access application to track projects. Application has a
[quoted text clipped - 16 lines]
> - I did some research and set the subdatasheet name property to none (if
> its auto)

Reply to this Message