Copying recordset from a "make table" query

  • Thread starter Thread starter Laurie
  • Start date Start date
L

Laurie

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
 
Laurie said:
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
Click to expand...

You are trying to open a recordset on a make-table query, but make-table
queries don't return recordsets. If you don't actually need to create
this last table, you could just open your recordset on a SELECT query:

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

With rst2
.Open Source:=Src, ActiveConnection:=cnn

' ... and so on.

If you do need to create that last table, tblMaterials, then first run
the make-table query as a Command, and then open the recordset just on
tblMaterials, or on a simple SELECT from tblMaterials.
 
Back
Top