ddrmis
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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).
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).
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.
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks for everyone's suggestions. I will try to implement them one by one when the users leave the office later today.
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.
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.
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.
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.
ASKER
What do you mean by "hidden form"?
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
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
Scratch that last image. I hadn't changed the window mode from 'Normal' to 'Hidden'.
Autoexec.bmp
Autoexec.bmp
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.
ASKER
@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.
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.
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.
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.
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.c
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.
http://support.microsoft.c
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.
ASKER
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.
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"=""
ASKER
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.
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.
ASKER
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
"EnableOplocks"=dword:0000
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?
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SYSTEM
"EnableOplocks"=dword:0000
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?
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.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<EnableOplocks was not there, so I tried to run the following to add it to the registry>>
Add it manually with Regedit.
JimD.
Add it manually with Regedit.
JimD.
ASKER
@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?
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?
ASKER
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.
I would like to get rid of this macro security message whenever the run-time user opens the database.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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.
<<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.
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.
ASKER
Intel Core 2. 1.86Ghz. 2GB RAM. 32 bit OS. (we try to stay away from 64 bit OS if we can help it)
ASKER
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.
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:
https://www.experts-exchange.com/questions/25091616/MS-Access-runs-slow-as-a-2008-Terminal-Server-RemoteApp.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.
See the comment in the open discussion in this thread:
https://www.experts-exchange.com/questions/25091616/MS-Access-runs-slow-as-a-2008-Terminal-Server-RemoteApp.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.
ASKER
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.