calling a stored access query from excel vba
 

I have a query already written inside of Access 2002 which I am trying to get
a recordset into Excel. Does anyone know if its possible to do this? I could
pass the SQL syntax into the connection object, but it's a pretty lengthy
query.This is what I have so far, but I get an error that says operation is
not supported for this type of object....
DbPath = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.mdb;"
Set ConnectionVariable = New ADODB.Connection
ConnectionVariable.ConnectionString = DbPath
ConnectionVariable.Open
Set cmd = New ADODB.Command
QueryString = "QueryName"
Set cmd.ActiveConnection = ConnectionVariable
With cmd
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = QueryString
End With
Set oRecordset = New ADODB.Recordset
Set oRecordset = cmd.Execute
Set Output = Worksheets("Worksheet").Range("A2")
If oRecordset.EOF < True Then
oRecordset.MoveFirst
End If
Output.CopyFromRecordset oRecordset

Thanks,

Travis

calling a stored access query from excel vba
 

Hi Travis,

DbPath = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.mdb;"
Set ConnectionVariable = New ADODB.Connection
ConnectionVariable.ConnectionString = DbPath
ConnectionVariable.Open
Set cmd = New ADODB.Command
QueryString = "QueryName"
Set cmd.ActiveConnection = ConnectionVariable
With cmd
.Properties("Jet OLEDB:Stored Query") = True
.CommandText = QueryString
End With
Set oRecordset = New ADODB.Recordset
Set oRecordset = cmd.Execute
Set Output = Worksheets("Worksheet").Range("A2")
If oRecordset.EOF < True Then
oRecordset.MoveFirst
End If
Output.CopyFromRecordset oRecordset

I'd do it just using the recordset, rather than the command:

oRecordset.Open "QueryName", ConnectionVariable

The only time I'd go for the command is when needing parameters.

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev