Recordset.Update Database or Object is read-only

sql ms-access vba recordset
Graham Warrender picture Graham Warrender · Dec 6, 2013 · Viewed 13.5k times · Source

Not sure this is going to work in the specifics I intend. The scenario is the text boxes are populated with data from a table, and the user edits the records and clicks a button to save. 

Dim cdb As DAO.Database, rstEdit As DAO.Recordset
Set cdb = CurrentDb
DataValues = "SELECT * FROM Companies, Link_Table WHERE Companies.CompanyID =  " & SelectedValue & ";"
Set rstEdit = CurrentDb.OpenRecordset(DataValues, dbOpenSnapshot)

With rstEdit
 OldCompanyName = !CompanyName
 OldCompanyDescription = !Description
 OldFriendlyName = !FriendlyName
 OldAddressLine1 = !AddressLine1
 OldAddressLine2 = !AddressLine2
 OldAddressLine3 = !AddressLine3
 OldTown = !Town
 OldPostcode = !AddressPostcode
 OldCounty = !AddressCounty
 OldMainTelephone = !MainTelephone
 OldMainEmail = !MainEmail
 OldWeb = !WebAddress

'Not sure this is necessary. It was quoted in the example, but unsure why? ^'

 !CompanyName = NewCompanyName
 !Description = NewCompanyDescription
 !FriendlyName = NewFriendlyName
 !AddressLine1 = NewAddressLine1
 !AddressLine2 = NewAddressLine2
 !AddressLine3 = NewAddressLine3
 !Town = NewTown
 !AddressPostcode = NewPostcode
 !AddressCounty = NewCounty
 !MainTelephone = NewMainTelephone
 !MainEmail = NewMainEmail
 !WebAddress = NewWeb
 .Update
End With

However, on clicking the save button, it errors stating that the Database or object is read only, and I'm unsure why. As far as I know it isn't open anywhere, nor should it get opened for any reason! Was hoping someone would be able to shed some light on the issue. The table has a primary key set, which is CompanyID however this isn't used in the routine.

Answer

pteranodon picture pteranodon · Dec 6, 2013

The Type parameter dbOpenSnapShot in:

Set rstEdit = CurrentDb.OpenRecordset(DataValues, dbOpenSnapshot)

makes rstEdit open as a read-only recordset(or snapshot). Consider using dbOpenDynaset instead.

Related questions

How to insert values into the database table using VBA in MS access

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error: Too few parameters. I have a table test with only one column in …

Read More
MS Access - execute a saved query by name in VBA

How do I execute a saved query in MS Access 2007 in VBA? I do not want to copy and paste the SQL into VBA. I rather just execute the name of the query. This doesn't work ... VBA can't find the …

Read More
Using Excel VBA to export data to MS Access table

I am currently using following code to export data from worksheet to MS Access database, the code is looping through each row and insert data to MS Access Table. Public Sub TransData() Application.ScreenUpdating = False Application.EnableAnimations = False Application.EnableEvents = …

Read More