Update Query Requires Source With Pk

doco

doco

Power User
Local time
Today, 13:30
Joined
Feb 14, 2007
Messages
482
Just a question out of extreme frustration in trying to deal with MS's toy database. :mad:

Apparently, if using a table to update another table and the source table does not have a primary key then this produces the infamous "Must use an updateable query" error. Was MS on a crack break when they came up with that nonsense?

Just wanted to vent...
 
Um, I really should point out that it's far easier to be a backside driver than it is to actually program. I'd rather assume they had a good reasons for not providing that functionality.

Besides, it's a easy fix anyway; just add a column with unique number and call it PK prior to appending. I'm even sure you don't have to actually add a column to the table; just do so with a query and use that as the source instead.
 
Banana said:
Um, I really should point out that it's far easier to be a backside driver than it is to actually program. I'd rather assume they had a good reasons for not providing that functionality.

Besides, it's a easy fix anyway; just add a column with unique number and call it PK prior to appending. I'm even sure you don't have to actually add a column to the table; just do so with a query and use that as the source instead.
Click to expand...

Yes there are a number of fixes. But just making a query of the table is not one. It still wants a PK. Anyway, just one more in a laundry list of extra steps Access requires. I suppose what is really torquing me is the fact the BE I am linking tables has the (vast) majority of its tables with no PK.

Thanks for the responses, I honestly thought the thread would be deleted by admin. I appologize for the blowup.:)
 
Okay, now I have to ask. Why in the world there aren't any PK in the backend? If it was one or two small throwaway tables, fine. But vast majority?!?!

I'd place the blame upon the feet of whoever developed that backend before blaming MS...

BTW, another fix though I'd call it more of a hack but at least there's no new columns: Declare all columns in the table to be a compound primary key. Of course that'd only work as long it wasn't a junction table or something like that.
 
Companies hire people who have no clue what they're doing to design and implement their databases. Usually those people are better sales people than they are engineers. You know the type...they post here asking a question they shouldn't have to ask with exactly 1 post, insult you while you're trying to help them figure out why they make such poor design decisions, and when their problem is solved they disappear.

Then when the system doesn't work, they contract with somebody like, well us, to clean it up.

Sounds like that's the phase in the development cycle that doco finds himself in.
 
georgedwilkinson said:
Companies hire people who have no clue what they're doing to design and implement their databases. Usually those people are better sales people than they are engineers. You know the type...they post here asking a question they shouldn't have to ask with exactly 1 post, insult you while you're trying to help them figure out why they make such poor design decisions, and when their problem is solved they disappear.

Then when the system doesn't work, they contract with somebody like, well us, to clean it up.

Sounds like that's the phase in the development cycle that doco finds himself in.
Click to expand...

Actually,there are a couple hundred core components in the BE. There is a main table that has a 'key' that is unique and that field is found in the majority of tables as a foreign key of sorts. But I have found no table yet that is being identified as having a bona fide PK - at least that Access would recognize as Primary. The database works well just frustrating being forced to use Access over the top as a reporting tool and run into those 'surprises'. I am not an expert by anyone's standards - just trying to be an efficient end user :cool:
 
I should clarify that when I said 'a main table' I meant a main table to the majority of reports that I need to generate. Of course there are other tables that have there own unique[ness]. Its just not clear how integrity is being enforced ... at least to me.
 
I wonder if this is actually an issue of you not having full privileges to the backend and thus cannot see primary keys? Does someone else manage the backend, if so, you may want to sit down and talk with the DBA about this.

If this isn't the case, I have to tell you- this got to be a seriously whacked up database if there's no primary keys for vast majority table. I understand it works well, but this isn't indicative of whether it works correctly. Kinda like saying that a car works well if you can roll it down the hill. ;)
 
I wonder if this is actually an issue of you not having full privileges to the backend and thus cannot see primary keys? Does someone else manage the backend, if so, you may want to sit down and talk with the DBA about this.
Click to expand...

Hmmmm.... That may very well be the case - I do not have full permissions to the BE and we have hired a DBA to manage the legacy and both converted db's. I will have that discussion first thing tomorrow morning.

The database is sound I am certain. It is widely used across North America by hundreds of clients - so I would not expect it to be otherwise. There is a companion database (both are SQL Server) that I can 'see' the PK's redily and if I have an update problem with those its usually because I have a hinky join or return multiple records or something like that.

So, thanks for the heads up. I would never have considered permission as a possibility. Sure would be kewl if it was as simple a matter as that :D
 
Just received this response from our DBA
I have verified that (NameOfDatabase) has NO primary key
Click to expand...

Took out the name of the database.
 
georgedwilkinson said:
If I were him, I'd be fuming.
Click to expand...

Quite agreed.

If I may ask, exactly what is this database? What kind of data is it supposed to store?

Can I also ask what kind of backend this is?

I ask because I'm genuinely curious and trying to imagine what kind of applications where primary keys would be liability. I know for fact that MySQL's default engine, MyISAM doesn't support transactions, which makes perfect sense for mostly-read or read-only database for web applications, but no primary keys?
 
Banana said:
Quite agreed.

If I may ask, exactly what is this database? What kind of data is it supposed to store?

Can I also ask what kind of backend this is?

I ask because I'm genuinely curious and trying to imagine what kind of applications where primary keys would be liability. I know for fact that MySQL's default engine, MyISAM doesn't support transactions, which makes perfect sense for mostly-read or read-only database for web applications, but no primary keys?
Click to expand...

It is a well known widely used Computer Assisted Mass Appraisal program/database. I will not mention the name for obvious reasons. The BE is SQL Server. The program was originally developed long before SQL. And I am being told now, it was common practice (then) to handle integrity by other means - programming? Now, the fields that would be on a cursory glance easily recognized as PK are indeed unique. But when linking to tables in the BE, then reviewing them in design view - Access does not recognize them as PK. And have confirmation there are indeed none.

What is hard for me to imagine now is continuing to build upon a 25+ year old archetecture unabated. But like I said the db works and the front end is excellent as well. Does make one wonder though how much of my maintenance fee is devoted to maintenance of now long deprecated archetecture.

I have issued a trouble ticket with the company and it has started a virtual firestorm of emails, etc. :eek:
 
Very interesting.

What is even more curiouser is that I'd think it easy to add primary keys even if they weren't in the original design, especially as you said, there are candidate keys that can be used as primary keys, so getting the child tables to correctly reference the parent table should be easier.

Ah, the joys of legacy applications!


Also, not sure if I've already mentioned, but you could just use what you saw to be good PK and declare it to Access yourself. I just checked and found I could set primary keys even for linked tables.

Of course, if you re-link now & then, you'll have to repeat the process.
 
There are several workaround options but it just seems really wierd that Boyce/Codd is foreign to the BE here.

Thanks for the pointers...
 
You did say it was 25 years old, no? It probably originated on a different database (I think it was hierarchical back then); relational database was just entering picture then, I believe...

Still, one would think it'd have been updated....
 
I am certain the concept preceeded modern computers and RDBMS by at least a decade. The company was founded and traded publicly since 1969.

Today, I directed the DBA to begin adding constraints to various unique keys in various of the core components. At least those commonly used for reporting in a test db environment. We will begin testing to see what changes beside SPEEEEEEED. Preliminary tests are promising.
 
You must log in or register to reply here.

Similar threads

R
Update Query with a join to another query - Help
Replies
6
Views
457
Gasman
Gasman
C
Update Query using SWITCH function
Replies
13
Views
633
GPGeorge
GPGeorge
K
Where False VS PK=0
Replies
7
Views
477
KitaYama
K
southwestgooner
Help!!! Update Query to update fields in table???
Replies
2
Views
578
Pat Hartman
P
S
Updating MakeTable Queries for a new version of the database
Replies
9
Views
947
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom