Need Help: Can't Update

**NeoPa** · Nov 24 '06, 07:38 PM

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.

Code:

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

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

thisdb.Execute ("Delete * From BM_Roster")

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

'Queries with JOINed tables in are more likely to qualify as updatable (as well as run more efficiently)
'Set rsBranch = thisdb.OpenRecordset("Select * From Branch,BM_ROSTER Where Branch.StateBranch = BM_ROSTER.sapNo", dbOpenDynaset)
Set rsBranch = thisdb.OpenRecordset("Select * From Branch INNER JOIN BM_ROSTER ON Branch.StateBranch = BM_ROSTER.sapNo", dbOpenDynaset)

Do Until rsBranch.EOF
   'To simplify
   '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
   BM_RosterName = rsBranch!Fname & " " & _
                   IIf(rsBranch!MName>" ",rsBranch!MName & " ","") & _
                   rsBranch!LName

   'To simplify
   'If rsBranch.Fields("BranchManager") = BM_RosterName Then
   ''do nothing
   'Else
   If rsBranch!BranchManager <> BM_RosterName Then
      rsBranch.Edit
      rsBranch.Fields("BranchManager") = BM_RosterName
      'Following lines refer to rsBMList - where defined?
      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

**NeoPa** · Nov 24 '06, 07:44 PM

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.

**PEB** · Nov 26 '06, 04:47 PM

In fact it is important to know on which line is produced the fault?

Originally posted by EForbes

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.SetWarnin gs False

Set thisdb = CurrentDb

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

thisdb.Execute ("Delete * From BM_Roster")

DoCmd.TransferT ext acImportDelim, "BMImport", "BM_ROSTER" , "C:\tbl_roster_ SPA_BM.txt"

Set rsBranch = thisdb.OpenReco rdset("Select * From Branch,BM_ROSTE R Where Branch.StateBra nch = 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 ("BranchManFNam e") = rsBMList.Fields ("Fname")
rsBranch.Fields ("BranchManLNam e") = rsBMList.Fields ("Lname")
rsBranch.Fields ("Modified") = True
rsBranch.Fields ("DateModified" ) = strModDate
rsBranch.Update
End If
rsBranch.MoveNe xt
rsBMList.MoveNe xt
Loop

rsBranch.Close
Set rsBranch = Nothing

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

End Function

Need Help: Can't Update

Need Help: Can't Update

Comment

Comment

Comment