SQL Server Linked Table Insert Record Problem

[Bri]

Greetings,

I'm having a very strange problem in an AC97 MDB with ODBC Linked tables
to SQL Server 7. The table has an Identity field and a Timestamp field.
The problem is that when a new record is entered, either from a form or
from the table view of the table, when the record gets saved it
immediately displays #DELETED# in all of the fields. However, if I close
the form or table view and reopen the record has in fact been inserted.
The identity field contains the next value in the sequence.

I originally suspected that the record was being deleted by a trigger
(The table is upsized from an Access backend and I picked Triggers for
the Relationships) but I tried disabling the triggers and the symptoms
didn't change.

Generally, if there is something wrong with a linked table you get the
generic error message, "ODBC Call Failed", that doesn't tell you much.
With this, there is no error message.

I've upsized several applications with dozens of tables and this is the
first time I've seen anything like this. Anyone have any ideas on where
to look next?

Thanks,
Bri

[Steve Jorgensen]

Well, triggers were a good place to look. If a trigger inserts a row into
another table that has an IDENTITY column, you'll get back the @@IDENTITY
value for that insert, not the initial insert you were trying to do, and that
-will- confuse ODBC. Other than that, I don't know of any specific cause for
the symptom you're having when the table has a TIMESTAMP column.

Oh, just thought of one... Did you change the structure of the table, and not
refresh the link in Access? Access will not know about structural changes
until you refresh the link. Other than that, make sure JET and the ODBC
driver are up to date.

On Thu, 25 Nov 2004 22:09:06 GMT, Bri <no*@here.com> wrote:

Greetings,

I'm having a very strange problem in an AC97 MDB with ODBC Linked tables
to SQL Server 7. The table has an Identity field and a Timestamp field.
The problem is that when a new record is entered, either from a form or
from the table view of the table, when the record gets saved it
immediately displays #DELETED# in all of the fields. However, if I close
the form or table view and reopen the record has in fact been inserted.
The identity field contains the next value in the sequence.

I originally suspected that the record was being deleted by a trigger
(The table is upsized from an Access backend and I picked Triggers for
the Relationships) but I tried disabling the triggers and the symptoms
didn't change.

Generally, if there is something wrong with a linked table you get the
generic error message, "ODBC Call Failed", that doesn't tell you much.
With this, there is no error message.

I've upsized several applications with dozens of tables and this is the
first time I've seen anything like this. Anyone have any ideas on where
to look next?

Thanks,
Bri

[Bri]

Steve,

Thanks for your reply.

As I said, I tried commenting out all of the Insert triggers and it
still did the same thing. I also tried creating a new ODBC Link after I
tried a relink and its still the same.

Also, when adding a new record from Enterprise Manager everything works
as expected.

The triggers are the ones that the upsizing wizard put in for RI to make
sure that values entered into certain fields exist in the Lookup Table
that the field is related to.

Is there a way to get at the @@IDENTITY value. I am just entering the
record in a bound form.

Thanks
Bri

Steve Jorgensen wrote:

Well, triggers were a good place to look. If a trigger inserts a row into
another table that has an IDENTITY column, you'll get back the @@IDENTITY
value for that insert, not the initial insert you were trying to do, and that
-will- confuse ODBC. Other than that, I don't know of any specific cause for
the symptom you're having when the table has a TIMESTAMP column.

Oh, just thought of one... Did you change the structure of the table, and not
refresh the link in Access? Access will not know about structural changes
until you refresh the link. Other than that, make sure JET and the ODBC
driver are up to date.

On Thu, 25 Nov 2004 22:09:06 GMT, Bri <no*@here.com> wrote:

Greetings,

I'm having a very strange problem in an AC97 MDB with ODBC Linked tables
to SQL Server 7. The table has an Identity field and a Timestamp field.
The problem is that when a new record is entered, either from a form or

from the table view of the table, when the record gets saved it

immediately displays #DELETED# in all of the fields. However, if I close
the form or table view and reopen the record has in fact been inserted.
The identity field contains the next value in the sequence.

I originally suspected that the record was being deleted by a trigger
(The table is upsized from an Access backend and I picked Triggers for
the Relationships) but I tried disabling the triggers and the symptoms
didn't change.

Generally, if there is something wrong with a linked table you get the
generic error message, "ODBC Call Failed", that doesn't tell you much.
With this, there is no error message.

I've upsized several applications with dozens of tables and this is the
first time I've seen anything like this. Anyone have any ideas on where
to look next?

Thanks,
Bri

[Steve Jorgensen]

On Fri, 26 Nov 2004 17:02:21 GMT, Bri <no*@here.com> wrote:

Steve,

Thanks for your reply.

As I said, I tried commenting out all of the Insert triggers and it
still did the same thing. I also tried creating a new ODBC Link after I
tried a relink and its still the same.

Also, when adding a new record from Enterprise Manager everything works
as expected.

The triggers are the ones that the upsizing wizard put in for RI to make
sure that values entered into certain fields exist in the Lookup Table
that the field is related to.

Is there a way to get at the @@IDENTITY value. I am just entering the
record in a bound form.
No, but you're not supposed to have to. Access should get back the @@IDENTITY
value from the insert, and identify the new record with that value. I guess
we know from the symptom that somehow, one of the following things is
occurring:

1. Access is not getting the the @@IDENTITY value back like it's supposed to
(e.g. a driver problem).
2. Access is getting back @@IDENTITY for a different insert (e.g., there's
still a trigger hiding somwehere).

Thanks
Bri

Steve Jorgensen wrote:

Well, triggers were a good place to look. If a trigger inserts a row into
another table that has an IDENTITY column, you'll get back the @@IDENTITY
value for that insert, not the initial insert you were trying to do, and that
-will- confuse ODBC. Other than that, I don't know of any specific cause for
the symptom you're having when the table has a TIMESTAMP column.

Oh, just thought of one... Did you change the structure of the table, and not
refresh the link in Access? Access will not know about structural changes
until you refresh the link. Other than that, make sure JET and the ODBC
driver are up to date.

On Thu, 25 Nov 2004 22:09:06 GMT, Bri <no*@here.com> wrote:

Greetings,

I'm having a very strange problem in an AC97 MDB with ODBC Linked tables
to SQL Server 7. The table has an Identity field and a Timestamp field.
The problem is that when a new record is entered, either from a form or

from the table view of the table, when the record gets saved it

immediately displays #DELETED# in all of the fields. However, if I close
the form or table view and reopen the record has in fact been inserted.
The identity field contains the next value in the sequence.

I originally suspected that the record was being deleted by a trigger
(The table is upsized from an Access backend and I picked Triggers for
the Relationships) but I tried disabling the triggers and the symptoms
didn't change.

Generally, if there is something wrong with a linked table you get the
generic error message, "ODBC Call Failed", that doesn't tell you much.
With this, there is no error message.

I've upsized several applications with dozens of tables and this is the
first time I've seen anything like this. Anyone have any ideas on where
to look next?

Thanks,
Bri

[Bri]

Steve,

OK, here is some more info:

- I tried it on Access 2000 with SQL 2000 (original was on AC97 and SQL
7) and got the same results.

- Info about the Table:
- Field1 is int and is foreign key to parent table
- Field2 is int and Identity
- Field1 and Field2 are the Primary Key
- Field3 and Field4 are int and are foreign keys to lookup tables
(the triggers, when I have them active, make these required fields, but
I get the #DELETED# whether these triggers are on or not)
- there are several other fields some of which are foreign keys to
lookup tables, others are data of various types
- there is a timestamp field

- If I enter any of the fields in addition to Fields 1-4 then I do not
get the #DELETED# but since all of these fields are optional in the app
I can't be sure any of them will be entered at the time the record is
created (Inserted).

I'm not sure what else I can try.

Bri

Steve Jorgensen wrote:

On Fri, 26 Nov 2004 17:02:21 GMT, Bri <no*@here.com> wrote:

Steve,

Thanks for your reply.

As I said, I tried commenting out all of the Insert triggers and it
still did the same thing. I also tried creating a new ODBC Link after I
tried a relink and its still the same.

Also, when adding a new record from Enterprise Manager everything works
as expected.

The triggers are the ones that the upsizing wizard put in for RI to make
sure that values entered into certain fields exist in the Lookup Table
that the field is related to.

Is there a way to get at the @@IDENTITY value. I am just entering the
record in a bound form.

No, but you're not supposed to have to. Access should get back the @@IDENTITY
value from the insert, and identify the new record with that value. I guess
we know from the symptom that somehow, one of the following things is
occurring:

1. Access is not getting the the @@IDENTITY value back like it's supposed to
(e.g. a driver problem).
2. Access is getting back @@IDENTITY for a different insert (e.g., there's
still a trigger hiding somwehere).

[Steve Jorgensen]

On Mon, 29 Nov 2004 00:05:15 GMT, Bri <no*@here.com> wrote:

Steve,

OK, here is some more info:

- I tried it on Access 2000 with SQL 2000 (original was on AC97 and SQL
7) and got the same results.

- Info about the Table:
- Field1 is int and is foreign key to parent table
- Field2 is int and Identity
- Field1 and Field2 are the Primary Key Aha! - Field3 and Field4 are int and are foreign keys to lookup tables

You are using an IDENTITY field as -part- of the key, not the whole key. I've
never seen anyone try that, I'm not sure why you would, and I wouldn't expect
DAO or ADO to be smart enough to know how to handle it.

Since the IDENTITY field must be unique all by itelf, why are you including
another field as part of the primary key?

[Bri]

Steve,

My thinking was that since Field1 is the PK of the Parent table that it
should be included in the Child PK. Otherwise, how do I create a
relationship between these tables?

OK, I changed the PK to be only the Identity field and the #DELETED#
problem went away. I now have the new problem of having no relationship
between the Parent and Child table. I suppose I can create a Trigger to
enforce this, but I wonder if that will effect performance since the
relationship is no longer indexed. Or will a separate index on the
Parent foreign key be sufficient? Or am I thinking too much in terms of
what is needed in a relationship in an Access BE? I'd rather have the
Engine level enforce the relationship than have to 'roll my own', but if
it is the only way to do it in SQL Server, then that's what I'll do.

Thanks,
Bri

Steve Jorgensen wrote:

On Mon, 29 Nov 2004 00:05:15 GMT, Bri <no*@here.com> wrote:

Steve,

OK, here is some more info:

- I tried it on Access 2000 with SQL 2000 (original was on AC97 and SQL
7) and got the same results.

- Info about the Table:
- Field1 is int and is foreign key to parent table
- Field2 is int and Identity
- Field1 and Field2 are the Primary Key

Aha!

- Field3 and Field4 are int and are foreign keys to lookup tables

You are using an IDENTITY field as -part- of the key, not the whole key. I've
never seen anyone try that, I'm not sure why you would, and I wouldn't expect
DAO or ADO to be smart enough to know how to handle it.

Since the IDENTITY field must be unique all by itelf, why are you including
another field as part of the primary key?

[Steve Jorgensen]

On Mon, 29 Nov 2004 18:49:14 GMT, Bri <no*@here.com> wrote:

Steve,

My thinking was that since Field1 is the PK of the Parent table that it
should be included in the Child PK. Otherwise, how do I create a
relationship between these tables?

That's simply not true. Often, when surrogate (e.g. IDENTITY or Autonumber)
keys are -not- used, a foreign key is used as part of the many-side table's
primary key because the key would not be unique otherwise, but that has
nothing to do with the requirements for a foreign key, it has to do with how
unique records are identified in the many-side table.

For instance, if I formulate invoices and lines as follows, I need the foreign
key in the invoice_line table because line number is not unique by itself...

invoice
* invoice_num
customer_name

invoice_line
* invoice_num
* line_num (1 to n) for each invoice.
line_descrip
line_qty
unit_cost

On the other hand, if I use surrogate keys as follows, I can create a foreign
key relationship just fine, and the foreign key field is not part of the
primary key of invoice_line...

invoice
* invoice_id (IDENTITY)
invoice_num
customer_name

invoice_line
* invoice_line_id
invoice_id (fk)
line_descrip
line_qty
unit_cost

[Bri]

Steve,

Now that you point it out, I realize I knew that all along, but had
somehow got caught up in the habit of including the FK as I usually do
parent-child relationships using the first method you showed. In this
case, there is a unique field in the child table that would have made a
perfect PK if it wasn't for the small detail of it not being determined
until some time after the initial record is created. That's why I
created the Identity field.

Thanks for all your help here. The symptoms certainly didn't make the
cause of the problem obvious.

Bri

Steve Jorgensen wrote:

On Mon, 29 Nov 2004 18:49:14 GMT, Bri <no*@here.com> wrote:

Steve,

My thinking was that since Field1 is the PK of the Parent table that it
should be included in the Child PK. Otherwise, how do I create a
relationship between these tables?

That's simply not true. Often, when surrogate (e.g. IDENTITY or Autonumber)
keys are -not- used, a foreign key is used as part of the many-side table's
primary key because the key would not be unique otherwise, but that has
nothing to do with the requirements for a foreign key, it has to do with how
unique records are identified in the many-side table.

For instance, if I formulate invoices and lines as follows, I need the foreign
key in the invoice_line table because line number is not unique by itself...

invoice
* invoice_num
customer_name

invoice_line
* invoice_num
* line_num (1 to n) for each invoice.
line_descrip
line_qty
unit_cost

On the other hand, if I use surrogate keys as follows, I can create a foreign
key relationship just fine, and the foreign key field is not part of the
primary key of invoice_line...

invoice
* invoice_id (IDENTITY)
invoice_num
customer_name

invoice_line
* invoice_line_id
invoice_id (fk)
line_descrip
line_qty
unit_cost