Solved

MS Access Update query not working

Posted on 2016-09-20
22
231 Views
Last Modified: 2016-09-20
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
Comment
Question by:Taras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +2
22 Comments
 
LVL 21
ID: 41806608
please post strSQL
0
 
LVL 30

Expert Comment

by:Randy Downs
ID: 41806617
We need to see how strSQL & dbSeeChanges are defined. Perhaps they are referencing the old server.
0
 
LVL 21
ID: 41806626
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:Taras
ID: 41806661
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 41806814
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
 

Author Comment

by:Taras
ID: 41806865
Not sure what Jet/Ace refer to.  tblExpense is linked table from SQL Server. It has primary key defined.
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 41806919
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
 
LVL 30

Expert Comment

by:Randy Downs
ID: 41806922
I still don't see strSQL defined in your code.
0
 

Author Comment

by:Taras
ID: 41806966
Randy strSQL was just example,  it is QryTxt as you can see in code from procedure.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41806987
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
 
LVL 30

Assisted Solution

by:Randy Downs
Randy Downs earned 125 total points
ID: 41807007
@Taras, sorry didn't make that connection.
0
 
LVL 30

Expert Comment

by:Randy Downs
ID: 41807030
I added MS SQL Server category to question.
0
 

Author Comment

by:Taras
ID: 41807108
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 41807119
<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
 

Author Comment

by:Taras
ID: 41807213
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41807223
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 41807319
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
 

Author Comment

by:Taras
ID: 41807467
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41807478
>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
 

Author Comment

by:Taras
ID: 41807591
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
 

Author Comment

by:Taras
ID: 41807687
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 41807990
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question