Access 2003 to 2000 bug

[Skeletal]

This must be a common question, but I can’t find an answer!

I have a db built in Access 2003 but compatible with 2000. If I only use 2003 (to run the db) there is no problem; if I use only 2000 there is no problem. However, if I use 2003, shut it down and then try to use 2000, the first time the db opens, a form I have that loads automatically and then should maximise, does not, the blue band at the top of the form is visible (i.e. not maximised).

In this sate the db will not work (even if I manually maximise the form). I then shut it down, open it, and all is well.

I have had many problems with Access 2003; an earlier db I built would crash approximately on the thirtieth opening, and once crashed would never work again. However, it would open in 2000, then it would be OK opening in 2003 for about another 30 times, until crashing again.

The two dbs I have built are nothing like one another. It is also odd that many other crashes/locked dbs I get can always be solved by opening in 2000.

The dbs are used by many people on many computers and operating systems (with 2000 and 2003 which is why I need it to work on both) and the problems can be perfectly replicated on any of the systems.

This is clearly a bug in Access 2003, and is so common, someone (all of you??) will have come across it.

How did you solve it? When is the next service pack coming out?

Thanks

Skeletal

 

[RoyVidar]

The not working in 2000 after usage in 2003 - no solution, but a workaround.

Since the code simply don't work, place a label on the opening form with some text "Sorry, there's been an error opening the database, try closing it and reopen".

Then in the on open/load event of the form, add some code to replace this message with a more friendly one.

If the friendly text appears, all is well, if not, the user will know what to do...

The chrashing - I'd say, ensure you're on the latest available service pack. On my systems, I usually only have crashes on db's under development. I would guess there might be some slight code corruption - have you tried the /decompile?
http://www.granite.ab.ca/access/decompile.htm

 

[Skeletal]

Thanks...

for the info, particularly the link. Some of the comments in the de-compile stuff sound very familiar to problems I've had!

I have been trying very hard myself to track this down (at least on this db) and I have found that it is the presence of a text box that has:

=DSum("Time","[qry_subtotalProject]")

in it. I have got rid of everything on the form except one single box with that in it!! I have also got rid of all code in the entire db except the "maximise". It is thus the presence of the "DSUM".

I sort of expected loads of replies to this, but I guess the "D" functions aren't used a lot...but even so I'm suprised uSoft haven't done anything about it. Am I the only person to use "D's"?!

I am using the latest service pack.

The trouble with the work-around is I cannot use code to sort it. The non-maximised form is the result of the VBA not running "maximise", because NO code runs; it is only the symptom.

I suppose I could change "Ds" into code, but I use a lot of them!

I will play with the decompile stuff, and I would love to hear of anyone trying to use a "D" between 2000 and 2003!!

Skeletal

 

[RoyVidar]

Lot of people uses the Domain Aggregates. I don't. For some replacement functions, have a look at
http://www.mvps.org/access/modules/mdl0012.htm

I guess you misunderstood the workaround.

In a label on the form put in a text:

"Warning - the db didn't open correctly, please close and reopen it"
-> in design view, then save the form <-

Then have some code in the form open/load altering it to a more friendly text (or no text at all) => if no code runs, the user get's the above message, and would hopefully close and reopen the database.

If code runs, well, then the first thing the code should do, is replace the caption of the label to a more friendly text.

Time is a reserved word (Time function), might be a problem, try surrounding with [brackets]

 

[Skeletal]

Thank you again!

Another useful link!

Yes, after posting I started to realise what you meant (and may do this, I'm just irritated that this bug seems to exist...I got excited by the reserved word idea, but changing it didn't make it work either); I also wondered what would happen if I used a new form (without Ds on it!) to open automatically, then after that opened, to get it to open the form with the Ds. This seems to work, but then I want the new form to close automatically.

But, perhaps not suprisingly, trying to get a form to close on its open event won't work, neither does trying to get it to close on the open event of the second form work either...head hurting... it's time I went to bed now!).

Perhaps I need to get rid of the Ds!

Thanks again.

Skeletal

 

[Skeletal]

As a follow up to anyone who has this problem, I have just built a db with one table having 2 fields, 3 records; one query; one form with a text box having DSUM(field1, query), and set the auto-start to open the form which has "maximise" as its only bit of code.

Result...exactly as described!!

Thus, there is now no doubt whatsoever that there is a bug in the domain aggregates.

So why can't I find mention of this anywhere?!

And lastly, I forgot that the way to open/close forms in code is to use the form's timer event rather than its open event...silly me.

Thanks for the help, there is some useful info in those links.

So, do I do the one form opening another trick, or get rid of the Ds??!!! Ah well.

Skeletal

 

[RoyVidar]

I'm not sure it is the domain aggregates, I don't use them, and have the same occuring on my setups. Would you mind trying out some other functions from the Access.Application library (NZ, CurrentDB, CurrentProject, CurrentUser...) and see if the challenge is persistent?

What I suspect, and emphasize suspect, is that when you've done some alterations in 2003, then try to open in 2000 (I've found it to most often occur when there's been some design or code changes in later version), all references aren't coerced back to 9.0 when opening the database the first time. But entering VBE and hit enter/backspace do a save/compile, or the simple close and reopen seems to do the trick.

So unless someone comes up with a brilliant idea, I /decompile, compile on the 2000 version prior to distributing databases, have the initial form display something like the above, and well, the customers pay the bill

I don't want to make any strong recommandations, only evaluate whether this may become a challenge for your customers/users. If so, allow your db to handle it in some way, for instance the above.

Why it isn't mentioned more - I've wondered too, but am thinking not many need the database to work on all versions (i e opening and using the same database with different versions). Usually, the user receives their front end which is located on their computer, and they don't go around copying it to other computers. It may seem this is a challenge where few are affected. Some few developers, and some of my users

 

[Skeletal]

Ah well, this is interesting and I’m really pleased you have these problems as well (err… if you see what I mean! I was beginning to think I was losing my marbles!) My first problem with Access 2003 occurred about a year ago, (the work 30 times, then crash). The only solution for me at that time was to revert to 2000 (for that application, all users had 2000 so that wasn’t a problem). I did everything in 2000, then converted a copy to 2003 to use the useful object dependences thing, but the code to the customer remained in 2000.

But, and this sounds hard to believe but it is true, as I was trying to see why my code was causing the crash, I merely made a minor change to a COMMENT, and this caused a crash! It was at this point I realised there were/are strange bugs in Access 2003!

Thus, to take your point, if commented lines can cause crashes I’m sure code (of any sort) will!

Your earlier post is the first time I’ve heard of de-compile and this is something I will try; this may have cured my earlier problems as well.

The earlier db had about 6000 lines of code, and the current one about 1500 lines and I use many functions; the earlier one also used the Word library as I ran an instance of Word within Access, the point being that they are stable, in 2000, but sometimes dodgy in 2003/become dodgy after being in 2003.

On both dbs, the ONLY time they ever crash is as they open up; I therefore think you are on to something. I have removed the Ds on the auto-opening form and replaced them with the code from one of the links. Up to now, everything works fine (but I want to play some more/try the decompile, before releasing this new version).

I’m still mystified why all forums haven’t been buzzing with this for the last couple of years…I would have thought “mega programmers” would be doing far clever/bigger things than me and would have come across these problems within 10 minutes of 2003 being released!

Skeletal

 

[Skeletal]

I just tried the de-compile thing on the latest db and it seemed to work, but nothing like it says in the articles. There was hardly any reduction in size.

I also found a copy of my “broken” db from last year. Decompiling that made it work again (so there looks to be something in this!), but still almost no reduction is size.

Also, a bit odd, the first times I used it, going into the VBA screen showed that the code needed compiling again (makes sense). But the last few times I’ve tried, the compile word is greyed out (i.e. it looks like it didn’t decompile); a minor code change enabled compile again. Is this normal? Seems odd to me.

Skeletal

 

[pdx_man]

Here's my 2 cents on this.

The problem is not with the domain functions, it is with references to code libraries. While in a module, go to Tools ... References in both versions. You will notice that there are two different sets of some of the referenced libraries. When Access gets confused with references, it will generate errors like this. It has nothing to do with the actual function. I've received the same error on the LEN() function ... fixed my references and voila!! Now, the fun part really starts up when you have code referencing old libraries that are different and the code doesn't exist, but is still out there in some hidden Access area, hence the need for the decompile. You will not see any issues with the references, but the code that isn't there any more (being rid of with the decompile) does reference it and this ambiguous error is generated.

 

[Skeletal]

That's very interesting! You are correct, there are different versions in the references. I had a problem with this last year when we installed my application as run-time on a server and multi clients. As the run time stuff did its work, an error message appeared saying about references to Word 9 vs Word 11 (can't remember exactly). The thing was that I used an instance of Word within Access, and I developed the app on my machine which has Word 11 on it. The machines I was installing it on had Word 9...error!

I had to find a reference library for Word 9; I put that into my app (with some difficulty because Office insists on trying to put the references it wants in, not what I want!) and all was well.

I'll play a bit more although I think that, somehow, it seems to sort itself out if, in the current case, I don't have Ds in a text box, but instead use the coded versions discussed earlier (which also run much faster than the Access ones).

Thanks for this useful info.

Skeletal

 

[RoyVidar]

To avoid reference challenges when doing automation, try late binding.

 

[Skeletal]

Yes, that's a good idea if/when I run other apps from Access, or indeed any apps from any other (I also run Powerpoint and Word from Excel).

Trouble is, I really like to have everything "all together" so to speak; I don't really like to have things "open". Mind you, I wonder if I could do a late binding from Access to Access! 'Cus, what's annoying about this is that I have deliberately chosen the "file type as Access 2000" (or whatever it's called) for the very reason of overcoming this sort of problem. A bit annoying that Access is not compatible with Access!

Thanks for the tips.

Skeletal