PDA

View Full Version : Edit MS Access data using Excel



sbbmaster09
07-01-2016, 06:38 AM
Hi, Ive been different forums and no one answering me, i hope I could get some bright minds over here whom can help me with my code, my code here returns an error of "Database or Object is read only" . How can a fix this? Whats wrong in my code? Ive been trying to search it but no luck :(




Public Sub UpdateEntry()
Dim cn As DAO.Database, rs As DAO.Recordset, sSql, TaskID As String




DBpath = Sheet1.Cells(1, 1).Value
TaskID = Sheet2.Cells(6, 2).Value
sSql = "SELECT * FROM qryData WHERE [Task ID] = '" & TaskID & "'"


Sheet2.Select


Set cn = OpenDatabase(DBpath)
Set rs = cn.OpenRecordset(sSql)


With rs
.Edit
.Fields("[Initial Allocation Date]") = Range("B4").Value
.Fields("[Date Processed]") = Range("B5").Value
.Fields("[Allocated To]") = Range("B6").Value
.Fields("[Business Name]") = Range("B8").Value
.Fields("[AI Due Date]") = Range("B9").Value
.Fields("[AI Owner Site]") = Range("B10").Value
.Fields("[AI Status]") = Range("B11").Value
.Fields("[AI Completion Date]") = Range("B12").Value
.Fields("[Comments]") = Range("B13").Value
.Fields("[Referral Originator (Site)]") = Range("B14").Value
.Fields("[Date Referral Raised]") = Range("B15").Value
.Fields("[Date Referral Completed]") = Range("B16").Value
.Fields("[Referral Status]") = Range("B17").Value
.Fields("[Referral Owner]") = Range("B18").Value
.Update
End With


rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing


MsgBox ("Entry Updated!")

End Sub

Kenneth Hobs
07-01-2016, 02:09 PM
Maybe the MDB file is read-only? Maybe your connection string does not have a valid username and password? I doubt that the MDB or ACCB would have a field called [Task ID].