473,324 Members | 2,356 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Error 3027 Help using DAO

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
4 7788
Oops Field1 in the SQL string is CustNum now, forgot to change in post. Still doesn't work though
Feb 9 '07 #2
ADezii
8,834 Expert 8TB
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
is there any other way?
Feb 9 '07 #4
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

5
by: Enos Meroka | last post by:
Hallo, I am a student doing my project in the university.. I have been trying to compile the program using HP -UX aCC compiler, however I keep on getting the following errors. ...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
4
by: google | last post by:
I am trying to implement form that allows a user to update data in multiple records in a table based on criteria they enter in the form. Because I want to allow some user interaction on a record...
0
by: PaulB | last post by:
Hi there, Allthough I searched for previous posts, I was unable to solve my problem. I try to link my front-mdb with an Excel file using the next line DoCmd.TransferSpreadsheet acLink, 8,...
1
by: Bruce Le Favre | last post by:
I upsized (using Upsize Wizard) ACCESS back end to MSSQL 2000. Front end is still ACCESS. I'm getting error when trying to add or updated a record entry from front end. The error message reads:...
1
by: Bruce Le Favre | last post by:
I upsized (using Upsize Wizard) ACCESS back end to MSSQL 2000. Front end is still ACCESS. I'm getting error when trying to add or updated a record entry from front end. The error message reads:...
2
by: f rom | last post by:
----- Forwarded Message ---- From: Josiah Carlson <jcarlson@uci.edu> To: f rom <etaoinbe@yahoo.com>; wxpython-users@lists.wxwidgets.org Sent: Monday, December 4, 2006 10:03:28 PM Subject: Re: ...
11
by: loisk | last post by:
(Access 2000/ODBC to MySQL tables) Hi, I am getting the run time erro 3027 and it says that "cannot update: database or object is read only." Does anyone see any errors in my code below? ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.