Upsize from Access 97 to SQL 2000 Problem

[Terry]

Hello,

Has anyone experienced the following problem following an Upsize from
Access 97 to SQL 2000 using the MS Upsize Wizard? Or can anyone see
what the problem might be.

Before Upsize (Access 97 front end and Access 97 tables): A MainForm
loads up correctly and then I enter a ReferenceNumber value in a
MainForm field control. I then click on the first field of a SubForm,
the ReferenceNumber then shows at the top of the SubForm and further
subform details can be entered. Finally, I can click on a SubForm
button to open another Form which allows me to enter more details. (It
also includes what I have already entered on the previous SubForm.)
This works...

After Upsize (Access 97 front end and SQL 2000 tables): ...As above ...
Finally, I click on a SubForm button to open another SubForm which
allows me to enter more details and at this point it returns an error
saying SubForm record does not exist.

Points that may be relevant. There are no triggers on the SQL tables.
There are three tables involved MainForm data table, SubForm data table
and a third table acting as a link between the first two tables. The
SQL table relationships appear to match those in Access 97. In the
Before Upsize all these tables get created, in the After Upsize only
the first two tables are created not the linking table. The SubForm has
the correct Child and Master links (as in the Before Upsize database).
As far as I can see the only difference between the Before and After is
that I am accessing SQL tables. There is no code creating any of these
records only the implicit SubForm Child/Master field links.
Interestingly, if I add triggers to the tables this ensures that all
records are created, however the error still appears and will not go
away until I close down the MainForm then reopen it.

Thanks for your time in reading this problem and any advice or help you
can give.

Kind regards

Terry

[Larry Linson]

There can be some problems with using an MDB with Jet, ODBC and server
databases because the server equivalent of AutoNumber is not assigned until
the record is written to the SQL Server table, and is not passed back to
ODBC and then to Jet.

This may well be what you are experiencing. I would assume that the key
field you describe is for the "parent" record and the actual key of the
"child" table may be a separate AutoNumber equivalent (perhaps in
combination with the key of the parent record).

To avoid this problem with both Informix and SQL Server databases, we had a
Stored Procedure that would obtain a "next id", create the record in the
server table, and return the id... so that we were not actually "adding a
new record from the Access front end" but editing a record newly-added by
the Stored Procedure. That was a couple of versions ago on SQL Server, but
as far as I know, that part still works the same with ODBC and Jet.

Larry Linson
Microsoft Access MVP

"Terry" <Te***@supradata.net> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Hello,

Has anyone experienced the following problem following an Upsize from
Access 97 to SQL 2000 using the MS Upsize Wizard? Or can anyone see
what the problem might be.

Before Upsize (Access 97 front end and Access 97 tables): A MainForm
loads up correctly and then I enter a ReferenceNumber value in a
MainForm field control. I then click on the first field of a SubForm,
the ReferenceNumber then shows at the top of the SubForm and further
subform details can be entered. Finally, I can click on a SubForm
button to open another Form which allows me to enter more details. (It
also includes what I have already entered on the previous SubForm.)
This works...

After Upsize (Access 97 front end and SQL 2000 tables): ...As above ...
Finally, I click on a SubForm button to open another SubForm which
allows me to enter more details and at this point it returns an error
saying SubForm record does not exist.

Points that may be relevant. There are no triggers on the SQL tables.
There are three tables involved MainForm data table, SubForm data table
and a third table acting as a link between the first two tables. The
SQL table relationships appear to match those in Access 97. In the
Before Upsize all these tables get created, in the After Upsize only
the first two tables are created not the linking table. The SubForm has
the correct Child and Master links (as in the Before Upsize database).
As far as I can see the only difference between the Before and After is
that I am accessing SQL tables. There is no code creating any of these
records only the implicit SubForm Child/Master field links.
Interestingly, if I add triggers to the tables this ensures that all
records are created, however the error still appears and will not go
away until I close down the MainForm then reopen it.

Thanks for your time in reading this problem and any advice or help you
can give.

Kind regards

Terry