Comments

# Roger said on January 3, 2008 7:09 AM:

Hi Zac

Well I did not use Access 2003 for a while because the clients did not require changes to the program. Now I had to do. First I opened the database in SP3. It is a container with linked tables to a SQL Server, DAO 3.6 and ADO 2.8. Lots of forms with lots of combo boxes and text boxes and 2 sub forms. Everything seems to be fine until I ran into the memory problem on some forms. When double clicked no action and sometimes the message: no memory etc.

Installed the post SP3 hotfix and the same thing happened only the memory message was gone. Editing the form did not work. Form deleted and a Acc2003 version imported. Same results. Acc97 version imported. Same results. Remember before there where no problems with this form. Via an other form going to the code and check it: 2 lines edited and as soon the form has to be opened in Access. BANG. Access crashes. Send all the information to you guys just a minute ago with error reporting.

Looking further I found the reason was a simple subform that lists records from a table. Deleted and imported and now I could open the form again.

BUT: after opening it several times to test it. BANG. Access2003 crashes again. Check out the subforms: no problem. Test it again. No problem.

When I take a look at the query behind the subform it look like this:

SELECT Opdrachtregels.IDdossier, Opdrachten.IDopdracht, RTrim([IDdebnr]) AS Debnr, Bedrijven.Naam, Proper([Plaats2]) & " - " & Left([Postcode2],2) AS Plaats, Opdrachtregels.AantalEenheden, Verpakkingen.Verpakking, Goederen.Goederen, Opdrachten.FaktuurValutaCode, Opdrachten.FaktuurKoersTov1Gulden, Opdrachten.FaktuurKoersTov1VV, Opdrachtregels.PrijsPerEenheid, Opdrachtregels.BedragExclBTWVV, Opdrachtregels.BedragExclBTWNLG, Opdrachtregels.BedragExclBTWEURO, Opdrachtregels.IDfaktuur, Opdrachtregels.Temperatuur, Opdrachtregels.BrutoGewicht, Opdrachtregels.NettoGewicht, Opdrachtregels.Colli, Opdrachtregels.IT_OpdrachtStatus, Opdrachtregels.GewijzigdOp, Opdrachtregels.GewijzigdDoor, BTWtarieven.Omschrijving FROM BTWtarieven INNER JOIN (Goederen INNER JOIN (((Opdrachtregels INNER JOIN Opdrachten ON Opdrachtregels.IDopdracht=Opdrachten.IDopdracht) INNER JOIN Bedrijven ON Opdrachten.IDopdrachtgever=Bedrijven.IDbedrijf) INNER JOIN Verpakkingen ON Opdrachtregels.IDverpakking=Verpakkingen.IDverpakking) ON Goederen.IDgoed=Opdrachtregels.IDgoed) ON BTWtarieven.IDbtwcode=Opdrachten.BTWpercentage WHERE (((Opdrachtregels.IDdossier)=Forms!frmDossiers!IDdossier));

As you can see it references to the parent form in order to prevent it form getting too much records (db is rather big).

Can it be that there is a memory problem in parent references?

I find it rather odd that a form/subform that runs perfectly in SP0/SP1 suddenly has a lot a problems in SP2/SP3/SP3 hotfix.

Same by the way in Acc2007 SP1. BANG. And Acc2007 is gone.

Form runs perfectly in Acc97 (latest version).

I hope this can help you guys any further.

rvunen@gmail.com

# Roger said on January 3, 2008 7:40 AM:

Hi Zac

Further testing showed that it was caused by a missing 'Public' statement in front of the function Proper.

Changed that but after doing so could not save the module anymore in Access 2003 SP3 rollup and I could not leave Access 2003 anymore.

Save operation failed. Access can not save ... After 3 times telling Access I don't want to save the changes it stopped.

Opening Access 2003 again and trying to access the module Access tells me it can not find the module again though it is there. At least I can see it.

Repairing and compacting does not do the job because the module is still invisible. Checking further shows that all of the modules are invisible for Access now and I can not open any of them. Great.

It gets even nicer. Finding myself a work around to access them by making a new module and then accessing the invisible module again shows that the whole module tree (forms, reports and modules) is gone and the only module that is there is Module1.

Hmmm. I think Access and VB don't work so well together then.

It looks like your team put a bomb under all the VB developements releasing this hotfix. My Access VB code is gone now. When I open a form I get a nice new empty module behind it. Great.

In Access2007 SP1 it seem to work though closing the form takes a while. Opening takes like 3 seconds and closing (DoCmd.Close) 17 seconds. On a Intel Core 2 Dual T7500 :(

There is definitly something very wrong with the memory management of the VB code behind Access2003 and Access2007. In Access2007 it does not get corrupt but in Access2003 it does.

And worse. It breaks the golden rule of database management. Whatever happens with your database you won't lose your data. Well since a form and a module is no more than data of the database, I would consider Access as HIGHLY DANGEROUS IN A DATABASE ENVIRONEMENT.

He guys. Since you do not pay me for doing this I quit using Access 2003 and 2007 for now. It is too dangerous.

I will role out a version of my program in Access97 again. That works fine.

# ROGER said on January 3, 2008 7:43 AM:

PS: Happy Newyear to you guys too.

And get to work now because there is a lot to be done.

Roger

rvunen@gmail.com

# Zac Woodall said on January 3, 2008 4:01 PM:

I'm sorry you experienced such trouble Roger, and I'm also sorry that you've chosen to give up the platform instead of working with us to try and debug your issue.  

Access 2003 and 2007 include new functionality which deals differently with percieved security flaws, such as invalid memory accesses (as in the case of your missing public statement).  While such functionality may be working in Access 97, this is more likely a fluke (flaw in 97 that is being exploited) than an intended design behavior.  I'd be interested to learn whether your case is actually a bug in 2003/2007, or whether it is the former.  Without having your solution in hand, it will be hard for us to do that.  

FWIW, before releasing the SP3 rollup patch to the web, we tested it with a number of customers who were experiencing problems with the SP3 bits.  In all cases the patch addresses their concerns.  I am curious to know what is special about your particular case.

New Comments to this post are disabled