MS Access Update query not working

crystal (strive4peace) - Microsoft MVP, Access

We need to see how strSQL & dbSeeChanges are defined. Perhaps they are referencing the old server.

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

ASKER

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

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.

ASKER

Not sure what Jet/Ace refer to. tblExpense is linked table from SQL Server. It has primary key defined.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

I still don't see strSQL defined in your code.

ASKER

Randy strSQL was just example, it is QryTxt as you can see in code from procedure.

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.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

I added MS SQL Server category to question.

ASKER

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.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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'

Jim Horn

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'.

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?

ASKER

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.

Jim Horn

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

ASKER

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.

ASKER

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.