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
3 2530 NeoPa 32,556
Expert Mod 16PB
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. - 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 32,556
Expert Mod 16PB
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 1,418
Expert 1GB
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Irvin |
last post by:
I new to ASP.net and am using the following code to attempt to update
an Access 2000 mdb. The code does make it through the code following
"try". NO rows are updated. There is a row with the...
|
by: Mike |
last post by:
Hey guys I need some help with updateing an access database. I used OleDB
controls to set up my connection, adapter, and dataset.
The access table I am trying to update has these columns in this...
|
by: my-wings |
last post by:
I think I've painted myself into a corner, and I'm hoping someone can help
me out.
I have a table of books (tblBooks), which includes a field (strPubName) for
Publisher Name and another field...
|
by: pcthug |
last post by:
Hi All,
I am creating multi-tier app in vb.net using visual studio .net.
I create a invoice.vb class file with properties, events and methods. This
also has a line item collection class...
|
by: Gary Paris |
last post by:
This should be simple but I can't get it to work. I want to update a single
row of data in a table.
ES.Clear()
Dim strSQL As String = "Select * from Contact where sysid = '" &
g_sysID & "'"...
|
by: Cheryl Langdon |
last post by:
Hello everyone,
This is my first attempt at getting help in this manner. Please
forgive me if this is an inappropriate request.
I suddenly find myself in urgent need of instruction on how to...
|
by: Learner |
last post by:
Hello,
Here is the code snippet I got strucked at.
I am unable to convert the below line of code to its equavalent vb.net
code. could some one please help me with this?
static public...
|
by: mike |
last post by:
I help manage a large web site, one that has over
600 html pages... It's a reference site for ham radio
folks and as an example, one page indexes over
1.8 gb of on-line PDF documents.
The site...
|
by: Rich Squid |
last post by:
Hello
Here's my basic problem:
On my asp.net form page I have a DetailsView (default mode=edit) bound
to a AccessDataSource control. Users can successfuly update a databound
template field,...
|
by: evilbungle |
last post by:
Good Morning,
Hopefully someone can help me with what I am assuming is a simple query that I simply can't get to work.
I am new to using Visual Basic and I am using Visual Studio 2005,...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |