469,366 Members | 2,497 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

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

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



Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Public DbMain As dao.Database
  4. Public RsMain As dao.Recordset
  5. Public SqlMain As String
  6.  
  7. Private Sub Command3_Click()
  8. RsMain.AddNew
  9. RsMain.Fields("code") = Me.Text4.Value
  10. RsMain.Fields("accode") = Me.Text0.Value
  11. RsMain.Fields("name") = Me.Text2.Value
  12. RsMain.Update
  13. End Sub
  14.  
  15. Private Sub Form_Load()
  16.  
  17. SqlMain = "SELECT Table1.accode, Table1.Code FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.maincode"
  18. Set DbMain = CurrentDb()
  19. Set RsMain = DbMain.OpenRecordset(SqlMain)
  20. End Sub
  21.  
  22.  
  23.  
Oct 13 '11 #1

✓ answered by NeoPa

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

8 12721
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load() 
  2. SqlMain = "SELECT Table1.accode, Table1.Code FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.maincode" 
  3.  
  4. Set DbMain = CurrentDb() 
  5. Set RsMain = DbMain.OpenRecordset(SqlMain) 
  6.  
  7. MsgBox "This Recordset is: " & IIf(RsMain.Updatable, "Updateable", "Not Updateable")
  8. End Sub
Oct 13 '11 #2
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.
Oct 13 '11 #3
NeoPa
32,185 Expert Mod 16PB
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.
Oct 13 '11 #4
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.
Oct 13 '11 #5
NeoPa
32,185 Expert Mod 16PB
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.
Oct 13 '11 #6
NeoPa
32,185 Expert Mod 16PB
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).
Oct 13 '11 #7
Stewart Ross
2,545 Expert Mod 2GB
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
Oct 13 '11 #8
NeoPa
32,185 Expert Mod 16PB
You may find Reasons for a Query to be Non-Updatable helpful in that respect. Good luck :-)
Oct 13 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Jegger | last post: by
4 posts views Thread by meyvn77 | last post: by
7 posts views Thread by haggis | last post: by
4 posts views Thread by Chuck | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.