I have a VBA question. In my program, I create a recordset through SQL
(contained in strSQL) as follows:

Set rstSQL = myODBC.OpenRecordset(strSQL)

Now I would like to append the result of this recordset to a table in
Access. Is there an efficient way to do this? For example:
db.INSERT rstSQL into tblResult (obviously this won't work, but it's what I
want!)

Thanks,
George

You don't need the select query you are using. Make it into an append query
that pulls the records based on the criteria in your select query and appends
to your Access table.

So instead of:
Set rstSQL = myODBC.OpenRecordset(strSQL)
You would need
Currentdb.Execute(newSQL)

"George" wrote:

> I have a VBA question. In my program, I create a recordset through SQL
> (contained in strSQL) as follows:
>
> Set rstSQL = myODBC.OpenRecordset(strSQL)
>
> Now I would like to append the result of this recordset to a table in
> Access. Is there an efficient way to do this? For example:
> db.INSERT rstSQL into tblResult (obviously this won't work, but it's what I
> want!)
>
> Thanks,
> George
>

The query I have defined in strSQL is a pass-through query to a DB2 database.
Access cannot run it and an append query at the same time. Is there another
way?

Thanks

"Klatuu" wrote:

> You don't need the select query you are using. Make it into an append query
> that pulls the records based on the criteria in your select query and appends
> to your Access table.
>
> So instead of:
> Set rstSQL = myODBC.OpenRecordset(strSQL)
> You would need
> Currentdb.Execute(newSQL)
>
> "George" wrote:
>
> > I have a VBA question. In my program, I create a recordset through SQL
> > (contained in strSQL) as follows:
> >
> > Set rstSQL = myODBC.OpenRecordset(strSQL)
> >
> > Now I would like to append the result of this recordset to a table in
> > Access. Is there an efficient way to do this? For example:
> > db.INSERT rstSQL into tblResult (obviously this won't work, but it's what I
> > want!)
> >
> > Thanks,
> > George
> >

George wrote:
> The query I have defined in strSQL is a pass-through query to a DB2
> database. Access cannot run it and an append query at the same time.
> Is there another way?

Use the Pass-Through as the input to the append query.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Sorry, I should have known. But, I don't think there is that big a problem.
A query can be based on a query. Have you tried creating the append query
and use your pass-through as the source?

"George" wrote:

> The query I have defined in strSQL is a pass-through query to a DB2 database.
> Access cannot run it and an append query at the same time. Is there another
> way?
>
> Thanks
>
> "Klatuu" wrote:
>
> > You don't need the select query you are using. Make it into an append query
> > that pulls the records based on the criteria in your select query and appends
> > to your Access table.
> >
> > So instead of:
> > Set rstSQL = myODBC.OpenRecordset(strSQL)
> > You would need
> > Currentdb.Execute(newSQL)
> >
> > "George" wrote:
> >
> > > I have a VBA question. In my program, I create a recordset through SQL
> > > (contained in strSQL) as follows:
> > >
> > > Set rstSQL = myODBC.OpenRecordset(strSQL)
> > >
> > > Now I would like to append the result of this recordset to a table in
> > > Access. Is there an efficient way to do this? For example:
> > > db.INSERT rstSQL into tblResult (obviously this won't work, but it's what I
> > > want!)
> > >
> > > Thanks,
> > > George
> > >

Assign your 1st SQL to a named querydef rather than a recordset. Then create
a 2nd SQL string that appends the querydef to your table?

--
George Nicholson

Remove 'Junk' from return address.


"George" <(E-Mail Removed)> wrote in message
news:618780E3-EDCB-4AD0-A337-(E-Mail Removed)...
> The query I have defined in strSQL is a pass-through query to a DB2
> database.
> Access cannot run it and an append query at the same time. Is there
> another
> way?
>
> Thanks
>
> "Klatuu" wrote:
>
>> You don't need the select query you are using. Make it into an append
>> query
>> that pulls the records based on the criteria in your select query and
>> appends
>> to your Access table.
>>
>> So instead of:
>> Set rstSQL = myODBC.OpenRecordset(strSQL)
>> You would need
>> Currentdb.Execute(newSQL)
>>
>> "George" wrote:
>>
>> > I have a VBA question. In my program, I create a recordset through SQL
>> > (contained in strSQL) as follows:
>> >
>> > Set rstSQL = myODBC.OpenRecordset(strSQL)
>> >
>> > Now I would like to append the result of this recordset to a table in
>> > Access. Is there an efficient way to do this? For example:
>> > db.INSERT rstSQL into tblResult (obviously this won't work, but it's
>> > what I
>> > want!)
>> >
>> > Thanks,
>> > George
>> >

Thanks for the idea - all three replies suggest the same approach: define an
append query that uses the pass-through query as the source. In VBA, how do
I do this with recordsets? I already have rstSQL.

Something like this?:
strSQL2 = "SELECT * FROM " & rstSQL & " INTO [TblResult]"
db.Execute(strSQL2)

Thanks

"Klatuu" wrote:

> Sorry, I should have known. But, I don't think there is that big a problem.
> A query can be based on a query. Have you tried creating the append query
> and use your pass-through as the source?
>
> "George" wrote:
>
> > The query I have defined in strSQL is a pass-through query to a DB2 database.
> > Access cannot run it and an append query at the same time. Is there another
> > way?
> >
> > Thanks
> >
> > "Klatuu" wrote:
> >
> > > You don't need the select query you are using. Make it into an append query
> > > that pulls the records based on the criteria in your select query and appends
> > > to your Access table.
> > >
> > > So instead of:
> > > Set rstSQL = myODBC.OpenRecordset(strSQL)
> > > You would need
> > > Currentdb.Execute(newSQL)
> > >
> > > "George" wrote:
> > >
> > > > I have a VBA question. In my program, I create a recordset through SQL
> > > > (contained in strSQL) as follows:
> > > >
> > > > Set rstSQL = myODBC.OpenRecordset(strSQL)
> > > >
> > > > Now I would like to append the result of this recordset to a table in
> > > > Access. Is there an efficient way to do this? For example:
> > > > db.INSERT rstSQL into tblResult (obviously this won't work, but it's what I
> > > > want!)
> > > >
> > > > Thanks,
> > > > George
> > > >