• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

MS Access Update query not working

In MS Access 2010 I had this query working properly up to we change location of database, we move it on new server. I  Re-linked all tables and everything looks ok.
However when user tried to run this query from form command button I got this error.
Query is just updating one table. And it worked before. I checked all references and they ara ok.
Not sure what is happening.

Db.Execute, strSQL, dbSeeChanges

Run-Time Error ‘3073’
Operation must use an updatable query.
0
Taras
Asked:
Taras
  • 8
  • 5
  • 4
  • +2
3 Solutions
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
please post strSQL
0
 
Randy DownsOWNERCommented:
We need to see how strSQL & dbSeeChanges are defined. Perhaps they are referencing the old server.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
good point, Randy -- we also need to see how DB is defined

it would be best to post the whole procedure -- and anything it calls
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
TarasAuthor Commented:
Here is code.

Private Sub cmdAuthorize_Click()
  Dim dba As DAO.Database
  Dim lngRowsAffected As Long
  Dim lngRowsDeleted As Long
 
  Dim lngEmpExpID As Long
  Dim lngMonthID As Long
  Dim strAuth  As String
  Dim StrIsActive As String
  Dim strIsCreditCard As String
  Dim strDate As String
 
 
  strIsCreditCard = "No"
 
  StrIsActive = "Active"

 
 
  Dim QryTxt As String
  Dim ExpAuthDate As Date
 

  Set dba = CurrentDb
 
  'we first check if Authorizaton date is selected
  If IsDate(Me.txtExpenceAuthorizationDate) = False Then
    MsgBox "Select Authorization Date"
    Exit Sub
  Else
    ExpAuthDate = CDate(Me.txtExpenceAuthorizationDate)
    strDate = Format(ExpAuthDate, "yyyy/mm/dd")

  End If
 

      If Nz(Me.cboExpenseAuthorization.Column(1), "") <> "" Then
     

       strAuth = Me.cboExpenseAuthorization.Column(1)
       
       
        lngEmpExpID = Me.cboExpEmployeeName.Column(0)
        lngMonthID = Me.cboExpenseMonth.Column(0)
   

       
    Else
       x = MsgBox("Check Authorized By", vbOKOnly + vbExclamation, "Employee Data Aplication")
        Exit Sub
       
   
    End If
   
'       Run update query
 


'  '*********
 QryTxt = "UPDATE tblExpense SET AuthorizedBy ='" & strAuth & "'" & "," & "  AuthorizationDate =#" & strDate & "# " & _
 " WHERE ExpenseEmployeeID =" & lngEmpExpID & "  And MonthID = " & lngMonthID & _
 " And Is_Active ='" & StrIsActive & "'" & " And CreditCard ='" & strIsCreditCard & "'"
 
 
dba.Execute QryTxt, dbSeeChanges
Me.Refresh



Dim strMsgBox
strMsgBox = "Items have been authorized.Please click OK to complete the authorization"

If lngRowsAffected <> 0 Then
   x = MsgBox(strMsgBox, vbOKOnly + vbExclamation, "Employee Data")
  End If

End Sub
0
 
PatHartmanCommented:
If tblExpense is not Jet/ACE, it  must have a primary key defined.  Does it?  If it Jet/ACE, then dbSeeChanges is not required.

Put a stop on the dba.execute line and print QryTxt to the debug window.  Copy the string and post it here.
0
 
TarasAuthor Commented:
Not sure what Jet/Ace refer to.  tblExpense is linked table from SQL Server. It has primary key defined.
0
 
PatHartmanCommented:
Jet and ACE (.mdb and .accdb) are the database engines typically confused with Access.  You did not include SQL Server as a topic so there is no way for us to know what your BE is unless you tell us.

Please follow the previous directions to post the ACTUAL query string being sent to the server.
0
 
Randy DownsOWNERCommented:
I still don't see strSQL defined in your code.
0
 
TarasAuthor Commented:
Randy strSQL was just example,  it is QryTxt as you can see in code from procedure.
0
 
PatHartmanCommented:
In the code, I see the code to create the string.  What I don't see is the result.  For the third time, let's look at the RESULT of this code to ensure that the created query does not contain syntax errors.
0
 
Randy DownsOWNERCommented:
@Taras, sorry didn't make that connection.
0
 
Randy DownsOWNERCommented:
I added MS SQL Server category to question.
0
 
TarasAuthor Commented:
Pat how to get this string:  " print QryTxt to the debug window"
 I put stop there who to get this to print in debug window.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Wild big honkin' hairy guesses>

  • Are any of these columns defined with some kind of formula that does not allow updating to a single value?
  • Is any of this also in the form / subform / ?'s RecordSource such that it is locking the table?
  • Do us all a heavy and add this line -->  Debug.Print QryTxt <-- immediately before the line that executes, run it, then copy-paste what's written to the Immediate window into this question.  Just in case it's a syntax error that's returning this error message instead.
  • Any chance tblExpense is really an Access query or SQL Server view, and that object is not updateable?
  • Does the user executing this code have priveleges to update?

The typical answer to this error message is when an aggregate like SUM() or COUNT() is used, or if there are multiple JOIN statements, but neither seems to apply here.
0
 
TarasAuthor Commented:
When I put cursor over --dba.Execute QryTxt, dbSeeChanges-- it shows QrtTxt= "UPDATE tblExpense SET AuthorizedBy = 'John Smith'.........

Here is QryTxt from debug window:

UPDATE tblExpense SET AuthorizedBy ='John Smith',  AuthorizationDate =#2016/09/19#  WHERE ExpenseEmployeeID =5  And MonthID = 32 And Is_Active ='Active' And CreditCard ='No'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Okay, so the syntax looks good.  Might be worth revisiting the data types of these columns to see if they match the values you're passing.  Especially the 'Active' and 'No'.
0
 
PatHartmanCommented:
Taras,
1. Can you update ANY records using this method or does the error happen for all the expenses you've tried?
2. Can you open the query in DS view and modify data directly?
0
 
TarasAuthor Commented:
Not just with update now I have problem with adding new record.

Before using entry form I was able to add new entry to linked table tblExpenses.  

Now when I tried to add a new record through entry form  to  tblExpense and I got this error.

Run-Time error ‘3027’
Cannot Update. Database or object is read Only.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Now when I tried to add a new record through entry form  to  tblExpense
(Somewhat redundant with the above posts)  Close the form, open up your Access Query Designer / SQL Server SSMS and see if you can run a query to insert or update a record.  If you can, then the problem may lie with the Access form locking the table.
0
 
TarasAuthor Commented:
Just to ad if this can help to solve this issue.
If I open my local access linked table tblExpense and tried to edit one record I am not able to do this.
I was able to do that before we moved tables to new Server and relinked my Access table to those table on new server.


But when I open my SQL management studio and go to that table on the new Server  I can edit record in table.
0
 
TarasAuthor Commented:
Thank you all for your answers it help me to go and check everything. Finally I found that when we moved table from old server somehow it did not move Primary key. When I allocate primary key this error disappear but new one pop up. I will post it in new question.
Thanks a lot.
0
 
PatHartmanCommented:
Taras, You picked some pretty random answers to award points to but you didn't award the actual answer which was #41806814 at 11:03 AM this morning??
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now