Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling a common XLA Library file, stored on a network drive :) | Excel Discussion (Misc queries) | |||
Calling a common XLA Library file, stored on a network drive :) | Setting up and Configuration of Excel | |||
Problem Code: Retrieving Stored Access 03 Query | Excel Discussion (Misc queries) | |||
calling an MS Access query from within VBA for Excel | Excel Programming | |||
calling a stored procedure on MS SQL Server within MS Excel 2000 | Excel Programming |