Using Access ODBC connection to update SQL database records when there is no key field

Kerry Wilson
Kerry Wilson used Ask the Experts™
on
I have a SQL table in which I need to update selected records.  But, by design, there is no key field in this database and it would be difficult to establish a key field.  Since there is no key field I have noticed the Access table listing has table edits (changes and deletions) greyed out.  So, there is no way to issue an Update command.

However, I know it is possible to update or delete the record(s) because I can write and execute a query using the SQL Query Analyzer or the Enterprise Manager.  

So, my question is, is there some control or property I can set that will enable Access to update or delete records using an ODBC connection when there is no key field?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You could create a Stored Procedure in the database and execute that, or create a PassThrough query to perform an Update, however without a unique index you'll need to solve the identification of the record(s) for the Update one way or another...
Commented:
Well first you should probably add a key field to your table--just an autonumbered integer will work great and is what most people use.  Call it ID and you'll be in good company.  Don't get hung up on trying to turn a specific field or group of fields into a key.

Having said that you should still be able to do updates and insertions at least via SQL and regular Access queries even if it won't let you edit and insert by opening (double clicking on) the table itself.
Kerry WilsonNetwork Administrator

Author

Commented:
ggzfab:
The identification of the record(s) is not an issue...if more than one record qualifies, the same update should be performed on all of them.  I have not written any Stored Procedures but I am aware of their existance and had wondered if they can be executed from Access.  How can Access command execution of a Stored Procedure?  What is a "PassThrough query."

Once a Stored Procedure has been created, what is the best way to execute it outside of any programmming?  I want various users (managers) to be able to execute the SPs from their desktops via Access or a batch file.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Kerry WilsonNetwork Administrator

Author

Commented:
kmslogic:
I appreciate the suggestion but creating a key field would be difficult.  There is another table that is identical in column layout to this one with the exception of the key field.  Our contract programmer, to save time and money and with my blessing, just used a "insert into table2 select * from table1 where blahblahblah" to add the records to table2 so the column layout has to be identical between the two tables or the insert statement will fail.  

There is a key field in table1 (serialnumber).  But the serialnumber column in table2 is not a key field because, by design, the same serialnumber record can appear more than once in table2.

Would it be likely to break anything if I added an autonumbered integer key field to both tables?   The programmer has moved out of state and it would be a major undertaking if all of the programs had to be recompiled and deployed.

The Access update queries should still work even though I can't open the table in the edit mode by double-clicking on the linked table itself?  I didn't know that.  That is very helpful...if it works!   :-)

You also commented about not getting "hung up" on creating a group of fields to create a key.  I had thought this approach might be a possible solution because even though the same table1 serial number record can appear more than once in table2, each appearance should have a different date_time_rejected value.  So, a key field could be created by combining the serialnumber field with the date_time_rejected field.  What did you mean by "hung up"?  Not to spend a lot of time and thought on it or that it wouldn't work?
Kerry WilsonNetwork Administrator

Author

Commented:
kmslogic:
I just wrote an update query in Access and ran it.  There are six qualifying records to update.  I received an error message "Operation must use an updatable query."

Here is the query:
UPDATE dbo_in_house_rejections SET dbo_in_house_rejections.process_field = 'B'
WHERE (((dbo_in_house_rejections.current_serial_number)='A671919'));

So, it appears that Access can not run an update query against this table.  I know I have update permissions set for the table and I have verified the update is properly executed with SQL Query Analyzer.

Commented:
You could create a PassThrough query, by creating a new query without table selection. Next  select from the Query menu the SQL-Specific/PassThrough option and paste your query.
When you run this, Access will ask for the ODBC connection to be used.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial