B
Bostjan
Hello,
We are developing an Access application using SQL Server 2000 as the
database back-end. We are migrating the entire application to .Net, however
we still need to support the "old" Access version for some years to come.
We've recently experienced a change in behaviour from Access XP to Access
2003 when it comes to accessing the SQL Server 2000 database.
Problem:
======
Within the Access form we have several fields bounded to our (remote) SQL
database table. For example: we have a customer form to manage customers and
say three bounded fields:
1) CustomerId; this is the primary key (int, not null, identity seed 1,1) in
the database table and hence an autonumber in Access (and it is set to
invisible on the Access form)
2) CustomerGivenName; this is an nvarchar in the database table and it is
shown on the Access form
3) CustomerFamilyName; same as 2)
.... (other fields)
The problem is that Access XP allowed for the following code even BEFORE the
customer record was saved into the database:
If IsNull(Me.CustomerId) Then
... the record was not yet inserted
Else
... the record is already present in the database
End If
It seems the new version of Access 2003 does not allow such a query -
instead it fails on "IsNull(Me.CustomerId)" with the following message:
31004 The value of an (Autonumber) field cannot be retrieved prior to being
saved.
Which is of course a very clear message: don't even try to use me cause I am
not saved. The problem is that the code as per above is present basically in
every single corner of our application - it would be virtuall impossible for
us to change the code.
Is there any workaround/patch/whatsoever that we could do WITHOUT changing
any code? The application is huge and there is no way we could revisit all
such occurencies. The SQL database contains nearly 100 tables (and thus a
similar number of forms; some of which are quite complex - and most of them
using the IsNull(...) concept to query whether the record has been already
saved or not).
Thanks and best regards,
Bostjan Augustin.
We are developing an Access application using SQL Server 2000 as the
database back-end. We are migrating the entire application to .Net, however
we still need to support the "old" Access version for some years to come.
We've recently experienced a change in behaviour from Access XP to Access
2003 when it comes to accessing the SQL Server 2000 database.
Problem:
======
Within the Access form we have several fields bounded to our (remote) SQL
database table. For example: we have a customer form to manage customers and
say three bounded fields:
1) CustomerId; this is the primary key (int, not null, identity seed 1,1) in
the database table and hence an autonumber in Access (and it is set to
invisible on the Access form)
2) CustomerGivenName; this is an nvarchar in the database table and it is
shown on the Access form
3) CustomerFamilyName; same as 2)
.... (other fields)
The problem is that Access XP allowed for the following code even BEFORE the
customer record was saved into the database:
If IsNull(Me.CustomerId) Then
... the record was not yet inserted
Else
... the record is already present in the database
End If
It seems the new version of Access 2003 does not allow such a query -
instead it fails on "IsNull(Me.CustomerId)" with the following message:
31004 The value of an (Autonumber) field cannot be retrieved prior to being
saved.
Which is of course a very clear message: don't even try to use me cause I am
not saved. The problem is that the code as per above is present basically in
every single corner of our application - it would be virtuall impossible for
us to change the code.
Is there any workaround/patch/whatsoever that we could do WITHOUT changing
any code? The application is huge and there is no way we could revisit all
such occurencies. The SQL database contains nearly 100 tables (and thus a
similar number of forms; some of which are quite complex - and most of them
using the IsNull(...) concept to query whether the record has been already
saved or not).
Thanks and best regards,
Bostjan Augustin.