473,883 Members | 1,598 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Nov 13 '05 #1
8 9251
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


Nov 13 '05 #2
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



Nov 13 '05 #3
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

immediatel y 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



Nov 13 '05 #4
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).

Nov 13 '05 #5
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?

Nov 13 '05 #6
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?

Nov 13 '05 #7
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
Nov 13 '05 #8
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
relationshi p 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


Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
6793
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
26
3846
by: David W. Fenton | last post by:
A client is panicking about their large Access application, which has been running smoothly with 100s of thousands of records for quite some time. They have a big project in the next year that will lead to a lot of use of the database and the adding of quite a lot of new data (though I can't conceive of them adding more than than 10s of thousands of records, which won't change the current performance profile at all). If there is a SQL...
6
10643
by: Larry Johnson | last post by:
I have two similar SQL Server databases each with a table named Payments. PaymentID is an identity field and the primary, unique, key in both tables. There is one other key but it is not unique. The same INSERT command works for one table but not the other. The error is "...didn't add 1 record due to key violations". Since the tables apper to be identical as far as I can tell, I suspect the error message really means something else. ...
0
6901
by: crypto_solid via AccessMonster.com | last post by:
I have been using a SQL database with a VB5 frontend for about 5 years. Works well. Unfortunately I don't have access to the source code. I was tasked with implementing a "job entry" application that will allow a restricted list of users to enter jobs in the database. We do not want these users to be able to access all the data. I created an application in Access 2002 that performs this function seemingly quite well. However we now...
2
9490
by: Charles Wilt | last post by:
I have a IBM iSeries (aka AS-400) running v5r3 of OS/400 that I access via a linked server from SQL Server 2000. The following select works fine: select * from prod400db.test.meldbf.InventoryHistory However, this insert statement fails: insert into prod400db.TEST.MELDBF.InventoryHistory
2
3560
by: Jim Devenish | last post by:
I am developing an Access front-end linked to SQL Server back-end. In the triggers I raise an error as in: RAISERROR 44447 'The record cannot be changed. RI rules require a related record in table "Locations"' However all I get in Access when this trigger raises its error is: "ODBC --insert on a linked table 'Tasks' failed.
20
37926
by: TC | last post by:
I need an automated procedure to copy data from an Access table to a SQL Server table. Speed is important. What is the recommended technique? I can export the data from Access, copy it via FTP, then import it into SQL Server. I've tried that, and the speed is acceptable. It is an ugly solution, however, and I expect to find a better one -- preferably a solution better integrated with the Access RDBMS. I've tried using an ODBC...
2
6984
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of attending interviews. If you own a company best way to judge if the candidate is worth of it. http://www.questpond.com/InterviewRatingSheet.zip
4
14153
by: Tonio Tanzi | last post by:
I have the following problem in a Win 2000 Server + SQL Server 2000 environment and I hope somewhat can help me to resolve it (after many days of useless attempts I am desperate). In my database I have two table: - master(id, field1, field2, ...) - detail(id0, id, progr, data, sede, esecutori, brani_autori) in a master-detail relation with "id" as foreign key. The fields of the "detail" table are:
0
9779
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11114
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9563
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4605
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.