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
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