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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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.pr
WHERE (((dbo_in_house_rejections
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 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.pr
WHERE (((dbo_in_house_rejections
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.
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.
When you run this, Access will ask for the ODBC connection to be used.
ASKER
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.