Good morning,

I have the following code I use to send data from Excel to
and Access database and it works fine. I now need to
update an existing record in the Access database from
Excel and I am not sure how to do that. I assume I can
use the "update" command but don't know where I should put
the criteria to determine what record to update. The
record to update is based on the "PDANum" field.

Any help would be appreciated. Thank you.

Dim response As String
Dim counter As Integer
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As
Long
' connect to the Access database
Set cn = New ADODB.Connection
'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
' "Data Source=T:\PDA\PDA Request.mdb;"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=T:\PDA\PDA Request.mdb;"
' open a recordset for PDATracking Table
Set rs = New ADODB.Recordset
rs.Open "PDATracking", cn, adOpenKeyset,
adLockOptimistic, adCmdTable
' all records in a table
With rs
.update (assume I use this command rather
than .AddNew)
'.AddNew ' create a new record
' add values to each field in the record
'pulls user id entered on frmPassword
.Fields("UserID") = strUserID
'pulls PDA Status entered on frmPDAStatus
.Fields("PDAStatus") = strPDAStatus
.Fields("Brand") = Range("Brand").Value
.Fields("BuyingGroup") = Range
("BuyingGroup").Value
.Fields("PDANum") = Range("PDANum").Value
.Fields("DateSubmitted") = Range
("DateSubmitted").Value
.Fields("RegMgr") = Range("RegMgr").Value
.Fields("Requestor") = Range("Requestor").Value
.Fields("DistributorLoc") = Range
("DistributorLoc").Value
.Fields("Distributor") = Range
("Distributor").Value
.Fields("DistributorCustNo") = Range
("DistributorCustNo").Value
.Fields("NoLocations") = Range
("NoLocations").Value
.Fields("SalesAmt") = Range("SalesAmt").Value
.Fields("ForecastSalesAmt") = Range
("ForecastSalesAmt").Value
.Fields("DistEmail") = Range("DistEmail").Value
.Fields("PriceQuoteEffectiveDateStart") = Range
("PriceQuoteEffectiveDateStart").Value
.Fields("PriceQuoteEffectiveDateEnd") = Range
("PriceQuoteEffectiveDateEnd").Value
.Fields("PrgmOffInvoice") = Range
("PrgmOffInvoice").Value
.Fields("PrgmFileBack") = Range
("PrgmFileBack").Value
.Fields("Displays") = Range("Displays").Value
.Fields("Advertising") = Range
("Advertising").Value
.Fields("Sample18x24") = Range
("Sample18x24").Value
.Fields("Sample9x12") = Range
("Sample9x12").Value
.Fields("Other") = Range("Other").Value
.Fields("Rebates") = Range("Rebates").Value
.Fields("KeyElement") = Range
("KeyElement").Value
.Fields("JobLock") = Range("JobLock").Value
.Fields("SpecialConditions") = Range
("SpecialConditions").Value
.Fields("RegMgrApproval") = Range
("RegMgrApproval").Value
.Fields("NSMgrApproval") = Range
("NSMgrApproval").Value
.Fields("ApprovalDateRM") = Range
("ApprovalDateRM").Value
.Fields("ApprovalDateNSM") = Range
("ApprovalDateNSM").Value
.Fields("Declined") = Range("Declined").Value
.Fields("DeclinedDate") = Range
("DeclinedDate").Value
.Fields("Reason") = Range("Reason").Value
' add more fields if necessary...
.Update ' stores the new record
End With
' r = r + 1 ' next row
'Loop
rs.Close
Set rs = Nothing

Move the cursor to the current row. I find using the recordset's
filter method is the best way of doing this. Then change the values
for the current row and issue the Update method, using the same as
your existing code.

BTW there's no need to return all rows from table ('optimistic'
doesn't lock the table). Use a SQL SELECT statement with a WHERE
clause to return only the rows you require.

--

"Cindy" wrote in message ...
Good morning,

I have the following code I use to send data from Excel to
and Access database and it works fine. I now need to
update an existing record in the Access database from
Excel and I am not sure how to do that. I assume I can
use the "update" command but don't know where I should put
the criteria to determine what record to update. The
record to update is based on the "PDANum" field.

Any help would be appreciated. Thank you.

Dim response As String
Dim counter As Integer
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As
Long
' connect to the Access database
Set cn = New ADODB.Connection
'cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
' "Data Source=T:\PDA\PDA Request.mdb;"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=T:\PDA\PDA Request.mdb;"
' open a recordset for PDATracking Table
Set rs = New ADODB.Recordset
rs.Open "PDATracking", cn, adOpenKeyset,
adLockOptimistic, adCmdTable
' all records in a table
With rs
.update (assume I use this command rather
than .AddNew)
'.AddNew ' create a new record
' add values to each field in the record
'pulls user id entered on frmPassword
.Fields("UserID") = strUserID
'pulls PDA Status entered on frmPDAStatus
.Fields("PDAStatus") = strPDAStatus
.Fields("Brand") = Range("Brand").Value
.Fields("BuyingGroup") = Range
("BuyingGroup").Value
.Fields("PDANum") = Range("PDANum").Value
.Fields("DateSubmitted") = Range
("DateSubmitted").Value
.Fields("RegMgr") = Range("RegMgr").Value
.Fields("Requestor") = Range("Requestor").Value
.Fields("DistributorLoc") = Range
("DistributorLoc").Value
.Fields("Distributor") = Range
("Distributor").Value
.Fields("DistributorCustNo") = Range
("DistributorCustNo").Value
.Fields("NoLocations") = Range
("NoLocations").Value
.Fields("SalesAmt") = Range("SalesAmt").Value
.Fields("ForecastSalesAmt") = Range
("ForecastSalesAmt").Value
.Fields("DistEmail") = Range("DistEmail").Value
.Fields("PriceQuoteEffectiveDateStart") = Range
("PriceQuoteEffectiveDateStart").Value
.Fields("PriceQuoteEffectiveDateEnd") = Range
("PriceQuoteEffectiveDateEnd").Value
.Fields("PrgmOffInvoice") = Range
("PrgmOffInvoice").Value
.Fields("PrgmFileBack") = Range
("PrgmFileBack").Value
.Fields("Displays") = Range("Displays").Value
.Fields("Advertising") = Range
("Advertising").Value
.Fields("Sample18x24") = Range
("Sample18x24").Value
.Fields("Sample9x12") = Range
("Sample9x12").Value
.Fields("Other") = Range("Other").Value
.Fields("Rebates") = Range("Rebates").Value
.Fields("KeyElement") = Range
("KeyElement").Value
.Fields("JobLock") = Range("JobLock").Value
.Fields("SpecialConditions") = Range
("SpecialConditions").Value
.Fields("RegMgrApproval") = Range
("RegMgrApproval").Value
.Fields("NSMgrApproval") = Range
("NSMgrApproval").Value
.Fields("ApprovalDateRM") = Range
("ApprovalDateRM").Value
.Fields("ApprovalDateNSM") = Range
("ApprovalDateNSM").Value
.Fields("Declined") = Range("Declined").Value
.Fields("DeclinedDate") = Range
("DeclinedDate").Value
.Fields("Reason") = Range("Reason").Value
' add more fields if necessary...
.Update ' stores the new record
End With
' r = r + 1 ' next row
'Loop
rs.Close
Set rs = Nothing