Upgrade from 97 - 2003 performance issue when in design mode.

[kitesurfingphil]

I am new to this forum so sorry if I have posted this in the wrong place, please feel free to move to correct place.

About 2 years ago the company I work for decide to upgrade to office 2003, it was decided to upgrade all databases from 97 to 2003.

The upgrade went ahead with out any problems or issue and all functionality was retained in the new 2003 version.

I have recently been asked to make a lot of changes to the upgraded db and to implement new feature and functions.

The problem I have is when I put an existing form or a new form in to design mode it seems go very slow. By this I mean if I click on a control it takes between 5 -10 sec to give focus to that control. This is he same when moving controls added, deleting or even viewing the control properties. Saving forms and opening in design mode is also very slow it can take upwards of 15sec to save a form.

When out of design mode the db functions with no speed issues and is nearly as fast as the original 97 version. I was sure this was not an issue before the upgrade so I have test the original 97db in design mode and the issue is not present.

I have made sure this is not a pc issue by running on different machines, I have also tested local and from server and the problem is always there, thus ruling out machine and network configuarion issues.

Data base consists of a access front end which is run on the client this holds the forms, queries and reports, the back end is a mdb (I know it should be moved to sql, but that’s for another day!) which is hosted on our file server. Mdb is 143mb in size and has about 40 - 60 tables possible more can’t see this being the issue.

I Google searched ‘Access 2003 performance issue’ and a couple of sites mentioned that Sub Data Sheets effects performance in 2003 so I have turned this off for ever table and this has made no difference.

I just can’t see what could be causing the problem. I have been using access for quite a few years and I have never come across an issue like this. Any help or guidance that you can give would be greatly appreciated this problem is killing me and if I can find a solution it may be quicker to scrap it and completely rewrite to include the new features and the move to sql.

Thanks in advance for any one who can shed some light on my very frustrating situation.

Cheers

 

[datAdrenaline]

I would suggest to turn off the 'Name AutoCorrect' feature too.

....

In addition here are some other tips ..
- Set your recordsource of the form to a limiting or empty recordset, something like:

SELECT * FROM SomeSource WHERE 1=0

Then modify your recordsource to a populated recordset in the OnOpen event of the form. Or create (or expand existing) code to open forma in hidden mode, then set the recordsource.

DoCmd.OpenForm "Form1",,,,,acHidden
With Forms("Form1")
    .Recordsource = "SELECT * FROM tblMyTable ORDER BY SomeField"
    .Visible = True
End With

- Set all your combos and list box sources to SQL statements and NOT stored query names.

- If your form has a bunch of sub-forms, then practice the above two techniques on them. Plus, you may want to consider using only ONE subform, then programatically setting the Source Object in response to a button click or option group (frame control) change.

- Compact and Repair your db frequently.

...

Some important notes to remember about A2000+ vs A97. The data in A2000+ is manipulated in DBCS (double byte character system) mode, where as in A97 SBCS (single byted character system) was used. To illustrate what that means, lets say you have the words "Hello World".. In A97, Access would manipulate 11 bytes of information when visualizing that field value. In A2000+, Access manipulates 22 bytes of information when visulizing that field value. Also, to save disk space, A2000+ can be set up to perform 'Unicode Compression' so, A2000+ compresses to 11 bytes, saves the data, and uncompresses the data to visualize it ...

Also in A2000+ the introduction of ADP's came about, which forced a change in the way code was stored. In A97, each module was stored as its own segmented block of memory. Well in A2000+ the storage mode was changed to a BLOB. What that means is that all VBA stuff is stored in one huge chunk, so if you modify ANY code the entire BLOB is retrieved/saved. This change of storage probably effects the design time loading of forms and reports .... for more info on the BLOB stuff click here

Here is an excerpt from the article:

When you try to save design changes that you made to a Microsoft Access object that contains a module (form, report, or standard module), it may take longer to save the object than it did in earlier versions of Microsoft Access. This is especially true in databases that contain large VBA projects.

I hope all this info helps out! ...

 

[gemma-the-husky]

i prefer to develop in A97, as I too find that A97 is faster than A2002 or A2003.

for the same reason, i keep backends in A97 format, as I find the data retrieves quicker than from a 2003 backend

A2003 definitely has hungrier memory requirements - so one issue might be you just need more memory on your development pc

 

[HiTechCoach]

This may also help:

Microsoft Access Performance FAQ

 

[kitesurfingphil]

Thanks you all for you comments, which gives me plenty to look at and think about.

Yes A97 differently faster that 2002/2003 as for memory I am running 2 GB on my development pc so can’t see that as an issue and I have tried on other pc’s and the problem is there.

I doesn’t look like any one thing will sort my problem, I have turned of auto correct and this hasn’t helped. I think that I will be rewriting the front end from scratch to include the new required features and all of your suggestions.

Thanks again.