Solved

MS Access Update query not working

Posted on 2016-09-20
22
55 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
  • 8
  • 5
  • 4
  • +2
22 Comments
 
LVL 18
ID: 41806608
please post strSQL
0
 
LVL 29

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 18
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
 

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 34

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 34

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 29

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 34

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 29

Assisted Solution

by:Randy Downs
Randy Downs earned 125 total points
ID: 41807007
@Taras, sorry didn't make that connection.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 29

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 65

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 65

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 34

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 65

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 34

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now