Running UPDATE and APPEND queries from Access in Excel

A

Amery

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top