• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

Can`t Update query using recordset in vba access

Can`t Update query in recordset :
I have this lines of code:

Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("qAddCheckBoxsForBankMimsarimAndBrinksPlusOneD", dbOpenDynaset)

rs.Filter = "TaarichPlus2 = T_Pizoor"
Set rsFiltered = rs.OpenRecordset

If Not (rsFiltered.BOF And rsFiltered.EOF) Then
rsFiltered.MoveFirst
Do Until rsFiltered.EOF
rsFiltered.Edit
rsFiltered!BrinksBankMimsarimID = GetNewID("tblBrinksFiles")
rsFiltered("BrinksBankMizomanID") = GetNewID("tblBrinksFiles")
rsFiltered.Update
rsFiltered.MoveNext
Loop
End If
rs.Close
rsFiltered.Close

I got error: Cannot update.database or object is read-only (error 3027)

 in this line :
rsFiltered.Edit
0
drtopserv
Asked:
drtopserv
  • 2
1 Solution
 
drtopservAuthor Commented:
the content of the query  is :

SELECT tblBrinksFiles.BrinksBankMimsarimID, tblBankMimsarim.BankMimsarimID, tblBrinksFiles.Snif_Sholiach, tblBrinksFiles.ErichMovalBrinksMimsarimMatched, tblBankMimsarim.ErichMovalBankMimsarimMatched, tblBrinksFiles.Erich_Moval, tblBrinksFiles.T_Pizoor, Format([T_Pizoor],"ddd") AS sT_Pizoor, tblBankMimsarim.Taarich, IIf(Format([Taarich],"ddd")<>"ה",[Taarich]+1,[Taarich]+3) AS TaarichPlus2, Format([Taarich],"ddd") AS sTaarich, tblBankMimsarim.Sug_Tchola, tblBrinksFiles.ErichMovalBrinksMimsarimMatchedUpdateTypeStatus, tblBankMimsarim.ErichMovalBankMimsarimMatchedUpdateTypeStatus, tblBrinksFiles.T_Esoof, Format([T_Esoof],"ddd") AS sT_Esoof, tblBrinksFiles.AutomationCheckNoteBrinksMimsarim, tblBankMimsarim.AutomationCheckNoteBankMimsarim
FROM tblBrinksFiles INNER JOIN tblBankMimsarim ON (tblBrinksFiles.Snif_Sholiach = tblBankMimsarim.Snif_Sholiach) AND (tblBrinksFiles.Sug_Tchola = tblBankMimsarim.Sug_Tchola) AND (tblBrinksFiles.Erich_Moval = tblBankMimsarim.Erich_Moval_Shalim);
0
 
Dany BalianCTOCommented:
try this:
Set rs = CurrentDb.OpenRecordset("qAddCheckBoxsForBankMimsarimAndBrinksPlusOneD",dbOpenDynamic,0,dbOptimistic)

Open in new window


source: Microsoft: Run-Time Error '3027' Using ODBCDirect to Open RecordSet
0
 
drtopservAuthor Commented:
invalid argument error 3001
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is the query itself updateable? That is, if you open the query in Design View in Access, can you then modify the query?

If it is updateable, then you might try removing the rst.Filter, and instead just create a recordset with a filter:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM qAddCheckBoxsForBankMimsarimAndBrinksPlusOneD WHERE TaarichPlus2 = T_Pizoor", dbOpenDynaset)

Although it looks like the WHERE clause above (and your Filter line) are wrong. If you want to filter on a VALUE name "T_Pizoor", then you'd have to somehow supply that value. Is "T_Pizoor" a value from a form? Doesn't look like it, but if you could explain more about that it would be helpful.


See this blog post for a listing of reasons that can cause a query to be not updatedable:

http://rogersaccessblog.blogspot.com/2009/11/this-recordset-is-not-updateable-why.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now