Found: 2000 / 2002 incompatability (AXP 10.2627.2625)

Topic

New Reply

In a function to provide one or two character day abbreviations, the statement

Abbrev = Left(Format(d,

Reply | Quote

Replies

Whatever the message, you may have some other problem in Access2000, I was able to run that first equation fine in Access2000.

Reply | Quote

Do you have both 2000 and 2002 installed on the same machine?

I wonder if this could be the cause, as I found that having 97 and 2000 installed on the same machine produced funny things like this also.

Jayden

Reply | Quote

Nope, 2002 on one 2000 on another.

This was found by my partner on a third machine, which has only 2000 also.

Reply | Quote

I have tested the expression on my machine. I have Access 97, 2000, and 2002 installed and it works in all versions. Only problem I can see is that you are using a “constant” as a variable name. “d” is a constant for “Day” in Access and could very well be confusing Access into producing the error message you are receiving.

RDH

Reply | Quote

No, ‘d’ is not a constant. I shortchanged you as far as the function goes. Here it is complete:

Public Function DayOfWeekAbbrev(d As Date) As String
'   return one or two chars to indicate the day of the week
    
    Dim wd As VbDayOfWeek    '   weekday, per vba conventions
    wd = Weekday(d)
    
    Dim s As String '   bypasses bug in "Left(Format(d,"ddd"),1)
    s = Format(d, "ddd")
    
    Select Case wd
        Case vbMonday, vbTuesday, vbWednesday, vbFriday '   single letter abbrev
            DayOfWeekAbbrev = Left(s, 1)
        Case vbSunday, vbThursday, vbSaturday   '   two char abbrev
            DayOfWeekAbbrev = Left(s, 2)
        Case Else
            '   can't happen
            DayOfWeekAbbrev = ""
    End Select
End Function

Reply | Quote

Well I guess it’s all personal preference …. but I would never declare a variable name of a Date/Time datatype “d”. I have seen Access confused by much less of my years of programming.

Anyway …. here is another function I quickly wrote that will do the same thing as yours without all the lines:

Public Function fDayAbbrev(dtEntry As Date) As String
Select Case WeekDay(dtEntry)

Reply | Quote

I notice that you have used the values (2,4,6) etc, instead of using vbMonday etc.

Just a small point that I thought that I would comment on.

This isn’t the best thing to do, because it isn’t guaranteed that later versions of Access will have the weekday function that returns the value for Monday evaluate to 2 – but it should always evaluate to the vbMonday constant. This is why MS include these types of constants.

Cheers

Jayden

Reply | Quote

If VBA cant tell that d as Date defines the variable concisely, and as accurately as anything more verbose, then we are looking at a disaster. Of course, it CAN tell, and in a scope of 16 lines or so, a one-character variable name is entirely adequate.

Unfortunately, your function does NOT do the same thing as mine: it gets the abbreviations wrong, and one cant tell for what days they are wrong. You would’ve been better off using the built-in “day symbols” rather than integers, since VBA makes them easy to include with their “Intellisense”

Reply | Quote

Maybe it was a mistake on my part to get involved in this thread. I have just tested the function again and can not find where it errors. The only thing I see is that I have Tuesday being calculated as “TU” instead of “T”. I thought that it may keep someone from confusing “T” with Thursday. As far as the “integers” … I have no problem knowing that the WeekDay() function returns an integer from 1 to 7. I have been programming in Access for almost 8 years and have seen Access get “sideways” by many things that can drive you completely nuts. I have only been a member here at this site for a short time but that does not mean I’m “New” at Access. I am a Moderator/Editor at A3 Forums (Access All Areas) and have answered many thousands of questions about Access at many sites. This is not to say that I can not be wrong (I have been wrong many times). I am very sorry if I offended you with my reply and will not respond further to this thread.

RDH

Reply | Quote

I take no offense at any of your remarks. The mistake is the statement “will do the same thing as yours”.

Jayden makes the cogent point that Weekday()’s return value may change in the future. If this does happen, altho I would expect the possibility to be very low, code that relies on an out-of-date distribution of integers would break, quietly, and the debugging of that problem would be a fierce challenge.

Reply | Quote

As you know quite well, VBA can tell what a variable is from the declaration. However, most of us have discarded the old single-letter variable names for self-documenting variable names, regardless of the length of the routine. That is entirely a matter of personal preference and programming style. I do agree that using the built-in constants is preferable to hard coding their numeric equivalents, although of course you must use the numeric equivalents in queries.

The fact is that the function you posted *works* in Access 2000 as well as in Access 2002. Mark tested it, and I tested it as well, pasting your code in to a module in A2k and running it. It is NOT an Access 2000 problem. Access isn’t “screwing around with good code and crashing” on our machines, so you need to look elsewhere for your problem.

We can all understand your frustration because we’ve all experienced similar situations. However, please be sure your replies to other posters remain courteous.

Reply | Quote

I am having some difficulty verifying [indent]

---

However, most of us have discarded the old single-letter variable names for self-documenting variable names, regardless of the length of the routine. That is entirely a matter of personal preference and programming style.

---

[/indent]
I checked at the Software Engineering Institute site http://interactive.sei.cmu.edu/Features/19…round.jun99.htm to see if i could find any statistics on “most of us”.Here’s a quote from the article “Pathways to Process Maturity: The Personal Software Process and Team Software Process” by Watts Humphrey, which I recommend in its entirety:
[indent]

---

Software engineers develop their personal practices when they first learn to write programs. Since they are given little or no professional guidance on how to do the work, most engineers start off with exceedingly poor personal practices. As they gain experience, some engineers may change and improve their practices, but many do not. In general, the highly varied ways in which individual software engineers work are rarely based on a sound analysis of available methods and practices.

---

[/indent]I read this as saying that “most of us” have NOT learned much, and are in fact doing a lot of wrong stuff! Since your statement contradicts this research, I wonder if you could point me to the studies that support your statement?

Reply | Quote

Take a look around the Lounge at the code posted here.

Reply | Quote

You wrote:
>>What IS bad is Access screwing around with good code and crashing.<<

The reality of the situation is that if your problem can not be reproduced anywhere else, then it appears it has to be related to the configuration on your machines. When the code failed on the 3rd machine, where they running a database compiled on your machine under Access2002? What happens if they recompile, does problem go away?

If all else fails, how about creating another database in Access2002 and include nothing else but a module containing your original function (the one that failed). If that also fails on all machines, then append it to your next message so we can try it.

Reply | Quote

I’ve tried to eliminate the machine as a variable. It’s plain vanilla Win2k pro. Office 2000 uninstalled, then OXP installed. Clean. Dell 450MHz PIII. This should be an ideal setup for Access development, as it is for other tools. (Altho a mite slow, these days.) I have not tweaked anything, there are no Access Add-Ins. It’s the straight deal.

The app was compiled on that machine, tried on machine 2 (A2K on Win2K). It halted on the Format function with a msg about a missing library!? Recompiled in A2K on that machine, still failed. Tried on machine 3 (A2K, win98), halt on Format function. Jiggle the code on machine 3, it compiles and works. Recode on machine 1 in AxP, it runs everywhere. I conclude at this point that there is a difference in the two compilers. I also conclude that the simpler the code, the better.

That test case is a good idea – i’ll try it.

I am guessing that access compiler works something like this: it has a single file in which it keeps both source and compiled code. These are maintained by pointers. With a simple database, ie, with one module, the pointers should be fairly straightforward. With heavy editing, and a couple of forms, i *presume* that the pointers sometimes get clobbered. When that happens, Acc goes down. Predicting the outcome of a simple test, then, I would guess that the error will not show up (*crosses fingers*).

Reply | Quote

Let me suggest another test – I had a somewhat similar situation a couple of days ago. Take the version that you created with XP to the 2000 PC. Then import all the objects into a new clean DB using 2000 and try compiling the database. I believe there is some obscure behavior in XP that causes databases to either end up with missing references of some sort, or actually crash as I had, when you try to run the same database on 2000. Very frustrating, and you suspect that MS must know something abou it.

Reply | Quote

The difficulty I see here is the Experimental Control – what is it that I have that acts as a Control on the experiment? Or perhaps: what is it that I am testing with this test?

Reply | Quote

Things do happen when you move between machines and versions. Let me tell you what happened to me the other day.

ON my Access2000, I had modified a form for client’s database. I went to client site, and update his database with my new form (he is running Access2002). Among the changes, I had changed the way a combo box had displayed information. When I ran the new form, the combo box displayed the data in the old way, yet other portions of the new form worked. In design mode, I clicked the expression button to open the controlsource SQL in QBE view. I then ran the query, and it displayed the data like it should. I went back to form view, and it was still displaying it wrong. I finally just cut the control and repasted it back onto the form, and then it displayed properly! Go figure.

Reply | Quote

From the posts i have read here in the Lounge, this confirms for me an indication that Access _still_ has a problem that it has had for [ two || three || several ] versions.

I am of the mind that there is a bug in the file management (oh, that is an easy target, eh!).

The procedures that i follow before releasing anything is one Charlotte recommended: Compact & Repair, Compile, C&R again.

Rhetorical question: Perhaps the additional step of imorting everything into a new database will pass the file contents thru yet another filter so that things work right?

Reply | Quote

The procedure I’d recommend would be: backup, decompile, compact & repair, compile, compact & repair.

Reply | Quote

yeah, that looks reasonable.

Did you follow that procedure for the database with the control that you pasted, or did you bypass it because “this was only a simple one-form change”?

Reply | Quote

I didn’t do the uncompile there. The situation was different from my normal “modus operandi”, in that I am maintaining the database on the client’s system. So if I update a form on my system, I just delete the old form on their database and import my new form. I should get in habit of recompiling (I’m trying to work out all the details of doing it all automatically).

Reply | Quote

Is this a reasonable summing up:
1. 2000/2002 incompatability has NOT been found as I asserted originally.
2. There are bugs in the way compiled code is stored in the mdb, which have existed in several releases/versions of the Access product.
3. Dealing with 2. requires several additional (at this time) manual processes on the part of a developer before an app should be released.

Reply | Quote

Well, there are no simple answers to your statements. The best I can say is:

1. There incompatibility you described doesn’t appear to be reproducable. Which doesn’t mean it doesn’t exist. It just may mean we done’t have the same exact set of circumstances.

2. There will always be bugs. I don’t think it is the same set of bugs that have been carried forward from version to version.

3. I think it is safe to say that everyone agrees that things happen to databases the more they are “manipulated”. That is, when objects are changed, deleted, added, etc. It is not predictable. For example I just got a “Reserved error (-1517)” error, for no apparent reason. I compacted and it went away, but why? But as a means of eliminating potential problems, the manual steps discussed are best.

Reply | Quote

It looks like we are in violent agreement. If the answers were simple, one would think that the questions would not be asked.
Re 2., what is disturbing to see is the measures that are required to cater for the existence of an apparently well-known situation.
Do other database tools have the same problems? Access is all I am mildly familiar with, but I do know that dBase separates tables and indexes into separate files. Is the failure rate for dBase similar to Access?
Not sure if the data are available to answer these either.

Reply | Quote

you wrote:
>>Do other database tools have the same problems? Access is all I am mildly familiar with, but I do know that dBase separates tables and indexes into separate files. Is the failure rate for dBase similar to Access?<<

I don't really know. However, I think it is worthwhile to note that most of the problems we've discussed occur in the frontend of a database, and not in the backend which contains the data. (Another reason to use split databases!).

Reply | Quote

Yes, in the FE. So where in the “release” rigmarole do you do the split?

* backup * decompile * compact & repair * compile * compact & repair *

Reply | Quote

you wrote:
>>Yes, in the FE. So where in the “release” rigmarole do you do the split?<<

I split all my databases, and I do it right from the beginning. I first create the backend database, then the frontend.

Reply | Quote

That is the first time I’ve heard of this approach. Does it go something like this:
1. Tables & relations in B.mdb
2. “Constant tables” + Everything else in F.mbd, and link to the tables.
3. Continue development by opening B to refine tables, opening F to refine everything else.
4. Release

The “textbook” approach is what i am following, such that the Database Splitter is used when it’s time for Release. Does the split ab initio buy you something like fewer crashes during development?

Reply | Quote

Build your databases split from the start. That will keep you from using code that only works with local tables (for example) and building in errors when you split the database. The idea is to plan your schema carefully enough so you won’t have to constantly tweak the back end tables.

If by “constant” tables, you mean lookup tables, I usually put those in the back end as well. The only tables I put in the FE are tables that I want to keep local so that multiple users can use them in their individual sessions without interfering with other users. If there’s going to be heavy network traffice, I may break down and put lookup tables in the front end, but then I have to build a mechanism for keeping them up to date so that all users are dealing with the same lookup lists.

Tables and their relationships are the only thing I put in the backend. All code either goes in the front end or in a database I reference as a code library. Queries, forms and reports all go in the front end.

Reply | Quote

I shall give the split from the beginning idea a try.

Constant tables= names of months, days, years, zipcodes, states, etc. = lookup tables, right.

This is a valuable lesson that you and Mark are teaching me – thanks!

Reply | Quote

Since I know I’m going to use a split database, I see no advantage in waiting until the last minute to do the splitting. I don’t need to use the Access Database Splitter, since I start off with split databases. Why introduce another factor just before releast?

Actually, I routinely have more than 1 backend database. One backend is for the application tables, the other for my menuing and report selection tables (which aren’t related in any way to the application tables). I’ve even on occasion used a 3rd backend database for large temporary tables.

I’m not sure I understand what you mean by “Constant Tables”, and why they should go in the frontend.

Reply | Quote

As I mentioned in my previous post to Charlotte – you and she are teaching me valuable lessons. Thanks!

Constant tables = lookup tables. Putting them in the FE seems reasonable.

With multiple BEs, are you primarily linking thru runtime code, or do you build the app using the Linked Table Manager?

Reply | Quote

Putting Constant Tables in the frontend deprives you of the advantage of being able to enforce referential integrity between such a constant table and other tables that reference it. Plus, if you do wish to modify them, you have to make sure you do it on every workstation. I see no real advantage to keeping them local.

If relinking is necessary in the field, I have code that will do this, rather than relying on the Linked TAble Manager, especially since the LTM is not available in runtime versions.

Reply | Quote

The failure rate of what? Do you mean how often does the program stop working? Each database language–in fact, each programming language–has its own personal set of problems and failures. They differ because the logic and structure behind the apps differ, but they all have them right up to the big guys like SQL Server and Oracle.

Back in the very early days of dBase (I worked with the first version and kept it up til Access was introduced in 1992), long before Windows, dBase had a built-in programming editor. That sounds handy, but we all used a text editor like WordStar to write our code. Nobody who programmed seriously would use the built-in editor because when its buffer was full, it would simply error out and dump *all* your code without any warning.

Reply | Quote

Good question – what to measure, and how to measure it. How about this: “Software quality is inversely proportional to the sum of the number of times it is cursed plus the number of times the heritage of the producers is impugned by a reasonable user.”

Certainly the IDE going down “often” is an indication that the software needs to be flushed into the bitbucket. That was the determining reason that I dumped Word”Perfect”. AXP is better than A2K in this regard: at least it saves a backup copy of the database ere it goes down. I would classify that as a “softer failure” than losing all your code.

Having “released” other kinds of apps, it is surprising to me the number of wickets one has to jump thru with Access to release an app that doesnt crash on arrival. I suppose it is a big improvement over earlier versions of Access? The last time I touched dBase was version 2 or 3, I think, on an Apple ][e that used tapes. Everything was painful on that machine, except the StarTrek game.

Reply | Quote

Blame the Package and Deployment Wizard for some of the problems in getting a setup to run. It is a decidedly inferior product, and I use InstallShield on my personal machine instead.

As for the IDE going down. I see that more in Access 97 on my Win2k Pro machine at work than in Access 2000 on my personal Win2k Pro laptop. It appears to me that it’s more an issue of what is installed on a given machine and how than a problem with any particular piece of software. I don’t toss software until I can reproduce the problem on a variety of platforms. One of the variables at work is that we use Visual Source Safe and it seems to make everything twitchier than the same setup without that additional joker in the pack.

Reply | Quote

LOL! I blame all my problems on all the software!

Additional software is something i have tried to avoid, including add-ins. I’d much prefer stand-alone products.

Reply | Quote