Cannot update records into tables.

J

J Cole

Hi

I have a user form from which I am trying to insert records into a few
tables and when I click on update, I get a message box which reads "Cannot
update. Database or object is read-only." (I am using Microsoft DAO 3.6
Object Library). I have ensured that the user has permissions on the
database. Is there anything that I am missing.

TIA
Cole
 
R

Rick B

Does the user have FULL permission to the folder where the database lives?
Add, delete, change, etc?

Whay "update" are you clicking? I don't see an "Update" button or link in
Access.
 
J

J Cole

Rick,

Thanks for the quick response. I have a custom "update" button on the form.
Yes, I am the admin on the machine on which I have created this db and I
have verified that the user has full permissions.
When the user clicks on the button, the following piece of code gets
executed.

excerptPrivate Sub cmdInsertRecord_Click()
On Error GoTo Err_cmdInsertRecord_Click

DoCmd.GoToRecord , , acNewRec
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employee")

Dim EmpCount As String
Dim Count As Integer
Dim Inc As Integer
Dim newEmpID As Integer


EmpCount = "SELECT count(*) from Employee"
Set db = CurrentDb
Set rst = db.OpenRecordset(EmpCount)
Count = rst![Expr1000]
Inc = 1
newEmpID = Inc + Count

rst.AddNew
rst("Emp Id") = newEmpID
rst("EmpName") = Me.txtEmpName.Value
rst("EmpComment") = Me.txtEmpComment.Value

rst.Update
rst.Close

Exit_cmdInsertRecord_Click:
Exit Sub

Err_cmdInsertRecord_Click:
MsgBox Err.Description
Resume Exit_cmdInsertRecord_Click


End Sub
<<<<<

TIA,
Cole
 
J

J Cole

Rick,

I figured it. If you observe, I am altering rst in the excerpt below ... and
that was causing a problem.

Anyways thanks much.
Cole

J Cole said:
Rick,

Thanks for the quick response. I have a custom "update" button on the form.
Yes, I am the admin on the machine on which I have created this db and I
have verified that the user has full permissions.
When the user clicks on the button, the following piece of code gets
executed.

excerptPrivate Sub cmdInsertRecord_Click()
On Error GoTo Err_cmdInsertRecord_Click

DoCmd.GoToRecord , , acNewRec
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Employee")

Dim EmpCount As String
Dim Count As Integer
Dim Inc As Integer
Dim newEmpID As Integer


EmpCount = "SELECT count(*) from Employee"
Set db = CurrentDb
Set rst = db.OpenRecordset(EmpCount)
Count = rst![Expr1000]
Inc = 1
newEmpID = Inc + Count

rst.AddNew
rst("Emp Id") = newEmpID
rst("EmpName") = Me.txtEmpName.Value
rst("EmpComment") = Me.txtEmpComment.Value

rst.Update
rst.Close

Exit_cmdInsertRecord_Click:
Exit Sub

Err_cmdInsertRecord_Click:
MsgBox Err.Description
Resume Exit_cmdInsertRecord_Click


End Sub
<<<<<

TIA,
Cole


Rick B said:
Does the user have FULL permission to the folder where the database lives?
Add, delete, change, etc?

Whay "update" are you clicking? I don't see an "Update" button or link in
Access.
Click to expand...
Click to expand...
 
V

Van T. Dinh

Yep.

Your use the same name "rst" for 2 different Recordset and the 2nd "Set rst
= " statement creates a Recordset based on a Total Query which is not
updateable. The Field collection would also be incorrect when you tried to
assign the values to the Fields.

HTH
Van T. Dinh
MVP (Access)
 
Top