Avatar of Kerry Wilson
Kerry Wilson
Flag for United States of America asked on

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

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?
Microsoft AccessMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
ggzfab

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ggzfab

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
kmslogic

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Kerry Wilson

ASKER
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.
Kerry Wilson

ASKER
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 Wilson

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ggzfab

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.