Import multiple Access Queries

G

garygoodguy

Hi, I have some vba code that pulls in a MS Access db query. It work
well, but my question is how can I alter the code to allow for multipl
queries to be imported from the same macro? Ideally I'd like to hav
another couple of queries (from the same db) pulled into separat
worksheets but WHERE statement will always be the same (i.e. SL ='"
Sheets("Start").Range("K41").Value & "'")

Here's the code:
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim MyConn
Dim i As Long
Dim ShDest As Worksheet
Dim sSQL As String

Set ShDest = Sheets("FTEGet")

sSQL = "SELECT * FROM qryFTE WHERE SL ='"
Sheets("Start").Range("K41").Value & "'"

Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic
_
Options:=adCmdText

'clear existing data on the sheet
ShDest.Activate
Range("A1").CurrentRegion.Clear

'transfer data to Excel
Range("A1").CopyFromRecordset rst

'Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

Thanks in advance. Cheers
 

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