MS Access 2010 Slow Switching From Form View To Form Design - REDUX

Form view to Design View, right?

If you have a front end and back end, and your have linked tables to the BE ... then your solution most likely is to do your design work on the FE linked to a *local* copy of the BE.  I see your situation all the time.

This is easy to try ... just grab a copy of the BE and put it on your local workstation, and relink to it.

mx

Also be sure to turn OFF Name AutoCorrect - in Access Options, under Current Database, make sure to uncheck Name AutoCorrect.

Also, Compact and repair your database files (front-end and back-end) regularly.

To be complete then, run this Decompile procedure on your front end db ... just to be sure it's clean:

Open the VBA Editor and from the menu ...Tools>>References ....
If you see any listed as **Missing: <reference name>, including the asterisks and the word Missing, the
you need to fix that first.

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

AND ...
Once you get familiar with the Decompile idea (and ALWAYS make a BACKUP first!) ... you can add both Decompile and Compact/Repair to the Right Click menus in Windows Explorer, which I use multiple times daily:

Getting the Decompile and Compact context menu options
http://access.mvps.org/access/modules/mdl0039.htm

mx

Just an update; I am away from the office right now but wanted to report that I think we have tried all of this.
As for ID: 37223849, the last thing I did Friday was to try this. I copied the back end and front end both onto my C: drive. I relinked, decompiled, compiled, compacted & repaired, all without sign of errors. After doing so I tried one form that we are currently working on and it took about 3 minutes and 30 seconds to move focus from the parent form to a child form while in design view. From design view back to form view was 30 seconds. I then tried another form that has had no recent changes and its performance was comparable. Both are large forms but ran better than this in Access 2003.
As for ID: 37224302 we turned autocorrect off several years ago after reading advice from Allen Browne. It is still off. We are also changing some of our tables to have no subdatasheets because they were created recently without our doing so.
About ID: 37227660, first a thanks to DatabaseMX for great advice, just as he has given for previous questions. I think we have done most everything listed but will double-check next week. We did have a reference to MSCAL that we deleted in both front and back ends after upgrading to 2010. I did notice something suspicious that I will need to check on Monday. I believe it is when I compile that I see a reference to "CB2000" which was the name of the database in Access 2000. We have since renamed our front end to CB2003 and now CB2010, but there are places that still refer to CB2000. This might not be of significance but I felt I should mention it.
As always, thanks for the suggestions; I will let you know next week if anything pans out.

I see I have no new ideas but want to let you know we have tried most everything, including creating a new blank database and imported everything into it. Right now I am waiting for a report that is very basic to expand to full page view while in design view. This is another sympton of what we face.

I just thought I would give an update.

appreciate that ...

mx

Have you added any new Forms or Reports to the database? If so, do those reports also exhibit the same behavior?

What happens if you move the database to another machine and try to open it? Do you still get the same behavior?

In addition to what mx said in his first comment re: working with a local copy of the BE - are you working with a LOCAL copy of the FE? That is, the FE portion of the database (the forms, reports, etc) is installed directly on your workstation, and you're not pulling a copy of that FE across the network?

Well gee ... I think my statement:

"This is easy to try ... just grab a copy of the BE and put it on your local workstation, and relink to it."

pretty much implies that the FE IS on the local workstation also, but I guess ya never know ...

mx

Why would you assume the FE is local? It probably is, but we often get questions here where the user is running the FE from a network location. I'm sure you'd agree that it never hurts to clear up these matters.

I think what I meant is I'm implying that it *should* be ...

mx

mx -

<Well gee ... I think my statement: ...>
 
Why not let Author clear that up?  Scott's asking a valid question.

Wow! I did not mean to create dissention.
As I stated on 12/03/11 03:14 PM, ID: 37232502:
"As for ID: 37223849, the last thing I did Friday was to try this. I copied the back end and front end both onto my C: drive.  I relinked, decompiled, compiled, compacted & repaired, all without sign of errors. After doing so I tried one form that we are currently working on and it took about 3 minutes and 30 seconds to move focus from the parent form to a child form while in design view. From design view back to form view was 30 seconds. I then tried another form that has had no recent changes and its performance was comparable. Both are large forms but ran better than this in Access 2003."
And, yes the blank database contains all of what was in the original front end but we see no improvement. This is the copy we are now using and the one in which it takes me several minutes to maximize a form in design view. I should qualify that statement by saying I can grab the corner and expand it very quickly, but clicking on hte "max" control will take minutes.
Another thing that happens is that if I select the option to add fields to the form/report, it takes minutes for the field list to show up. This happened today on a form using a table with 6 fields.

I appreciate your ideas and hope they keep coming but play nice, now.
.

No problem here ...

mx

" I believe it is when I compile that I see a reference to "CB2000" which was the name of the database in Access 2000. We have since renamed our front end to CB2003 and now CB2010, "

Are you referring to what you see in image 1 below? If so, that is the VBA Project Name, which initially, defaults to the original name of the DB - as noted above.  You can of course, change that to anything you like. And, as noted above, I doubt it's possible related to the problem.

mx


Capture1.gif
Capture2.gif

So, lets summarize here:

IF ... you put your FE on a local workstation AND put your BE on the same local workstation (say in the same folder), and then link the FE to the BE ... *do* you still see this issue ?

mx

For ID: 37301010: there are only two of us who work on an .accdb version; everyone else uses an .accde version. Both of us have the same issue. I do like the suggestion to remove the recordset and see what happens. In fact, we have read some suggestions that it is faster to routinely open forms and then load the record source. I have a few examples of that and will try to see if they have this issue.
For ID: 37301729 and  ID: 37303246: I found the place to fix the name issue but you were correct in saying that had no influence. I thought the "dissention" was just part of the normal routine but did not want to stir up too much. By-the-way, are the screen captures from Faststone Capture? We love that program.
For ID: 37303253: I had the FE and BE on the same station in the same folder and still had this issue.
Monday, I will try the idea in ID: 37301010 and see what happens. In case I do not get to update before the 25th, here is wishing Merry Christmas (or any other holiday) to everyone.

The screen captures are from RoboScreenCapture, not really available any more, or you might ... be able to find it on the Adobe site. Macromedia actually it over from another company ... and Macromedia was of course taken over by Adobe.

"In fact, we have read some suggestions that it is faster to routinely open forms and then load the record source. "
Not sure that would be the case, because this results in Access having to rebind all the (bound) controls to the Recordsource, as well as other overhead associated with the Form loading.

mx

You CAN load a recordsource dynamically through code, which might make a difference ... I hadn't thought of that when I posted the comment.  Its an interesting idea that may help a lot ...

My thought at the time was to make a quick check to see whether there was a major improvement in performance, and if so strpping the data in your local 'development' back end down to a bare minimum needed for development and testing.

Maybe try reducing the data in your local 'development' back end first ... it's an easy first step.

Just for clarification, I was *not* implying that you should not try the test @ http:#a37301010 ... in fact you should ... just remove the recordsource, save form, reopen ... and see if you have still have the issue.

I was only commenting on dynamically setting the recordsource on the fly when loading the form in general.

mx

Hmmmm ... if you still continue to have this trouble, it might be best to upload the database, both FE and BE, and let us review. If you do this, be sure to remove or obfuscate any sensitive data like phone numbers, emails, ssn's etc. Anything you upload here is publicly available.

[off topic]
I like SnagIt from Camtasia software for screen captures. The Editor alone makes it worth the cost (~$39).

<Yes - we have Snagit here at work ... VERY cool.  Although I do you Robo Screen Capture only because I am used to it. Everyone loves Snagit!>

mx

We can't upload a copy for several reasons, one being the fact the thing is loaded with data we don't want to have available to others. It also would need to be stripped very much; I am at home but think the BE is at 400 MB and the FE at 150 MB. We use Allen Browne's Database Issue Checker to find some issues. The scariest was that one of our most used tables had lost its primary key. As nearly as we can determine, this happened when we put a password on the BE. It might be totally unrelated to that but it happened withing days of that event.

We went through and corrected the issues raised by the Checker and do not see any evidence so far that it helped our issue. We do hope it will prove to have helped our users.

We did try removing the recordsource from a form and a subform but did not find improvement.

We are closed now for Christmas but I will try some more of the ideas when we return. Merry Christmas.

An update: there are no new developments but the one thing that does continue to plague us just reared its ugly head. I an working in design view on a form with a subform and it took almost five minutes to move focus from parent to the child. Yesterday, I clicked to maximize a form on which I was working and it took about the same time to do that.

I continue to look for structual problems and have tried just about every suggestion made so far but with little improvement. It bugs me that this was not an issue before we upgraded to Office 2010 and I keep trying to find something about 2010 that might suggest an issue.

I hate to prolong this question but do not want to close it without getting a solution, if that is possible.

I am adding 3 screenshots of "task manager", taken while experiencing one of the delays. This might not be the proper forum for this but I thought I would start here. . The Processes screen shows very little happening with MSAccess. The Performance screen shows very little of the CPU being used. The Networking screen, however, shows a fluctuating activity. Is it possible that this is being limited in some way that extends the amount of time required to open/close/switch views? If so, where would I look for a setting to change?

An update: I looked through some other threads and found one on performance issues that seemed to have some parallels with our situation. It was ID: 22456306 from 3/16/07 and featured one of the most well stated questions and responses from some of the same people who have responded to my question. As a result I have considered trying The Ultimate Troubleshooter but am holding off on that. I have read up on "Changing Thread Priority to Improve Performance" and have used the "Performance" tool under "Administrative Tools" on the Control Panel. Here is a shot of the results, with 4 of the high usage items labeled. The red an green dashed traces are the ones that bother me because the ONLY thing going on at this time is me moving focus from parent form to child subform in design mode. At the same time I snapped a view of the "Networking" page of Task Manager. I know the percent usage is small but why is it fluctuating? All of the oscillations end as soon as the focus is inside the subform,  I have tried to raise the Base Priority for MSACCESS and lower the priority for RTVSCAN.EXE but neither one stayed changed. I have turned off Dameware for a short time with no change. I have not been allowed to turn off Norton for a short time to see if that helps. We are starting to believe this could be something outside of Access. I am not sure what zone might be appropriate for this question if we want to pursue that possibility.

I have raised the points to 500 because so many good comments were added. Only one turned out to be the correct answer but the others helped eliminate other possibilities. I think we has a suspicion the antivrus was the problem but ignored it until the end because we did not want to deal with the issues this may cause us with corporate.
I hope I have been fair with my point distribition.
Thanks to all.
Dave

didn't help me

Thanks for working on this. However, I retired in 2013 and have not done much with Access since then. Supposedly, Corporate was going to assume the duties of managing the database and I have no idea what they might have done, if anything. Thanks for all the help.