Import Queries instead of Tables (ADO)

[George]

Hi, I am working on a project in Excel that imports from
Access. I have a code that imports tables to excel but
what I really need is to import the reports or the queries
of this database. If someone know a way to do this pls let
me know.

 

[Frank Kabel]

Hi George:
- for queries: what do you want to import: the result? if yes simply
use the query name as source (instead of the table)

- for the report: What exactly do you want to nimport: the layout?
(IMHO not possible)

 

[George]

For the queries is the results and the tittles of each
colums and for the reports, yes is the layout but if I can
get the queries is better. I tried changeing the name of
the table with one of the queries and it gives me an error
that the file is not available.

 

[George]

The exact erro is: Run-time error ...
The Microsoft Jet database engine cannot find the input
table or query'my queries name'. Make sure it existe and
that its name is spelled correctly.

I made sure that it was correct and it dosent work for the
queries.

 

[Dick Kusleika]

George

Post the code you are using. This works OK for me

Sub ImpQry()

Dim cn As ADODB.Connection
Dim sConSt As String
Dim Rs As ADODB.Recordset

sConSt = "DSN=MS Access 97 Database;DBQ=c:\Dick\db1.mdb;"

Set cn = New ADODB.Connection
Set Rs = New ADODB.Recordset

cn.Open sConSt

Rs.Open "Query3", cn

Sheet2.Range("a1").CopyFromRecordset Rs

Rs.Close
cn.Close

Set Rs = Nothing
Set cn = Nothing

End Sub