Problem pasting to a sub form with a SQL 2003 Back End

[Bobby]

Hi,
I have an application which uses Access 2003 as both the FE and the BE.
However, for a number of good reasons, I am in the process of
converting the BE to SQL server 2003. I am not converting the FE to
ADP, I am simply linking the tables using ODBC. I have overcome a
number of isues and it is working quite nicely, however I do have an
issue with a sub form.

I have a main form with two sub forms. I can enter text directly into
the sub forms and it works fine, as I would expect. However, I need to
be able to copy a number of lines from one sub form, and paste them
into the other (both sub forms have exactly the same field names and
types, but they are based on different tables). When I do this, every
field in the second sub form (i.e. the one i am pasting to) is
displayed as #Deleted, yet if I close the main form and go back in, it
has pasted successfully, and all the lines are displayed.

This works fine with the Access 2003 BE. Can anybody suggest a reason
why this might be happening? I did have some code on the BeforeInsert
event of the sub form which I thought might have been causing it, but I
have taken that out and it is still happening.

Thanks for any advice,

Colin

[Larry Linson]

Likely you have an Autonumber (SQL Server "identity" field) as the key...
and in many cases, one of which you have encountered, ODBC does not
automatically receive the newly created identity field and return to the
Form for display. I've never encountered the situation you describe, because
having multiple tables with the same content is almost certainly indicative
of an "unnormalized" design. There are exceptions, but they are rare... I'd
repeat with emphasis, _rare_. You might be able to do what you want with
VBA code...

With some other server DBs, we found it useful to write a Stored Procedure
that would obtain for us the next key in sequence for a table, and update
the table-of-tables. Thus we didn't use the server's identity field
capability. When we moved some applications to MS SQL Server, we found that
worked very well there, too.

Short of a discussion on what you are accomplishing with the two tables with
identical data, and how that could be done differently, I think perhaps you
may need to create a similar Stored Procedure, and copy the data into the
other table using code.

Larry Linson
Microsoft Access MVP
"Bobby" <bo****@blueyonder.co.ukwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...

Hi,
I have an application which uses Access 2003 as both the FE and the BE.
However, for a number of good reasons, I am in the process of
converting the BE to SQL server 2003. I am not converting the FE to
ADP, I am simply linking the tables using ODBC. I have overcome a
number of isues and it is working quite nicely, however I do have an
issue with a sub form.

I have a main form with two sub forms. I can enter text directly into
the sub forms and it works fine, as I would expect. However, I need to
be able to copy a number of lines from one sub form, and paste them
into the other (both sub forms have exactly the same field names and
types, but they are based on different tables). When I do this, every
field in the second sub form (i.e. the one i am pasting to) is
displayed as #Deleted, yet if I close the main form and go back in, it
has pasted successfully, and all the lines are displayed.

This works fine with the Access 2003 BE. Can anybody suggest a reason
why this might be happening? I did have some code on the BeforeInsert
event of the sub form which I thought might have been causing it, but I
have taken that out and it is still happening.

Thanks for any advice,

Colin

[Bobby]

Larry Linson wrote:

Likely you have an Autonumber (SQL Server "identity" field) as the key...
and in many cases, one of which you have encountered, ODBC does not
automatically receive the newly created identity field and return to the
Form for display. I've never encountered the situation you describe, because
having multiple tables with the same content is almost certainly indicative
of an "unnormalized" design. There are exceptions, but they are rare... I'd
repeat with emphasis, _rare_. You might be able to do what you want with
VBA code...

With some other server DBs, we found it useful to write a Stored Procedure
that would obtain for us the next key in sequence for a table, and update
the table-of-tables. Thus we didn't use the server's identity field
capability. When we moved some applications to MS SQL Server, we found that
worked very well there, too.

Short of a discussion on what you are accomplishing with the two tables with
identical data, and how that could be done differently, I think perhaps you
may need to create a similar Stored Procedure, and copy the data into the
other table using code.

Larry Linson
Microsoft Access MVP

Thanks Larry,
I'll take a look at some of the points you have raised. Just to clear
up one point, the tables don't hold the same data, they just have some
of the same fields. One table is a Purchase Order table, the other is a
Returned Items table. The two sub forms use the same fields from each
table, and I use copy and paste between the two as an easy way for the
user to select which items are being returned, but both tables have
other fields which are more specific to the data which they hold.

Thanks again

Colin

[Bobby]

Bobby wrote:

Hi,
I have an application which uses Access 2003 as both the FE and the BE.
However, for a number of good reasons, I am in the process of
converting the BE to SQL server 2003. I am not converting the FE to
ADP, I am simply linking the tables using ODBC. I have overcome a
number of isues and it is working quite nicely, however I do have an
issue with a sub form.

I have a main form with two sub forms. I can enter text directly into
the sub forms and it works fine, as I would expect. However, I need to
be able to copy a number of lines from one sub form, and paste them
into the other (both sub forms have exactly the same field names and
types, but they are based on different tables). When I do this, every
field in the second sub form (i.e. the one i am pasting to) is
displayed as #Deleted, yet if I close the main form and go back in, it
has pasted successfully, and all the lines are displayed.

This works fine with the Access 2003 BE. Can anybody suggest a reason
why this might be happening? I did have some code on the BeforeInsert
event of the sub form which I thought might have been causing it, but I
have taken that out and it is still happening.

Thanks for any advice,

Colin

Ok, I finally resolved this issue. After a lot of work trying various
solutions such as using VBA and temporary tables etc., I resolved it by
accident. I refreshed the ODBC link for the table the form was using,
and it worked immediately.

I am quite confident that this was the answer, because I had an
identical problem with another form, and so went straight to refresh
the link, and it worked again.

Oh well, you live and learn,

Thanks

Colin