Non updatable query & duped IDs

[Guest]

There is some VBA code (Access2k, Win2k, ODBC linked SQL
Server tables) that has run perfectly for a weekly process
6 times. The 7th run had an error with the following
message: "Runtime error 3073, Operation must use an
updatable query"

The code runs if tables are imported into Access on a
local machine and I found that one of the lookup tables
was the problem. The table had an identity field but no
primary key and someone added records that were duplicates
in the identity field. No updates were being made to this
table, only lookups and the code got wrong answers and
even a simple query would not run properly even though the
identity field was not used as one of the criteria.

Everything is fixed and works again but I find this
troubling, so I ask the following questions:

1) How can a query return records that do not match the
specified criteria under any circumstances? (Even duped
ids did not match)

2) Why would the code run with native access tables but
not SQL Server links?

3) Why is a non-duplicated id field required to do lookups?

4) This exasperating experience has taught me that all SQL
Server tables need a non-duplicated id field. Did I learn
the right lesson? What else should I learn from this
experience?


Thanks,

al

 

[Albert D. Kallal]

1) How can a query return records that do not match the
specified criteria under any circumstances? (Even duped
ids did not match)

Did you save a copy of this sql statement, and the tables? Can your export
out the 1 or 2 records to a separate mdb file, and have the simple sql
fail?. If you can create a simple two table mdb file with a few records in
the two tables, and repeat the sql problem, then likely one of us here can
look into what your problem was. If you have those two simple tables, and 2
or 3 records in each table, and the one sql statement that does not
work...email it to me, and I take a look at it.

2) Why would the code run with native access tables but
not SQL Server links?

Gee, sql server is a complete different platform, completely different
architecture, and a complete different version of sql. I mean, why does
water taste different then lemon juice. (I mean, those both have water..but
they are VERY different).

I am not trying to be sarcastic here, but that is a very difficult question
for me (or anyone) to answer. Unless some more details are proved here, my
guess is NO better then anyone else's. However, I do want to help you. Here
is a few things:

* Perhaps some RI (relations) constrains are different on the server
* Perhaps some fields have their required settings on the server set
different
* Perhaps allow nulls, or not allow nulls is set different on the server
* Perhaps some field types are different lengths on the server
* Perahps some field types are differnt
* Perhaps some triggers, or update code runs on the server when records
are updated
* Perhaps some table permissions or security settings are different
(some tables might not have the same rights).

I could probably wrote a lot more reasons here. There is a lot of things
that can be checked. Also, did you use ADO, or dao to update the data? Or,
perahps you used just linked tables? .

3) Why is a non-duplicated id field required to do lookups?

Because you can't have a field return more then one value..can you? (ask
your self what sql server does in this case?). I not 100% clear on what you
mean by the above. For sure, the lookup MUST resolve to ONE value in the
other table.

Further, the above statement is not 100% true, and this really depends on
how you are making the lookup. If you are using sql server, or most systems,
then you can fetch values from other tables using sql (and you don't have
this problem of duplicates). In fact, you can also do this in ms-access *IF*
you use sql.

However, if you are using the lookup feature in ms-access tables (which
don't exist in Oracle, or Sql-server, or MySql, or Sybase, or PostGres),
then you need to read the following, and pay close attention to #2:

http://www.mvps.org/access/tencommandments.htm

I don't necessary buy into the above statement that you should not use
lookups, but Microsoft's own sql server, the competition like Oracle, and
all those other mainstream database systems do NOT have this lookup feature.
Thus the industry as a whole does get a long without this feature. For some,
the lookup is a very useful feature, and if it works for them, then who am I
to say otherwise? So, I avoid the feature, but others, successfully use and
enjoy the feature. After all, it was put in the system to make things easy
for people, and if they like the feature..then that is ok!

4) This exasperating experience has taught me that all SQL
Server tables need a non-duplicated id field. Did I learn
the right lesson? What else should I learn from this
experience?

To effectively update server based tables, you also need to expose a
timestamp field to ms-access. So, having both a primary key AND also the
timestamp field exposed to ms-access is a good idea. Sql server uses these
timestamp fields, and so does ms-access when connection to a server based
system.. Another good things to learn is in MANY cases, you are better to
create a view on the sql server side, and use that as the linked table, then
trying to have ms-access join together two tables on the client side (this
is especially the case if you just use general sql on the two linked
tables).

 

[Albert D. Kallal]

Also, for problems with updates, check the followign on null bits:

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
Server Table

http://support.microsoft.com/?id=280730