Ms Access Err - Run-time error '3027': Cannot Update. Database or object is read-only

[ritesh272004]

Hi,

Could you help me with the below code from Ms Access. I am trying to add data to an access table. FE and BE in the same file. However i get the error as

Run-time error '3027': Cannot Update. Database or object is read-only

Option Compare Database
Option Explicit
Public DbMain As dao.Database
Public RsMain As dao.Recordset
Public SqlMain As String
 
Private Sub Command3_Click()
RsMain.AddNew
RsMain.Fields("code") = Me.Text4.Value
RsMain.Fields("accode") = Me.Text0.Value
RsMain.Fields("name") = Me.Text2.Value
RsMain.Update
End Sub
 
Private Sub Form_Load()
 
SqlMain = "SELECT Table1.accode, Table1.Code FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.maincode"
Set DbMain = CurrentDb()
Set RsMain = DbMain.OpenRecordset(SqlMain)
End Sub
 
 
 

[ADezii]

It's possible that the Recordset itself is Not Updateable, add a Line of Code (Code Line #7) to the Load() Event to find out for sure:

Private Sub Form_Load() 
SqlMain = "SELECT Table1.accode, Table1.Code FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.maincode" 
 
Set DbMain = CurrentDb() 
Set RsMain = DbMain.OpenRecordset(SqlMain) 
 
MsgBox "This Recordset is: " & IIf(RsMain.Updatable, "Updateable", "Not Updateable")
End Sub

[ritesh272004]

Yes Adezii

I checked with your line of code and you are correct the recordset is not updatable. But i have used this code earlier and have never found such issue.

It would be great if you could help to make it updatable.

[NeoPa]

Let's start by sending you off on a quick detour to When Posting (VBA or SQL) Code. We don't know which line is causing the problem unless you tell us.

Now, with that out of the way, it seems to me you have a much more fundamental problem even than the one you ask for help with. It seems to me you are trying to code around Access instead of using Access to do the work it was designed to do. Access forms are designed to handling updating of data automatically. You are only making life complicated for yourself by tring to update the table using recordset programming. Let the form do what it was designed to do easily.

[ritesh272004]

Hi Guru,

I know access can handle on its own but what i am trying is through recordset programming.

Infact i have done this earlier and it worked , but do not understand why it wont work now.

[NeoPa]

In that case why not start by providing the missing information we would need in order to help you, as I mentioned in post #4? We may be psychic, but you should never rely on it.

[NeoPa]

ritesh272004:
I know access can handle on its own but what i am trying is through recordset programming.

The obvious question is why anyone in their right mind would even want to make their own situation more complicated by taking such an unprofitable approach. I cannot stress enough how bad an idea this is. I've seen programmers spending all their time down this ever-shrinking blind alley, simply because it never occurred to them to find the easier way. If you know the better way but still choose to do it the harder way then just don't say I didn't warn you (and all the other unfortunates that may happen to come across this thread at other times).

[Stewart Ross]

As NeoPa has indicated forcefully, using recordset programming in place of the in-built facilities is likely to prove a frustrating experience unless you have very good reason to do so.

When you ran Adezii's check it confirmed that the recordset you are trying to update is NOT updateable. This is a good indication that you have got something fundamental wrong. This has nothing to do with the code which you tell us you have used before. It is to do with the tables (unhelpfully named table1 and table2) you have joined together, which are not related in a way that Access can update.

No amount of work on recordsets (or forms) will help you if you don't know how to relate two tables so that they are updateable when joined.

I'd suggest a fundamental rethink of your approach, before you go down the code-it blind alley you face here.

-Stewart

[NeoPa]

You may find Reasons for a Query to be Non-Updatable helpful in that respect. Good luck :-)