Update a reocrd in Access 2003 from Excel

[kbsudhir]

Hi All,

I want to know how to create a query in excel to update a record as per the criteria give in access 2003 from excel 2003.

Please guide.

:help :help

Regards
Sudhir

[CreganTur]

You can accomplish this by using either a DAO (Data Access Objects) or an ADO (ActiveX Data Objects) connection.

Here's a DAO example:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strFind As String
Dim intResult As Integer
Set db = OpenDatabase("C:\Acc07_ByExample\Northwind.mdb")
Set rst = db.OpenRecordset("Employees", dbOpenTable)
rst.MoveFirst
'change the zip/postal code of all employees from 99999 to 99998
Do Until rst.EOF
With rst
.Edit
.Fields("PostalCode") = "99998"
.Update
.MoveNext
End With
Loop
'find the record for Regina Smith and update country/region field
strFind = "Smith"
rst.MoveFirst
rst.Index = "LastName"
rst.Seek "=", strFind
MsgBox rst!LastName
Debug.Print rst.EditMode
rst.Edit
rst!Region = "USA"
If rst.EditMode = dbEditInProgress Then
intResult = MsgBox("Do you want to save the changes?", vbYesNo)
End If
If intResult = vbYes Then
rst.Update
ElseIf intResult = vbNo Then
rst.CancelUpdate
End If
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Here's an ADO example:
Dim rst As ADODB.Recordset
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Acc07_ByExample\Northwind.mdb"
Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM Employees WHERE LastName = 'Roberts'" _
, strConn, adOpenKeyset, adLockOptimistic
.Fields("City").Value = "Redmond"
.Fields("Region").Value = "WA"
.Fields("Country").Value = "USA"
.Update
.Close
End With
Set rst = Nothing

HTH:thumb

[kbsudhir]

Hi Randy,

I am getting the that the table is readonly. & anot able to update the table.

I am using ADODB connection

Below is the code I am using
rs.Open "SELECT Request_Table.FollowUp FROM Request_Table WHERE (((Request_Table.[Req#])=PreReq)) GROUP BY Request_Table.FollowUp;", cn, adOpenStatic, adLockOptimistic
FollowUp = rs(0)
' rs.Open "", cn, adOpenDynamic, adLockBatchOptimistic


If FollowUp = "" Then
FollowUp = 1
Else
FollowUp = FollowUp + 1
End If

rs(0).Value = FollowUp
rs.Update
rs.Close

Nobody except me is accessing the code through the above code.

Please Guide.

Regards
Sudhir

[CreganTur]

Is there a Form or any other object open that is already accessing the table? If another object is accessing the table, then it probably has it locked for editing.

[kbsudhir]

Nope. No other object is accessing the table in any manner.

In the same connection I am opening a recordset witha different query on the same table & then closing the recordset using rs.close & opening it again using the above given code.

Thanks
Sudhir

[CreganTur]

Let me see the code, please.

[kbsudhir]

Below is the code


Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=F:\DB\CostChange.mdb;"

'open a recordset to get the last Req#
Set rs = New ADODB.Recordset
rs.Open "SELECT Max(Request_Table.ReqNo) FROM Request_Table", cn, adOpenStatic, adLockOptimistic

ReqNo = rs(0)
rs.Close
rs.Open "Select * from Request_Table", cn, adOpenStatic, adLockOptimistic

Set OlItems = olFolder.Items

For Each olItem In OlItems
Subject = olItem.Subject

If InStr(1, UCase(Subject), "Req#", vbTextCompare) > 0 Then

PreReq = Trim(Replace(Subject, "Req#", ""))

PreReq = Trim(Left(PreReq, 6))

'open a recordset to check the followup#
Set rs2 = New ADODB.Recordset
rs2.Open "SELECT Request_Table.FollowUp FROM Request_Table WHERE (((Request_Table.[Req#])=PreReq)) GROUP BY Request_Table.FollowUp;", cn, adOpenStatic, adLockOptimistic
FollowUp = rs(0)
rs2.Close

Else

ReqNo = ReqNo + 1

'Prefixing the Req# to the subject

Subject = "Req#" & " " & ReqNo & " " & Subject

'Updating the table with data mail data
rs.AddNew

rs.Fields("ReqNo") = ReqNo
rs.Fields("Subject") = olItem.Subject
rs.Fields("Received_Date_n_Time") = olItem.ReceivedTime
rs.Fields("From") = olItem.SenderName
rs.Fields("Senders_Email") = olItem.SenderEmailAddress
rs.Fields("To") = olItem.To
rs.Fields("CC") = olItem.CC
rs.Fields("Importance") = olItem.Importance
rs.Fields("Message_Body") = olItem.Body
rs.Fields("FollowUp") = 0

x = olItem.Attachments.count

If x > 0 Then
rs.Fields("Attachment") = "Yes"
Else
rs.Fields("Attachment") = "No"
End If

olItem.Subject = Subject

'Saving the mail with updated subject name

olItem.Save

'Providing path for the parent folder
ParentFld = "F:\StoringMails\Temp Mails\"

'Providing path for the folder mhere mail is to be saved
FldrPath = ParentFld & ReqNo

'Verifying whether the folder exists or not
' If no then creating the folder

If Dir(ParentFld & ReqNo, vbDirectory) = "" Then
MkDir FldrPath
FlName = FldrPath & "\" & Subject & ".msg"
olItem.SaveAs FlName

End If

rs.Fields("FollowUp") = 0
rs.Fields("Location") = FldrPath

rs.Update
rs.Close

End If

olItem.BodyFormat = olFormatHTML

Body = olItem.Body
Dim oReply As Outlook.MailItem
Set oReply = olItem.ReplyAll

Body = oReply.Body

oReply.Body = "" & vbCr & "Hi" & vbCr & _
"Please refer the attached spreadsheet. One of the following info is missing from the highlighted rows" & vbCr & _
"1. Desc" & vbCr & _
"2. ContacInfo" & vbCr & _

oReply.Attachments.add strFile
oReply.Display

FlName = FldrPath & "\" & oReply.Subject & ".msg"
oReply.SaveAs FlName

oReply.Send

rs.Open "SELECT Request_Table.ReqNo, Request_Table.FollowUp FROM Request_Table WHERE (((Request_Table.ReqNo)=ReqNo)) GROUP BY Request_Table.ReqNo, Request_Table.FollowUp", cn, adOpenStatic, adLockPessimistic

FollowUp = rs(1).Value

rs("FollowUp").Value = FollowUp + 1
rs.Update
rs.Close

Next



I really appreciate your valueable time.

Regards
Sudhir