Copy recordset from an Access "make table" query
 

I posted this as an Access question and have received no
responses, so I have decided that maybe this is more of
an Excel programming question. I am hesitant to duplicate
the posting, but I am stumped!

I have an application that has an Excel front-end where
some of the behind the scenes stuff happens in Access.
The user only sees Excel. Up to this point, data is
input and read into simple Access tables.

The latest revision requires some rather complex Access
queries. I have gotten everything working in the queries
as required, but now I need to get them into Excel using
VBA code.

The first thing I have to do is two "Make Table"
Queries. From these two tables, I then run another more
complex query. The make table queries are required every
time this procedure runs.

At this point, I am trying to output the new table to an
Excel Spreadsheet, just to make sure it is working.

The code is:

Set rst2 = New ADODB.Recordset

Src = "SELECT Materials.*, IIf(IsNull
([Materials.Color]),'~',[Materials.Color]) AS ColorMat
INTO tblMaterials FROM Materials;"

With rst2
.Open Source:=Src, ActiveConnection:=cnn
Set WDF = Worksheets("Delivery Forecast")
WDF.Range("A1").Offset().CopyFromRecordset rst2
End With

When I do this I get an error that says "Operation is not
allowed when Object is Closed."

The connection to Access seems fine, or I would not be
able to run the query. The error occurs when I try to
copy rst2 into Excel. Since the worksheet WDF cannot be
the object that the error is referring to, it must be the
recordset "rst2." I am assuming this is happening
because the table is new. How do I open this new table,
so I can copy the recordset? After the procedure has
run, how do I delete the new table?

Thanks,
Laurie

Copy recordset from an Access "make table" query
 

The problem is a SELECT..INTO query does not return a recordset.
Instead, you will have to do it in two stages:

1. Execute the SELECT..INTO against the Connection object;
2. Use a new SELECT query which uses the new table as the recordset's
Source property.

For example:

Src = "SELECT *," & _
" IIf(IsNull(Color),'~',Color) AS ColorMat" & _
" INTO tblMaterials" & _
" FROM Materials"

cnn.Execute Src

Src2 = "SELECT * FROM tblMaterials"

With rst2
.ActiveConnection = cnn
.Source = Src2
.Open
End With

Set WDF = Worksheets("Delivery Forecast")
WDF.Range("A1").Offset().CopyFromRecordset rst2

--

"Laurie" wrote in message ...
I posted this as an Access question and have received no
responses, so I have decided that maybe this is more of
an Excel programming question. I am hesitant to duplicate
the posting, but I am stumped!

I have an application that has an Excel front-end where
some of the behind the scenes stuff happens in Access.
The user only sees Excel. Up to this point, data is
input and read into simple Access tables.

The latest revision requires some rather complex Access
queries. I have gotten everything working in the queries
as required, but now I need to get them into Excel using
VBA code.

The first thing I have to do is two "Make Table"
Queries. From these two tables, I then run another more
complex query. The make table queries are required every
time this procedure runs.

At this point, I am trying to output the new table to an
Excel Spreadsheet, just to make sure it is working.

The code is:

Set rst2 = New ADODB.Recordset

Src = "SELECT Materials.*, IIf(IsNull
([Materials.Color]),'~',[Materials.Color]) AS ColorMat
INTO tblMaterials FROM Materials;"

With rst2
.Open Source:=Src, ActiveConnection:=cnn
Set WDF = Worksheets("Delivery Forecast")
WDF.Range("A1").Offset().CopyFromRecordset rst2
End With

When I do this I get an error that says "Operation is not
allowed when Object is Closed."

The connection to Access seems fine, or I would not be
able to run the query. The error occurs when I try to
copy rst2 into Excel. Since the worksheet WDF cannot be
the object that the error is referring to, it must be the
recordset "rst2." I am assuming this is happening
because the table is new. How do I open this new table,
so I can copy the recordset? After the procedure has
run, how do I delete the new table?

Thanks,
Laurie