By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,277 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Error 3027 Help using DAO

P: 5
I am having a problem with my Database. I am having an issue with being able to update a set of records. What I need is a way to uncheck a checkbox in the underlying table in this query. The # of checkboxes to be unchecked will be different for each customer, so I've been told an SQL query won't work. When I run the following code I get "error 3027 Cannot update. Database or object is read only" It stops at rs.edit

Expand|Select|Wrap|Line Numbers
  1. testString = "SELECT tblTEST.[CustNum], tblTEST.[Month/Year], tblTEST.[Yes/No] " & _
  2.              "FROM tblTEST " & _
  3.              "GROUP BY tblTEST.Field1, tblTEST.[Month/Year], tblTEST.[Yes/No]"
  4.  
  5.         Dim MyDB As DAO.Database
  6.         Dim rs As DAO.Recordset
  7.         Set MyDB = CurrentDb
  8.  
  9. Set rs = CurrentDb.OpenRecordset(testString, dbOpenDynaset, 0,dbOptimistic)
  10.  
  11.         tempBalance = 0
  12.  
  13.         Do
  14.  
  15.             If rs.EOF = False Then
  16.  
  17.                 rs.Edit    'This is where it craps out
  18.                 rs("Yes / NO") = 0
  19.                 rs.Update
  20.  
  21.                 rs.MoveNext
  22.             Else     'last record don't keep moving
  23.                 Exit Do
  24.                 'MsgBox ("exited loop")
  25.             End If
  26.  
  27.         Loop While tempBalance < 3
I have tried using Set rs = MyDB.OpenRecordset(testString, dbOpenDynamic) in place of the current call just to check and it gives me "error 3001 invalid argument". Any help I am lost at this point, Thanks
Feb 9 '07 #1
Share this Question
Share on Google+
4 Replies


P: 5
Oops Field1 in the SQL string is CustNum now, forgot to change in post. Still doesn't work though
Feb 9 '07 #2

ADezii
Expert 5K+
P: 8,638
I am having a problem with my Database. I am having an issue with being able to update a set of records. What I need is a way to uncheck a checkbox in the underlying table in this query. The # of checkboxes to be unchecked will be different for each customer, so I've been told an SQL query won't work. When I run the following code I get "error 3027 Cannot update. Database or object is read only" It stops at rs.edit

testString = "SELECT tblTEST.[CustNum], tblTEST.[Month/Year], tblTEST.[Yes/No] FROM tblTEST GROUP BY tblTEST.Field1, tblTEST.[Month/Year], tblTEST.[Yes/No]"

Dim MyDB As DAO.Database
Dim rs As DAO.Recordset
Set MyDB = CurrentDb

Set rs = CurrentDb.OpenRecordset(testString, dbOpenDynaset, 0,dbOptimistic)

tempBalance = 0

Do

If rs.EOF = False Then

rs.Edit 'This is where it craps out
rs("Yes / NO") = 0
rs.Update

rs.MoveNext
Else 'last record don't keep moving
Exit Do
'MsgBox ("exited loop")
End If

Loop While tempBalance < 3

I have tried using Set rs = MyDB.OpenRecordset(testString, dbOpenDynamic) in place of the current call just to check and it gives me "error 3001 invalid argument". Any help I am lost at this point, Thanks
Any Recordset created by using a GROUP BY Clause is not updateable.
Feb 9 '07 #3

P: 5
is there any other way?
Feb 9 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
Try this SQL instead :
Expand|Select|Wrap|Line Numbers
  1. testString = "SELECT DISTINCT [CustNum],[Month/Year],[Yes/No] " & _
  2.              "FROM tblTEST"
Feb 10 '07 #5

Post your reply

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