SP3 Problems???

Hi Experts,

Since installing SP3 on my XP Pro, Access 2003 , 2gb RAM set-up I've been getting into all sorts of problems with corrupted code and strange errors, such as "Out of memory". Reading through the forums, I'm wondering if the way I work might be the problem.

In the past I've run my app for testing and could go quite happily into the VBA editior to make changes while forms were open - but there was a suggestion on one of the posts that this may cause corruption - is that correct?

I've also read that incorrect references may cause corruption - they all seem to be there, but how can I check if they might cause a problem? Do they need to be in a particular order? This is how they are now:
1. VBA
2. Microsoft Access 11.0 Object Library
3. Microsoft DAO 3.6 Object Library
4. Microsoft Outlook 11.0 Object Library
5. Microsoft Scripting Runtime

One of the symptoms is that I'll get the "Out of memory" message, then when I go to edit the main form (which contains 5 sub-forms on tabs) it won't open - there's no error message, Access just doesn't do anything with it.  Sometimes it will open but  one of the sub-forms seems to have got corrupted and I have to delete it and import an older version from a back-up to get it functional again.

So far I've tried de-compilng and compacting & repairing, creating a new d/base and importing everything (seemed to work for a while) and also running "detect & repair" in Access.

Any ideas gratefully received.
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<In the past I've run my app for testing and could go quite happily into the VBA editior to make changes while forms were open - but there was a suggestion on one of the posts that this may cause corruption - is that correct?>

That is correct - you should not edit code behind forms which are running in Normal view. You should, instead, switch to Design view, make your changes, then reopen the form in Normal view.

Incorrect refs cannot really cause corruption, but they can cause your app to not function properly on other machines. In your specific example, if you were to deploy your app to a machine with Access 2000 only, your app would fail.

Out of Memory errors can be caused by several things. 5 Subforms is a LOT and, depending on how many records are being returned by those subforms, could contribute to your problem. However, I'd fix the other issues first and see what happens.

One trick you can use with Subforms is to set the .SourceObject of your subform ONLY when the user clicks on the tab ... use the TAb Change event for this, setting the .SourceObject like:

Me.YourSubformControl.SourceObject = NameOfYourForm
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note also that SP3 has caused some issues as well:


See if any of these symptoms match yours ...
redpoppyAuthor Commented:

Thanks for replying. Looking at the Microsoft article, I've had the following:

- When you try to open an Access Project (.adp) form in Design view, the form does not open. Additionally, you receive the following error message:
There isn't enough memory to perform this operation. Close unneeded programs and try the operation again.  

- The search key was not found in any record.

What I don't understand is that they are saying these apply to  .adp project - BUT I'm not running a .adp app! The first one in particular seems to describe the problem with not being able to open a form for editing.

Regarding my form/sub-form structure, it's basically just retrieving one main Client record, then up to about 12 subsidiary records on the sub-form tabs (details of quotations etc) linked by the client_id - would that amount of records be causing a problem?

I'll change my editing practices to see if that has any affect.

Thanks again, Steve.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I wouldn't think that number of records would cause a problem.

Also, I'd start with a fresh copy - do the import into a new database again, and work on that copy.

What are you doing that requires the Scripting runtime? This can also cause some issues, especially if you intend on deploying this app ...
redpoppyAuthor Commented:
Ok, I'll try another re-build and see how I get on with that.

I'm using Scripting runtime to get HTML from a template to put into the body of an e-mail (using routines that I found on here). Here's an extract:

'set up email dets from template
    Set fso = CreateObject("Scripting.FileSystemObject")
    Call PublishHtml(Forms![frm_Main_Menu]![templ_dir] & "prov_booking_conf_new.htm", False, bfname)
    Set ts = fso.OpenTextFile(bfname)
    htmlText = ""
    htmlText = ts.ReadAll

Do you think that would cause problems?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The FSO is notorious for causing troubles, especially on deployment, but I don't think that's causing your current problems.
redpoppyAuthor Commented:
I eventually re-built the d/base again this morning after a few problems - I wondered if they might be more of a clue to what's going on:

I firstly tried importing everything in one go and got the error "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again."

It seemed to fail on the reports and when I tried to open them in design mode, nothing happened.

I then tried again with another new d/b, this time just importing the tables, then forms followed by reports and it still came up with the same error.

Eventually I managed to re-build it by importing tables, forms and reports each in two halves, queries then modules all as separate operations.

Is it possible that I actually do have memory problems? I installed an extra 1gb of RAM a couple of weeks ago which seems fine, but I'm now beginning to wonder. I'm going to carry on and do a bit of development on the re-built d/b and I'll report back. Thanks again for you time, LSM
redpoppyAuthor Commented:
I've been working away on the re-built d/base and everything seemed ok until this morning - I made a fairly simple change (a 1 line setfocus) and then started to get Error 2455 - "you entered an expression that has an invalid reference to property form/report"

When I look at the debug it's showing this line in a routine which I've not changed and that has run just fine previously:
 Forms!frm_add_quotations!frm_Upd_Client.Form!addr_full = Forms!frm_add_quotations!frm_Upd_Client.Form!Address1

Incidentally, I'm confused about the correct way to address fields.  For example, for the above I could have put in "Form_frm_Upd_Client.addr_full", which would then be flagged  in the compile if there was an error - is there a "best" way to do this?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming you're referring to a subform here, make sure your syntax is correct:


Assuming that "frm_upd_client" is the name of the SUBFORM CONTROL on your form, this would work. Please note that you should be referring to the control on the main form, and you are NOT referring to the form being used as a SourceObject ... in most cases, they won't be named the same.

Using the Bang ( ! ) operator would actually refer to the Field that is part of the Form's Recordsource. Either way should work, but typically you'd want to refer to the actual control on the Form, and not the Field.

Access converts all these calls to parentheses and quotes behind the scenes, so the quickest way is this:


This would refer to the CONTROL named addr_full ... NOT the field in the form's Recordsource named "addr_full".

Confused enough yet <g>?
redpoppyAuthor Commented:
Well, I'm not sure what to do with this question - I've tried the various things that have been suggested and from other searches on this board and it does seem the "not enough memory"  is a tough one to crack. My app seems a bit more stable than it was but I'm still getting the error after working  for an hour or so - it's happening when I'm editing my main form that contains 5 tabs with linked sub-forms. There's no particular pattern that I can detect (eg a particular sub-form being edited) and before installing SP3 I haven't had any problems.

Is there a way I can check/monitor if there are genuine memory problems? There's plenty of RAM (2gb) and paging available. I've been through the app making sure that all resources are being released (ie: set rs=nothing) and when the compiled live version is running on the server, it seems to run OK.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can test your memory:


Perhaps removing SP3 <g> .... seriously, I know a few people who have done this by completely uninstalling Office 2003 and then re-installing.

BTW, did you install the hotfix mentioned earlier? If you haven't I don't see where it could hurt at this point.
redpoppyAuthor Commented:
I've downloaded and looked at MemTest but I think it's a bit beyond me - I've downloaded both versions (ISO and zip) but I can't figure out what I'm supposed to do with either of them. I pressed "install" after extracting the zip and it gave me a DOS window asking for a drive, which I entered - but the screen just disappeared. I don't really know what I'm supposed to do with an ISO file - it says about burning it to disk, which I did, but then what? I rebooted my computer, but nothing happened and it isn't at all clear to me how to use the tests. If you have time/inclination to explain that would be great.

As far as re-installing Office, I've actually tried just re-installing Access as I bought it separately as an upgrade  to the Office version I had,  but I can't see how to avoid it being upgraded to SP3. After the re-install, I went to the MS upgrade site but I didn't see a list of upgrades that I could select - how would I go about just getting it back to SP2? Or maybe I do need to re-install the whole lot?

Thanks for your time, LSM I'm definitely struggling with this one.
redpoppyAuthor Commented:
I found a memory test from MicroSoft (http://oca.microsoft.com/en/windiag.asp) that I found a bit easier to understand. I ran the tests and memory seems OK.

I'm now going to try uninstalling Access and see if I can figure out how to bring it back to SP2.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The recommended process is to just uninstall, then reinstall and update it to SP2 ... you'll need to do that by downloading that update, and by turning OFF automatic updates on your machine (since that would just update it back to SP3 immediately).


(includes SP1, by the way)

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
redpoppyAuthor Commented:
I re-installed to SP2 successfully and I've been working away quite happily for most of the day - I would definitely have had the memory problems by now if it was under SP3, so I'm convinced that was the problem. So thanks to LSM for pointing me in that direction and for the links supplied that were very helpful and a reminder to self: DON'T TRUST AN MS SERVICE PACK RELEASE UNTIL IT'S BEEN AROUND FOR AT LEAST 6 MONTHS !!! - I've been tied up with this problem for about 5 days.
Hi Everyone,

I ran into this same issue and found a solution on microsoft.com:

It's a Hotfix for SP3, which causes all kinds of errors, and seems to fix the issue (for me at least)

Good luck!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Kalliopi: The hotfix only fixes some of the issues associated with SP3. If it fixed yours that's great, but it won't work for every install ...
redpoppyAuthor Commented:
Just to back up LSM's comments, I did try the hotfix as part of the process I went through - it fixed some of the problems but certainly not all and the only thing that worked for me was to go back to SP2, which in itself is not as straight forward as just uninstalling SP3 - see LSM's solution that I accepted for details.
I've had exactly the same problem.  When trying to edit a multi-tabbed form with subforms on it, I get the same memory error.  The form won't display and can't be edited.  It worked fine with Office SP2.  So I'm rolling back to SP2 as well.  The alleged "hotfix" does not seem to address this problems.  My machine has over 2GB of memory on it, so I seriously doubt there is really a memory problem.  I suppose it's possible there is some registry setting that, if changed, would resolve this problem.  But I am tired of trying to troubleshoot this issue.
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.