MS Access 2007 is slow to open queries, forms, and reports

I have been dealing with this database issue for months, ever since I upgraded the users from Access 97 to 2007.
I have tried every suggestion under the sun to try to speed up the database opening queries, forms, and reports:
*created a brand new database and imported all objects into it
*made sure none of the objects in question are using Dlookup function
*reduced # of command buttons on forms
*removed unnecessary macros from reports
*went through each form and report and deleted unneeded objects
*compacted and repaired db
*created relationships for 12 tables
*ran db analyzers and let it add indexes on tables and added Option Explicit statement on forms
*downloaded and installed Office 2007 updates including Offc 2007 SP2
*ran compact and repair again
*as last resort, I converted the 2007 db to 2003 format

The db is on the hard drive on a PC - the PCs are not old and have 2GB memory - and another PC shares the C: drive from the main PC to access the database.  The main PC opens the objects quickly, but the seconday db takes as long as 10 minutes just to open 1 form; says it's running a query.  If the user tabs from one field to another, it stops and says "running query" again.  Trying to do a simple search on a field takes up to 5 minutes.

I looked at the answers given so far, and they either don't apply to me or I've already tried them.  Can anyone else tell me what to do?  The users are getting restless.  They can't be taking 10 minutes to open a report when they have 20 to open every day.
ddrmisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
On the troublesome computer, when things are bogged down, press Ctl+Alt+Del to open the Task Manager, have a look at all the tasks running, and which one(s) is/are the CPU hog(s).  That will give you a place to start.
Richard DanekeTrainerCommented:
The first place I would start is network adapter.  What speed is your network 10MB, 100MB or 1000MB.  This can make a huge difference.
Second, is to split the database into a front end backed.  
One Backend database on the first computer storing tables and shared queries.
A front end that has been copied.  One copy on the computer with the front end and one copy on the second computer - this off loads much network traffic for screen layout and report formatting where it can be executed locally.
If you need help on either issue ask to clarify on this question.  
Think about it - a 100 MB line is 10 times faster than a 10 MB line.  and 1000 MB (1 GB) line is 10 times faster than a 100 MB line.   10 sec on a 100 MB line is only 1 sec on a 1 GB line.   I expect, after all the other work you have done, this is your throttle or choke.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ddrmisAuthor Commented:
system idle process - 98 or 99%
msaccess.exe - 1%
taskmgr.exe - 1%

rtvscan.exe - 142,008 K
scvhost.exe - 24,044 K
msaccess.exe - 20336 K
explorer.exe - 10992 K
CLI.exe - 10056 K

I could not end rtvscan.exe - said access denied.  the main pc also has this running in the processes with the same K amount but it opens Access fine.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ddrmisAuthor Commented:
@dodahD: what I don't understand is why we did not have these issues with Access 97 - it was on the same network, same PCs as now.  Nothing changed except upgrading from 97 to 2007.

The database was orignally split into front end and back end and was slow as a snail.  I finally did away with the splitting and put everything into one db (I forgot to mention this in my list of things I tried).
GRayLCommented:
With msaccess using only 1% of cpu resources, and a system idle process of 98%,  obviously, access is waiting for data.  I'm with DoDahD,  your network is not good at moving even snails.
Richard DanekeTrainerCommented:
What is your netwok adapter speed?  Is it wired or wireless?  Did you change any network elements at the same time you change the databae?
FunkyBrownCommented:
What do you have for antivirus running on the main and secondary PC, and do you have Access 2007 set to "trust" the network location where you have the DB?  I know 2007 acts very stupid regarding DBs on network locations, and/or Antivirus might have been ignoring Access '97 but is scanning everything 2007 is doing - either of these could lead to a brutal slowdown.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<I finally did away with the splitting and put everything into one db>

Assuming that users are sharing the data, then this is a bad idea for several reasons. Split databases are generally not the cause of the problem, but instead may simply force that problem to surface more quickly.

However, I'm with the others: This is a network issue.

ddrmisAuthor Commented:
We're using 10/100 layer 2 switches, so we doubt it's a network issue.

I'll go ahead and try to split the database again.  Will be tricky because the drive letter on the main PC is C but on the shared it's B, so the links can't find B because they are pointing to C.

Regarding the trusted locations, it's a pain to add a trusted location on a PC that runs Access RunTime.  You have to do a registry hack to add the trusted location, which I've done, but it still gives the warning message whenever the user opens the database.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Couple other things to check:
1. Make sure your not virus scanning the files in any way.
2. Make sure you turn off name autocorrect.
3. Turn off the sub data sheet feature on all tables (I have code to do that if you need it).
4. With a split app, depending on the app and how it's used, keeping a consistent connection open to the back end (hidden form opened at startup bound to a table in the BE) can give a considerable jump in performance.
5. Make sure all user have the proper permissions for the BE directory (full read, write, delete).  Not having them can cause processing delays and other problems.
 And the BE db; what OS is hosting this?
JimD.
ddrmisAuthor Commented:
XP Pro is running on both PCs.
Thanks for everyone's suggestions.  I will try to implement them one by one when the users leave the office later today.  
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
1. Curious as to the size of the tables involved
2. As a very fast and simple network test, drop to a command prompt and type:

 PING <computer name>
  and look at the times in the replys.  You shoul dbe well under 15ms on a local network.  Do it from both PC's to the other.
JimD.
shambaladCommented:
Just throwing in my two cents:
One of the first things I would look at is the following suggestion from Jim's earlier post:

4. With a split app, depending on the app and how it's used, keeping a consistent connection open to the back end (hidden form opened at startup bound to a table in the BE) can give a considerable jump in performance.

I had a similar problem to what you are experiencing, and opening a hidden form bound to the BE at startup solved it.
ddrmisAuthor Commented:
What do you mean by "hidden form"?
shambaladCommented:
OK, first of all, if you find that the 'hidden form' solves your problem, please accept Jim Dettman's post above for the points. I did not intend to jump into this discussion for any credit.

There are a couple of ways to open a form in hidden mode. One way is with VBA:

DoCmd.OpenForm "MyForm", , , , , acHidden

You can also use a macro to open the form (see image below).

The idea is you simply open the form when you first start up the app, then just leave it there in the background while the app runs.

Autoexec.bmp
shambaladCommented:
Scratch that last image. I hadn't changed the window mode from 'Normal' to 'Hidden'.
Autoexec.bmp
Richard DanekeTrainerCommented:
It is okay to ignore the network adapter speed.  But, if you are using only a 10MB connection to your workstation, you can suffer the degradation in performance you have been experiencing.  Plus, the simple test of running the database from the other workstation would have resolved the network issue.
ddrmisAuthor Commented:
@JimD: pinging from main PC to secondary PC: <1 ms  and no packet drops.
pinging from secondary PC to main PC: <1 ms  and no packet drops.

The main table has almost 85,000 records.
The database is around 106,000 KB.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Nothing there suggests any real problems.
and I don't understand how a network problem severe enough to do what you say is happening would not affect other apps on the network.  I would think Access would not be the only issue.
Something else is going on...
JimD.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
As a test, after you've double checked the above, I think you should try and disable opportunistic locking (or OPLOCKS). See the MSKB article here:
http://support.microsoft.com/kb/296264
for the registry entires. Just do the one on the station acting as a "server", which is the second setting down. If the registry entry does not exist, create it.
JimD.
ddrmisAuthor Commented:
Update:
1) We use Symantec anti-virus.  I set it to exclude files with mdb extensions (since I converted the db to 2003 format, it uses mdb instead of accdb) from its scan on both computers
2) Checked autocorrect name: already de-selected
3) Split the database into FE and BE
4) Ran compact and repair on NE and BE on main PC (can't do this on other PC b/c it's using run-time, so this feature is not available
5) Secondary database still slow to open forms and reports!
6) ran registry hack to add folder to trusted sites, but the db still gives the message about potential security concern/trusted source!  I don't know why my hack isn't working on that PC.  I ran the same hack (just changed drive letter) on PCs at another office that had Access run-time

Below is the code for the registry hack.  B: is the drive letter that the PC looks in to find the FE and BE databases.
Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations]
"AllowNetworkLocations"=dword:00000001

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location0]
"Path"="B:\\"
"AllowSubfolders"=dword:00000001
"Description"=""

Open in new window

ddrmisAuthor Commented:
I still don't understand the part about the hidden forms.  How do I know which forms to hide?  Should I hide all of them?
The db has an autoexec macro that opens the switchboard, so I changed the setting to hidden for the switchboard form in the autoexec macro.  Do I need to hide any other forms?
After doing this, I tried opening a report.  Showed "running query" for a long time and report took about 3 1/2 min to open on the secondary PC.  This same report opens in a few seconds on the main PC.
ddrmisAuthor Commented:
EnableOplocks was not there, so I tried to run the following to add it to the registry:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters]
"EnableOplocks"=dword:00000000

Gives error msg: Cannot import.......reg:  Not all data was successfully written to the registry.  Some keys are open by the system or other processes.

I have no other apps open on the PC.  I ran this several times with the same error.  I checked the registry afterwards just in case it actually put it in, but still no EnableOplocks.  Am I doing something wrong?
shambaladCommented:
Please look at the attached database. It consists of one table (tblOpenlink), two forms (frmOpenLink and frmSwitchboard) and a macro (AutoExec). Here's how it works:

1. Save the app to your desktop, and double-click it.
2. When the app opens, it automatically looks for a macro named 'Autoexec'. If it finds one it runs it. Since the macro here is named 'Autoexec', it is run.
3. The macro executes two actions:
      a. The 1st action opens from 'frmOpenLink', which is the hidden form.
      b. The 2nd action opens form 'frmSwitchboard'.
4. Note that for this first startup, you can see both forms.
5. Open the AutoExec macro in Design mode. For the first action, which opens frmOpenLink, change the 'WindowMode' from 'Normal' to 'Hidden'; then save and close it.
6. Click the 'Quit App' command button on frmSwitchboard to close the app.
7. Now re-open the app, all you will see is frmSwitchboard, although frmOpenLink is, in fact, running in the background.

The code that runs when frmOpenLink runs is listed below. Note that it opens the table 'tblOpenlink'. As a general practice I like to use a dedicated table (I call it 'tblOpenlink') for the purpose of maintaining the link to the Backend database. It's a single column, single row table. Normally this table would reside in your back-end, and you would then have the linked table in your front-end.

Option Explicit
Option Compare Database
Private strModule As String
Private db As DAO.Database
Private rst As DAO.Recordset

Private Sub Form_Close()
   On Error Resume Next
   rst.Close
   Set rst = Nothing
   Set db = Nothing
   On Error GoTo 0
End Sub

Private Sub Form_Open(Cancel As Integer)
      Const strProcedure As String = "Form_Open"
10    On Error GoTo ErrorHandler

20    strModule = Me.Name
30    Set db = CurrentDb
40    Set rst = db.OpenRecordset("tblOpenLink", dbOpenSnapshot)
         
ExitSub:
50    On Error GoTo 0
60    Exit Sub

ErrorHandler:
70    HandleError strModule, strProcedure, Err.Description, Err.Number, Erl
80    Resume ExitSub
End Sub

Open in new window

Hidden-Form-Demo.accdb
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I still don't understand the part about the hidden forms.  How do I know which forms to hide?  Should I hide all of them?
The db has an autoexec macro that opens the switchboard, so I changed the setting to hidden for the switchboard form in the autoexec macro.  Do I need to hide any other forms?
After doing this, I tried opening a report.  Showed "running query" for a long time and report took about 3 1/2 min to open on the secondary PC.  This same report opens in a few seconds on the main PC.>>
1. Create a new fom.  Set it's record source to any of the BE tables.  Typically I use my version control table or the menu table; something that won't change often.  You even can create a dummy table in the BE just for this purpose.
2. Place a control on it and set its controlsource to any field in the table.
3. Save the form.
4. Modify the Autoexec.  Open the form as hidden.
Done.
What this does is makes sure that the BE never closes no matter what your app does.  If your app was written in a way that it already does this, then you'll notice no difference.  But if it is always closing connections (like it should), then without this form, the BE will repeatedly be closed and re-opened.  The overhead can add up.
JimD.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<EnableOplocks was not there, so I tried to run the following to add it to the registry>>
  Add it manually with Regedit.
JimD.
ddrmisAuthor Commented:
@JimD: I did your 4 steps above about the hidden form, then closed both databases, reopened both databses , but It doesn't seem to have made a difference.  Took between 3 1/2 and 4 min to open the same report I opened yesterday.

I can't add the EnableOplocks - when I right-click on the parameters folder and do New\DWORD value, it says "cannot create value: error writing to the registry."  I don't know how else to add this to the registry manually.  Could you please tell me how to do it?
ddrmisAuthor Commented:
Anyone have any ideas on my issue about: <<6) ran registry hack to add folder to trusted sites, but the db still gives the message about potential security concern/trusted source!  I don't know why my hack isn't working on that PC.  I ran the same hack (just changed drive letter) on PCs at another office that had Access run-time>>
I would like to get rid of this macro security message whenever the run-time user opens the database.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I can't add the EnableOplocks - when I right-click on the parameters folder and do New\DWORD value, it says "cannot create value: error writing to the registry."  I don't know how else to add this to the registry manually.  Could you please tell me how to do it?>>
  Sounds like you don't have enough priv for writting to that registry key.  Make sure your logged in on the computer with an account that has admin rights.
Jim.
ddrmisAuthor Commented:
@JimD: I have successfully added EnableOplocks to the main PC and set it to 0.  Went to other PC to open some reports.  The strange thing is that the 1st report I open still take more than 2 min to open, but reports I open after that, including the same one again, open in a few seconds.  It's bizarre that the 1st report I open takes so long to open.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The strange thing is that the 1st report I open still take more than 2 min to open, but reports I open after that, including the same one again, open in a few seconds.  It's bizarre that the 1st report I open takes so long to open.>>
  That would be more or less normal.  What your seeing on the first report is the overhead associated with opening the BE MDB.  I say more or less though because it will always take a little longer to do something if the BE is not already opened, but it still should not be taking 2 minutes.  Something is still going on with that PC.
  I've got to think about this a bit...we've hit most of the high points already and nothing has really jumped out except this, which points more towards OS/Server operation than networking, although a networking issue might be contributing to it.  It could be a multiple problem that in total is causing a long delay.
  Do you know if this is a multi-core processor?  And are we dealing with a 32bit or 64 bit OS?
JimD.
ddrmisAuthor Commented:
Intel Core 2.  1.86Ghz.  2GB RAM.  32 bit OS.  (we try to stay away from 64 bit OS if we can help it)
ddrmisAuthor Commented:
I have still have some slowness opening reports, but I'm closing the issue because I don't think there is anything else that can be done.  Slowness in Access 2007 is a known issue.  I've experienced the same with my own databases after I converted from Access 97, but my slowness issues aren't as bad as the ones in the particular database that I opened up this issue for.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You also might want to try bumping up Maxbuffers().  It seems JET has a problem with it's cache cleanup when multiple processors are involved.
See the comment in the open discussion in this thread:
http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Remote_Desktop-Terminal_Services/Q_25091616.html
  This was running under 64 bit, but it seems the real issue is multiprocessors and the size of ACE/JET's cache.
JimD.
PS and BTW, sorry for loosing track of this and never responding to your last comment.
 
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.