Hi, I have the vba code below to run a sql query off an Access
database.... what I would like to do is change this code so that it
can run off a table of data held in an excel file. Is this possible?

Essentially, due to our fantastic network, I need to make one call to
the database to get the full pipeline data and then run 4 SQL queries
off the retrieved data. Each call to the data base takes about 5mins
(due to network performance), so I was hoping to do one call and then
manipulate in excel.

Thanks

Dim vConnection As New ADODB.Connection
Dim rsPubs As ADODB.Recordset
Dim db As String
Set rsPubs = New ADODB.Recordset
Set vConnection = New ADODB.Connection
db = "data source=J:\Pipeline.mdb;"

vConnection.ConnectionString = db & "Provider=Microsoft.Jet.OLEDB.
4.0;"
vConnection.Open

Sql1 = "SELECT x "
Sql2 = "FROM y "
Sql3 = "WHERE z; "

Sql = Sql1 + Sql2 + Sql3

Debug.Print (Sql)
' vRecordSet.Open sql
With rsPubs
' Assign the Connection object.
.ActiveConnection = vConnection
' Extract the required records.
.Open Sql
Worksheets("All pipeline").Range("b7:iv65536").ClearContents
Worksheets("All pipeline").Range("b7").CopyFromRecordset
rsPubs
.Close

End With

vConnection.Close
Set rsPubs = Nothing
Set vConnection = Nothing

Is this what you are looking for? Not sure if I got the columns correct.

Sub test()

Dim vConnection As New ADODB.Connection
Dim rsPubs As ADODB.Recordset
Dim db As String

Worksheets("All pipeline") _
.Range("b7:iv65536").ClearContents


Set rsPubs = New ADODB.Recordset
Set vConnection = New ADODB.Connection
db = "data source=J:\Pipeline.mdb;"

vConnection.ConnectionString = _
db & "Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open

With Sheets("Sheet1")
First = True
RowCount = 1
Do While .Range("B" & RowCount) < ""

MyItem = .Range("B" & RowCount)

Sql1 = "SELECT x "
Sql2 = "FROM y "
Sql3 = "WHERE " & MyItem & "; "

Sql = Sql1 + Sql2 + Sql3

Debug.Print (Sql)
' vRecordSet.Open sql
With rsPubs
' Assign the Connection object.
.ActiveConnection = vConnection
' Extract the required records.
.Open Sql
With Worksheets("All pipeline")
If First = True Then
NewRow = 7
First = False
Else
LastRow = _
.Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End If
.Range("B" & NewRow).CopyFromRecordset rsPubs
End With

.Close

End With
RowCount = RowCount + 1
Loop
End With
vConnection.Close
Set rsPubs = Nothing
Set vConnection = Nothing
End Sub


"macroapa" wrote:

Hi, I have the vba code below to run a sql query off an Access
database.... what I would like to do is change this code so that it
can run off a table of data held in an excel file. Is this possible?

Essentially, due to our fantastic network, I need to make one call to
the database to get the full pipeline data and then run 4 SQL queries
off the retrieved data. Each call to the data base takes about 5mins
(due to network performance), so I was hoping to do one call and then
manipulate in excel.

Thanks

Dim vConnection As New ADODB.Connection
Dim rsPubs As ADODB.Recordset
Dim db As String
Set rsPubs = New ADODB.Recordset
Set vConnection = New ADODB.Connection
db = "data source=J:\Pipeline.mdb;"

vConnection.ConnectionString = db & "Provider=Microsoft.Jet.OLEDB.
4.0;"
vConnection.Open

Sql1 = "SELECT x "
Sql2 = "FROM y "
Sql3 = "WHERE z; "

Sql = Sql1 + Sql2 + Sql3

Debug.Print (Sql)
' vRecordSet.Open sql
With rsPubs
' Assign the Connection object.
.ActiveConnection = vConnection
' Extract the required records.
.Open Sql
Worksheets("All pipeline").Range("b7:iv65536").ClearContents
Worksheets("All pipeline").Range("b7").CopyFromRecordset
rsPubs
.Close

End With

vConnection.Close
Set rsPubs = Nothing
Set vConnection = Nothing