Running UPDATE and APPEND queries from Access in Excel
 

I use the code below to run an Access DB SELECT Query and place it in an
Excel Worksheet. Is it possible to adapt this code to run APPEND or UPDATE
queries using Excel's VBA Editor, and if so how do I go about the revisions?

Sub accessQuery(vSQL As String, strTab As String)
If conn Is Nothing Then Set conn = New ADODB.Connection
If conn.State = adStateClosed Then 'if not active
With conn
.Provider = "Microsoft.jet.oledb.4.0"
.Mode = adModeShareDenyWrite
.Open data1
End With
End If

Set rs = New ADODB.Recordset
rs.Open vSQL, conn, adOpenStatic, adLockReadOnly

Sheets(strTab).Activate
sglRows = 2
Cells(sglRows, 1).CopyFromRecordset rs
Range("A2").Select

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub

Running UPDATE and APPEND queries from Access in Excel
 

http://erlandsendata.no/english/inde...badacexportado

This shows how to append data to an Access Table from Excel using VBA.
Re an update query, you can probably figure out how to loop through
the recordset and update the fields you want from Excel data.

Hth,
Merjet