Moving from 97 to 2k3 - problems !!

[Guest]

I have a fairly intense database that I just spent the last weekend updating to Access 2003
The only reason for the move was to take advantage of the Object Dependencies feature to help with house cleaning.
The update seemed painless until I started using the database again. I kept running across more and more things that didnt work any longer. It was bad enough that I had to go back to 97!! Here are a couple things I discovered along the way
1) iif statements in queries used with 'is null' - the 'is null' doesnt work any more. I replaced them with '="" ' (or equals nothing) and it worked
2) reading information from a text box from a subform has changed. The source code in the mainform text box used to be: =[subform]![subform textbox]. But now its =[subform].[form]![subform textbox]
3) Help - the help on Access 2003 is horrible. Neither of the above items were mentioned. In fact I discovered the solution to the subform issue in the sample database. The help was wrong. Also, they got rid of the context sensitive 'Whats This' arrow. This is the arrow that (under the help menu) you could pick and then click on anything in the database and it gave you help on it. What a great idea. What happened to it
4) Changed formatting - for some reason during the upgrade it changed some of the formatting in the tables. I dont know why, maybe it read the existing data and changed accordingly. The areas changed were the field size and allow zero length. I import tables from another application into existing tables. It didnt like that anymore.
There are several more issues that I have not found the solutions to yet so I didnt list them here.
When I get them fixed, I will list them. However, since I have gone back to 97, I am not sure I will spend much time on it any more. Typically when a product is upgraded, it is to fix existing problems and add features, not alter existing functionality. They may have made these changes along the way in 2000 or XP, but I never saw the need to upgrade to them either.
Sorry for venting - but just beware anyone looking to upgrade !

 

[Douglas J. Steele]

I can't address all of your issues, but you were lucky if you got Is Null to
work with an IIf statement before. Is Null is for use in SQL queries. To
determine whether a field is null, use the IsNull function:

IIf(IsNull(MyField), "It's Null", "It's Not Null")

Note that Null and "" are NOT the same thing. Null means you don't know what
the value is. "" means you know what the value is, and it's nothing. If you
want to check both, use Len(MyField & vbNullString) = 0

The correct syntax always was =[subform].[form]![subform textbox] . Access
97 must have let people be "sloppy", and the rules were tightened up. That
may actually have been changed in one of the 2 versions of Access between 97
and 2003. .Net is even stricter about correct syntax, BTW.

Access 97 was the last version to have decent help in it. I haven't used
Access 2003 yet, but I understand that its help is slightly better than
Access 2000 or 2002.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have a fairly intense database that I just spent the last weekend updating to Access 2003.
The only reason for the move was to take advantage of the Object

Dependencies feature to help with house cleaning.

The update seemed painless until I started using the database again. I

kept running across more and more things that didnt work any longer. It was
bad enough that I had to go back to 97!! Here are a couple things I
discovered along the way.

1) iif statements in queries used with 'is null' - the 'is null' doesnt

work any more. I replaced them with '="" ' (or equals nothing) and it
worked.

2) reading information from a text box from a subform has changed. The

source code in the mainform text box used to be: =[subform]![subform
textbox]. But now its =[subform].[form]![subform textbox]

3) Help - the help on Access 2003 is horrible. Neither of the above items

were mentioned. In fact I discovered the solution to the subform issue in
the sample database. The help was wrong. Also, they got rid of the context
sensitive 'Whats This' arrow. This is the arrow that (under the help menu)
you could pick and then click on anything in the database and it gave you
help on it. What a great idea. What happened to it?

4) Changed formatting - for some reason during the upgrade it changed some

of the formatting in the tables. I dont know why, maybe it read the
existing data and changed accordingly. The areas changed were the field
size and allow zero length. I import tables from another application into
existing tables. It didnt like that anymore.

There are several more issues that I have not found the solutions to yet so I didnt list them here.
When I get them fixed, I will list them. However, since I have gone back

to 97, I am not sure I will spend much time on it any more. Typically when
a product is upgraded, it is to fix existing problems and add features, not
alter existing functionality. They may have made these changes along the
way in 2000 or XP, but I never saw the need to upgrade to them either.

 

[Mike Wachal]

Hi Donovan,

I'm sorry to hear that you've been having some problems migrating you
application to Access 2003. Some of the issues you've outlined below are
expected, others are not, I've made some specific comments in-line.

As you've surmised, there have been changes to the product in every
version, and you are seeing the cumulative effect by skipping several
versions in between. You might want to consider contacting Microsoft
Support (http://support.microsoft.com has information on how to contact
them) as they will be able to help you resolve any remaining issues you
have.

--
Regards,
Mike Wachal
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

I have a fairly intense database that I just spent the last weekend updating to Access 2003.
The only reason for the move was to take advantage of the Object

Dependencies feature to help with house cleaning.

The update seemed painless until I started using the database again. I

kept running across more and more things that didnt work any longer. It
was bad enough that I had to go back to 97!! Here are a couple things I
discovered along the way.

1) iif statements in queries used with 'is null' - the 'is null' doesnt

work any more. I replaced them with '="" ' (or equals nothing) and it
worked.

[Mike] Access 2003 has the ability to block unsafe expressions using the
Jet 4.0 Sandbox Mode, but to do so correctly, your computer must have Jet
4.0 SP8 installed. If you have Jet 4.0 SP6 or earlier installed and have
expression blocking turned on, IsNull is blocked and will stop working. You
should install Jet 4.0 SP8 from http://windowsupdate.microsoft.com (it is a
recomended update) and this will likely cause the problem to go away. You
can learn more about Jet sandbox mode in the Knowledge Base at
http://support.microsoft.com/default.aspx?kbid=294698.

2) reading information from a text box from a subform has changed. The

source code in the mainform text box used to be: =[subform]![subform
textbox]. But now its =[subform].[form]![subform textbox]

[Mike] You should be able to access a text box on a subform without using
the Form property. I've reported this problem to the development team,
thanks for bring it to my attention.

3) Help - the help on Access 2003 is horrible. Neither of the above

items were mentioned. In fact I discovered the solution to the subform
issue in the sample database. The help was wrong. Also, they got rid of
the context sensitive 'Whats This' arrow. This is the arrow that (under
the help menu) you could pick and then click on anything in the database
and it gave you help on it. What a great idea. What happened to it?

[Mike] The help system has been changed significantly since Access 97.
While the help in Access 2000 was not very good, it has been significantly
improved in Access 2003.

The What's This arrow has been removed from all Office 2003 products. You
will still find it available in some of the Access 2003 dialogs, but as a
general help tool it has been removed. In Access 2003, you can get help on
most things simply by selecting them and hitting the F1 key to get context
sensitive help. In general, this is what has replaced What's This help.

4) Changed formatting - for some reason during the upgrade it changed

some of the formatting in the tables. I dont know why, maybe it read the
existing data and changed accordingly. The areas changed were the field
size and allow zero length. I import tables from another application into
existing tables. It didnt like that anymore.

[Mike] The default values for a few of the table properties has changed
over time. These properties should not be affected during conversion, but
if you create new tables, then the new dafaults will be used. Since it
seems that you did indeed create new tables and then brought your data into
the new tables, you are seeing the new defaults. In most appications, the
changes to the table properties are not important, but if you have an
application that relies on the ability to have zero length strings in your
table (or prevent them) then you will need to modify the property in those
cases.

There are several more issues that I have not found the solutions to yet so I didnt list them here.
When I get them fixed, I will list them. However, since I have gone back

to 97, I am not sure I will spend much time on it any more. Typically when
a product is upgraded, it is to fix existing problems and add features, not
alter existing functionality. They may have made these changes along the
way in 2000 or XP, but I never saw the need to upgrade to them either.