Creating a new table from a recordset in vba (1 Viewer)

mikewood1980

Registered User.
Local time
Today, 13:23
Joined
May 14, 2008
Messages
45
Hi

I have created a new recordset in my VBA code using a query (i.e. set rs = dbcurr.openrecordset(sQuery) - where sQuery is the query string).

is the any way of saving the reultant recordset as a new table?

Thanks for your help

Mike
 

namliam

The Mailman - AWF VIP
Local time
Today, 14:23
Joined
Aug 11, 2003
Messages
11,696
Run a make table query... something like so:

currentdb.execute "Create table somename as " & sQuery
 

mikewood1980

Registered User.
Local time
Today, 13:23
Joined
May 14, 2008
Messages
45
Run a make table query... something like so:

currentdb.execute "Create table somename as " & sQuery

Hi - thanks for that!

I tried the following code but get a syntax error.... (Run Time Error 3290 - Syntax error in CREATE TABLE statement)

Code:
sQuery = "select * from tblLining where ((tblLining.chrOuterBoard) = 'Duraline 13mm' or (tblLining.chrOuterBoard) = 'Fireline 15mm' or (tblLining.chrOuterBoard) = 'Gyproc Multiboard 12.5mm' or (tblLining.chrOuterBoard) = 'Soundbloc 15mm')"

CurrentDb.Execute "Create table tblTEMP as " & sQuery
the query (sQuery) was executing fine before :)

Any ideas?

Thanks
Mike
 

namliam

The Mailman - AWF VIP
Local time
Today, 14:23
Joined
Aug 11, 2003
Messages
11,696
OK, *UHM* to much oracle on the brain, sorry.
The syntax for this in Access is a little more complex (unfortunatly)
Select... Into tblTemp from tablename where....

so... You have to cut and paste the sQuery a little bit find " From " and insert tblTemp" into the query. Then execute it.
 

mikewood1980

Registered User.
Local time
Today, 13:23
Joined
May 14, 2008
Messages
45
Thanks namliam! Any chance you could show me how to delete the table too ;)

Thanks again!
 

namliam

The Mailman - AWF VIP
Local time
Today, 14:23
Joined
Aug 11, 2003
Messages
11,696
Why save and drop the table??

Check out the help on: Docmd.DeleteObject
 

Users who are viewing this thread

Top Bottom