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

Need Help: Can't Update

P: 1
Not an expert so I need some help. I have this function that I am trying to run. The function is to compine two tables, comcare a field in the tables and update the one table if te fields are not equal. I am getting the Run-Time error 3027 Can not update. The database or object is read only. I know for sure that the database is not read only because I am able to edit the tables within other forms and modules. I believe that it might have something to do with the import command but do not understand how...

Function ImportBMList()

Dim thisdb As DAO.Database
Dim rsBranch As DAO.Recordset
Dim BM_RosterName As String
Dim strModDate As String
Dim mmddyyyy As Date

DoCmd.SetWarnings False

Set thisdb = CurrentDb

strModDate = Format(Date, "mm" & "/" & "dd" & "/" & "yyyy")

thisdb.Execute ("Delete * From BM_Roster")

DoCmd.TransferText acImportDelim, "BMImport", "BM_ROSTER", "C:\tbl_roster_SPA_BM.txt"

Set rsBranch = thisdb.OpenRecordset("Select * From Branch,BM_ROSTER Where Branch.StateBranch = BM_ROSTER.sapNo", dbOpenDynaset)

Do Until rsBranch.EOF
If rsBranch.Fields("MName") = " " Then
BM_RosterName = rsBranch.Fields("Fname") & " " & rsBranch.Fields("LName")
Else
BM_RosterName = rsBranch.Fields("Fname") & " " & rsBranch.Fields("MName") & " " & rsBranch.Fields("LName")
End If

If rsBranch.Fields("BranchManager") = BM_RosterName Then
'do nothing
Else
rsBranch.Edit
rsBranch.Fields("BranchManager") = BM_RosterName
rsBranch.Fields("BranchManFName") = rsBMList.Fields("Fname")
rsBranch.Fields("BranchManLName") = rsBMList.Fields("Lname")
rsBranch.Fields("Modified") = True
rsBranch.Fields("DateModified") = strModDate
rsBranch.Update
End If
rsBranch.MoveNext
rsBMList.MoveNext
Loop

rsBranch.Close
Set rsBranch = Nothing

MsgBox "Branch Manager Roster Imported.", , "Citizens Hierarchy"

End Function
Nov 24 '06 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
It's said often in these forums, but I suggest you look at the conceptual design of your data.
Storing the same info more than once is a recipe for problems I'm afraid.

However, in the current circumstances I've gone through your code and made some of the more important changes. Hopefully the comments by the changes indicate clearly why change is advised.
You may not see the results clearly on a web page so for a closer look, just copy and paste elsewhere.
Expand|Select|Wrap|Line Numbers
  1. Function ImportBMList()
  2.  
  3. Dim thisdb As DAO.Database
  4. Dim rsBranch As DAO.Recordset
  5. Dim BM_RosterName As String
  6. Dim strModDate As String
  7. Dim mmddyyyy As Date
  8.  
  9. DoCmd.SetWarnings False
  10.  
  11. Set thisdb = CurrentDb
  12.  
  13. 'Unnecessarily complicated
  14. 'strModDate = Format(Date, "mm" & "/" & "dd" & "/" & "yyyy")
  15. strModDate = Format(Date, "mm/dd/yyyy")
  16.  
  17. thisdb.Execute ("Delete * From BM_Roster")
  18.  
  19. DoCmd.TransferText acImportDelim, "BMImport", "BM_ROSTER", "C:\tbl_roster_SPA_BM.txt"
  20.  
  21. 'Queries with JOINed tables in are more likely to qualify as updatable (as well as run more efficiently)
  22. 'Set rsBranch = thisdb.OpenRecordset("Select * From Branch,BM_ROSTER Where Branch.StateBranch = BM_ROSTER.sapNo", dbOpenDynaset)
  23. Set rsBranch = thisdb.OpenRecordset("Select * From Branch INNER JOIN BM_ROSTER ON Branch.StateBranch = BM_ROSTER.sapNo", dbOpenDynaset)
  24.  
  25. Do Until rsBranch.EOF
  26.    'To simplify
  27.    'If rsBranch.Fields("MName") = " " Then
  28.    'BM_RosterName = rsBranch.Fields("Fname") & " " & rsBranch.Fields("LName")
  29.    'Else
  30.    'BM_RosterName = rsBranch.Fields("Fname") & " " & rsBranch.Fields("MName") & " " & rsBranch.Fields("LName")
  31.    'End If
  32.    BM_RosterName = rsBranch!Fname & " " & _
  33.                    IIf(rsBranch!MName>" ",rsBranch!MName & " ","") & _
  34.                    rsBranch!LName
  35.  
  36.    'To simplify
  37.    'If rsBranch.Fields("BranchManager") = BM_RosterName Then
  38.    ''do nothing
  39.    'Else
  40.    If rsBranch!BranchManager <> BM_RosterName Then
  41.       rsBranch.Edit
  42.       rsBranch.Fields("BranchManager") = BM_RosterName
  43.       'Following lines refer to rsBMList - where defined?
  44.       rsBranch.Fields("BranchManFName") = rsBMList.Fields("Fname")
  45.       rsBranch.Fields("BranchManLName") = rsBMList.Fields("Lname")
  46.       rsBranch.Fields("Modified") = True
  47.       rsBranch.Fields("DateModified") = strModDate
  48.       rsBranch.Update
  49.    End If
  50.    rsBranch.MoveNext
  51.    rsBMList.MoveNext
  52. Loop
  53.  
  54. rsBranch.Close
  55. Set rsBranch = Nothing
  56.  
  57. MsgBox "Branch Manager Roster Imported.", , "Citizens Hierarchy"
  58.  
  59. End Function
Nov 24 '06 #2

NeoPa
Expert Mod 15k+
P: 31,186
BTW the error message refers to the database or object being Read Only.
It is not your database but the object - in this case your query (the recordset in rsBranch).
I don't know all the rules that make a recordset updatable, but I can suggest that you are able to test it before adding it to your code by inserting it into an Access Query (in SQL view). When you display an updatable query it will show an extra record entry at the end for entering a new record in. If this is absent, then I'm afraid your query is non-conformant.
Nov 24 '06 #3

PEB
Expert 100+
P: 1,418
PEB
In fact it is important to know on which line is produced the fault?

Not an expert so I need some help. I have this function that I am trying to run. The function is to compine two tables, comcare a field in the tables and update the one table if te fields are not equal. I am getting the Run-Time error 3027 Can not update. The database or object is read only. I know for sure that the database is not read only because I am able to edit the tables within other forms and modules. I believe that it might have something to do with the import command but do not understand how...

Function ImportBMList()

Dim thisdb As DAO.Database
Dim rsBranch As DAO.Recordset
Dim BM_RosterName As String
Dim strModDate As String
Dim mmddyyyy As Date

DoCmd.SetWarnings False

Set thisdb = CurrentDb

strModDate = Format(Date, "mm" & "/" & "dd" & "/" & "yyyy")

thisdb.Execute ("Delete * From BM_Roster")

DoCmd.TransferText acImportDelim, "BMImport", "BM_ROSTER", "C:\tbl_roster_SPA_BM.txt"

Set rsBranch = thisdb.OpenRecordset("Select * From Branch,BM_ROSTER Where Branch.StateBranch = BM_ROSTER.sapNo", dbOpenDynaset)

Do Until rsBranch.EOF
If rsBranch.Fields("MName") = " " Then
BM_RosterName = rsBranch.Fields("Fname") & " " & rsBranch.Fields("LName")
Else
BM_RosterName = rsBranch.Fields("Fname") & " " & rsBranch.Fields("MName") & " " & rsBranch.Fields("LName")
End If

If rsBranch.Fields("BranchManager") = BM_RosterName Then
'do nothing
Else
rsBranch.Edit
rsBranch.Fields("BranchManager") = BM_RosterName
rsBranch.Fields("BranchManFName") = rsBMList.Fields("Fname")
rsBranch.Fields("BranchManLName") = rsBMList.Fields("Lname")
rsBranch.Fields("Modified") = True
rsBranch.Fields("DateModified") = strModDate
rsBranch.Update
End If
rsBranch.MoveNext
rsBMList.MoveNext
Loop

rsBranch.Close
Set rsBranch = Nothing

MsgBox "Branch Manager Roster Imported.", , "Citizens Hierarchy"

End Function
Nov 26 '06 #4

Post your reply

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