Using ODBC Linked Tables

[Steven]

Where do I post ODBC questions?

I can insert a new row, but cannot modify an existing row, or delete a row.

It's Access 2003 frontend with linked SQL Server 2000 tables. And it's just
1 out of 20 tables that has the problem. The table has a PK index and its
rows can be modified using Enterprise Mgr or QA, but cannot be modified in
Access.

In the SQL table, I tried dropping and recreating the index, tried added a
timestamp column, and tried adding a unique ID, all in addition to the
existing PK, but no luck (relinked the table with each of these attempts).

Successfully deleting the child rows in the child tables, but still can't
delete the primary row in the parent table.

I tried enabling an ODBC trace, but the log always turns up empty. Is there
some trick to getting the trace to work?

Linked the parent table to a brand new MDB, but have the same problem, even
when recreating a new System DSN and using a SQL or Windows account that has
full permissions to the SQL database. Query Anlayzer is able to delete the
parent rows just fine.

There is no error message. When you delete the row in the datasheet view of
the linked table within Access, the row appears to be removed. But if you
close and reopen the datasheet view, the row is back. Absolutely no errors,
it just fails silently.

I'm really stuck.

 

[Rick Brandt]

Where do I post ODBC questions?

I can insert a new row, but cannot modify an existing row, or delete
a row.

It's Access 2003 frontend with linked SQL Server 2000 tables. And
it's just 1 out of 20 tables that has the problem. The table has a PK
index and its rows can be modified using Enterprise Mgr or QA, but
cannot be modified in Access.

In the SQL table, I tried dropping and recreating the index, tried
added a timestamp column, and tried adding a unique ID, all in
addition to the existing PK, but no luck (relinked the table with
each of these attempts).

Successfully deleting the child rows in the child tables, but still
can't delete the primary row in the parent table.

I tried enabling an ODBC trace, but the log always turns up empty. Is
there some trick to getting the trace to work?

Linked the parent table to a brand new MDB, but have the same
problem, even when recreating a new System DSN and using a SQL or
Windows account that has full permissions to the SQL database. Query
Anlayzer is able to delete the parent rows just fine.

There is no error message. When you delete the row in the datasheet
view of the linked table within Access, the row appears to be
removed. But if you close and reopen the datasheet view, the row is
back. Absolutely no errors, it just fails silently.

I'm really stuck.

What is the DataType of the PK on the server? My guess is it's a type that
Access has no exact equivelant for and that is causing issues. If Access
interprets the value of the PK slightly differently (rounding for example)
then the DELETE statement sent to the server will delete a non-existent row
because the value in its WHERE clause will be incorrect.

DateTimes, BigIntegers, and a few others will do this.

 

[a a r o n _ k e m p f]

yah.. how about 'instead of mapping 12 different flavors of the same
datatypes' why don't you just go native and use ADP

then things 'just work'.
and if they don't you always have the freedom to write triggers

One single connection. All databases on the same server are easy to
chat with-- you all know that

 

[Steven]

Whenever I've had a problem like this in the past, I've just always added a
column of type TimeStamp to the SQL table, relinked the table on the
Access-side, and I was good-to-go.

The PK on the SQL-side is/was an 10-char varchar, which Access associated
with a Text type. But I had experimented with other data-types on the
SQL-side as well, relinking the table each time, but the problem still
occurred.

I tried 5 different PK data-types on the SQL-side (timestamp, int, bigint,
smalldatetime, and GUID), relinking the table within Access each time. Design
View of the linked table would reflect my changes each time. For example, the
int PK on the SQL-side would be interpreted as an AutoNumber PK on the linked
table Access-side. It does not matter which PK data-type I specify on the
SQL-side, I still cannot modify or delete rows within the linked table.

This morning I tried dropping all relationships between this table and any
other tables, just to make absolutely sure this wasn't some sort of
parent-child issue.

At this point, I think I'm just going to try pass-thru queries to SQL
Server, since I know I can modify/delete data from Ent Mgr or QA.

This is a huge Access MDB that someone else developed years ago and I have
been asked to come in and make some changes. Using an ADP would be nice, but
this MDB has over 165 forms, and each form has 1000 to 4000 lines of code,
half of which is DAO code. So if the MDB were converted to an ADP, all that
DAO code would require a rewrite to ADO. Might as well go with .NET in my
humble opinion.

If you have any other ideas concerning this problem, please let me know.
Otherwise, thanks for your help.

 

[Stefan Hoffmann]

hi Steven,

In the SQL table, [..] tried added a timestamp column [..]

Of SQL type TIMESTAMP?

There is no error message. When you delete the row in the datasheet view of
the linked table within Access, the row appears to be removed. But if you
close and reopen the datasheet view, the row is back. Absolutely no errors,
it just fails silently.

Run SQL Profiler: What SQL command does Jet run against your SQL Server
when you delete a row?

btw, what MDAC/Jet do you use? Maybe a reinstall does work.


mfG
--> stefan <--

 

[a a r o n _ k e m p f]

I don't think that your super-duper app is really too complex to move
to ADP.

Sorry.

DAO is a pig; it always has been. You should have moved away from it
when MS reccomended.

I guess that is what you get for listening to the yahoos in this group

Passthrough queries are nice-- they BEGIN to give you some of the
performance that you deserve if you use SQL Server.

but linked tables are inherently dog slow at anything that they do.

I just don't understand. Use integers for keys. Case closed. Sure,
use a timestamp.. but also use a handful of small triggers (where
appropriate).

Native SQL Server just sounds like a whole lot less of a hassle for
you.

Whenever I've had a problem like this in the past, I've just always addeda
column of type TimeStamp to the SQL table, relinked the table on the
Access-side, and I was good-to-go.

The PK on the SQL-side is/was an 10-char varchar, which Access associated
with a Text type. But I had experimented with other data-types on the
SQL-side as well, relinking the table each time, but the problem still
occurred.

I tried 5 different PK data-types on the SQL-side (timestamp, int, bigint,
smalldatetime, and GUID), relinking the table within Access each time. Design
View of the linked table would reflect my changes each time. For example,the
int PK on the SQL-side would be interpreted as an AutoNumber PK on the linked
table Access-side. It does not matter which PK data-type I specify on the
SQL-side, I still cannot modify or delete rows within the linked table.

This morning I tried dropping all relationships between this table and any
other tables, just to make absolutely sure this wasn't some sort of
parent-child issue.

At this point, I think I'm just going to try pass-thru queries to SQL
Server, since I know I can modify/delete data from Ent Mgr or QA.

This is a huge Access MDB that someone else developed years ago and I have
been asked to come in and make some changes. Using an ADP would be nice, but
this MDB has over 165 forms, and each form has 1000 to 4000 lines of code,
half of which is DAO code. So if the MDB were converted to an ADP, all that
DAO code would require a rewrite to ADO. Might as well go with .NET in my
humble opinion.

If you have any other ideas concerning this problem, please let me know.
Otherwise, thanks for your help.

What is the DataType of the PK on the server?  My guess is it's a type that
Access has no exact equivelant for and that is causing issues.  If Access
interprets the value of the PK slightly differently (rounding for example)
then the DELETE statement sent to the server will delete a non-existentrow
because the value in its WHERE clause will be incorrect.

DateTimes, BigIntegers, and a few others will do this.

 

[a a r o n _ k e m p f]

you should email me (e-mail address removed) or (e-mail address removed)
i'd love to help you move it to ADP

Whenever I've had a problem like this in the past, I've just always addeda
column of type TimeStamp to the SQL table, relinked the table on the
Access-side, and I was good-to-go.

The PK on the SQL-side is/was an 10-char varchar, which Access associated
with a Text type. But I had experimented with other data-types on the
SQL-side as well, relinking the table each time, but the problem still
occurred.

I tried 5 different PK data-types on the SQL-side (timestamp, int, bigint,
smalldatetime, and GUID), relinking the table within Access each time. Design
View of the linked table would reflect my changes each time. For example,the
int PK on the SQL-side would be interpreted as an AutoNumber PK on the linked
table Access-side. It does not matter which PK data-type I specify on the
SQL-side, I still cannot modify or delete rows within the linked table.

This morning I tried dropping all relationships between this table and any
other tables, just to make absolutely sure this wasn't some sort of
parent-child issue.

At this point, I think I'm just going to try pass-thru queries to SQL
Server, since I know I can modify/delete data from Ent Mgr or QA.

This is a huge Access MDB that someone else developed years ago and I have
been asked to come in and make some changes. Using an ADP would be nice, but
this MDB has over 165 forms, and each form has 1000 to 4000 lines of code,
half of which is DAO code. So if the MDB were converted to an ADP, all that
DAO code would require a rewrite to ADO. Might as well go with .NET in my
humble opinion.

If you have any other ideas concerning this problem, please let me know.
Otherwise, thanks for your help.

What is the DataType of the PK on the server?  My guess is it's a type that
Access has no exact equivelant for and that is causing issues.  If Access
interprets the value of the PK slightly differently (rounding for example)
then the DELETE statement sent to the server will delete a non-existentrow
because the value in its WHERE clause will be incorrect.

DateTimes, BigIntegers, and a few others will do this.

 

[Steven]

Thank you very much. SQL Profiler did help me find the cause of the problem.

Among various columns in the SQL Server table, there are 3 bit data-type
columns that default to Null. In the Access linked table, these 3 columns are
correctly interpreted as Yes/No data-types. But instead of Access displaying
these Yes/No columns as blank, they default to 0 (zero). The SQL Profiler
trace showed that ODBC was passing a DELETE statement back to SQL Server
WHERE these 3 Yes/No columns were equal to zero, b/c those were the values
displayed on the Access-side. SQL Server would then run the SQL statement and
not find a row where the 3 bit columns were equal to zero, and therefore, no
record was deleted. And Access doesn't know that there was no record deleted
on the SQL-side, and therefore no error would occur (it would fail silently).

I had no idea that when you choose to delete a row at the linked
table-level, it passes the value of every single column as criteria for the
Delete statement, instead of just passing the unique ID column. Well, I
shouldn't say "every single column", b/c it didn't pass the Memo column
(thank God). I would have thought that Jet and the ODBC drivers would have
been smart enough to send just the unique ID.

As for the ADP (not that I need to explain myself), but its not that I do
not want to make the conversion. The client and I have had this discussion.
They are looking for added features and it is not in there budget to pay
someone to make such a conversion. And if it were in there budget, I wouldn't
even recommend Access.

hi Steven,

In the SQL table, [..] tried added a timestamp column [..]

Of SQL type TIMESTAMP?

There is no error message. When you delete the row in the datasheet view of
the linked table within Access, the row appears to be removed. But if you
close and reopen the datasheet view, the row is back. Absolutely no errors,
it just fails silently.

Run SQL Profiler: What SQL command does Jet run against your SQL Server
when you delete a row?

btw, what MDAC/Jet do you use? Maybe a reinstall does work.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Steven,

As for the ADP (not that I need to explain myself), but its not that I do
not want to make the conversion.

This explains why the TIMESTAMP failed.

And this should describe your problem:

http://support.microsoft.com/kb/943967


mfG
--> stefan <--

 

[Tony Toews [MVP]]

Thank you very much. SQL Profiler did help me find the cause of the problem.

Among various columns in the SQL Server table, there are 3 bit data-type
columns that default to Null. In the Access linked table, these 3 columns are
correctly interpreted as Yes/No data-types. But instead of Access displaying
these Yes/No columns as blank, they default to 0 (zero). The SQL Profiler
trace showed that ODBC was passing a DELETE statement back to SQL Server
WHERE these 3 Yes/No columns were equal to zero, b/c those were the values
displayed on the Access-side. SQL Server would then run the SQL statement and
not find a row where the 3 bit columns were equal to zero, and therefore, no
record was deleted. And Access doesn't know that there was no record deleted
on the SQL-side, and therefore no error would occur (it would fail silently).

I had no idea that when you choose to delete a row at the linked
table-level,

it passes the value of every single column as criteria for the
Delete statement, instead of just passing the unique ID column. Well, I
shouldn't say "every single column", b/c it didn't pass the Memo column
(thank God). I would have thought that Jet and the ODBC drivers would have
been smart enough to send just the unique ID.

This is very interesting. Thanks for posting back your experience.

But how are you deleting the row? By pressing delete at the record?
Running some VBA code building a delete query?

FWIW I usually, although not always, delete a record by inserting some
code behind a command button.

strsql = "Delete * from table where <primarykeyfieldname> = " &
primarykeyfieldvalue
currentddb.execute strsql, dbfailonerror

As for the ADP (not that I need to explain myself), but its not that I do
not want to make the conversion. The client and I have had this discussion.
They are looking for added features and it is not in there budget to pay
someone to make such a conversion.

Quite understandable.

And if it were in there budget, I wouldn't
even recommend Access.

Why not? What would you recommend?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n _ k e m p f]

re: strsql = "Delete * from table where <primarykeyfieldname> = " &
primarykeyfieldvalue
currentddb.execute strsql, dbfailonerror


REAL PEOPLE USE SPROCS

Access doesn't support sprocs-- 20 steps to bind a sproc to a form?

move to ADP it is the best choice by far.
best performance
best maintenance
best auto-tuning

Jet is a waste of time, no doubt