Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 08-14-2008, 06:13 AM   #1
mikewood1980
Registered User
 
Join Date: May 2008
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
mikewood1980 is on a distinguished road
Creating a new table from a recordset in vba

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

mikewood1980 is offline   Reply With Quote
Old 08-14-2008, 06:24 AM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 798 Times in 785 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Run a make table query... something like so:

currentdb.execute "Create table somename as " & sQuery
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 08-14-2008, 07:59 AM   #3
mikewood1980
Registered User
 
Join Date: May 2008
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
mikewood1980 is on a distinguished road
Quote:
Originally Posted by namliam View Post
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

mikewood1980 is offline   Reply With Quote
Old 08-14-2008, 09:20 AM   #4
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 798 Times in 785 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
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.
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 08-14-2008, 09:35 AM   #5
mikewood1980
Registered User
 
Join Date: May 2008
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
mikewood1980 is on a distinguished road
Thanks namliam! Any chance you could show me how to delete the table too

Thanks again!
mikewood1980 is offline   Reply With Quote
Old 08-14-2008, 12:10 PM   #6
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 798 Times in 785 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Why save and drop the table??

Check out the help on: Docmd.DeleteObject

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I query a recordset object in VBA? jc1104 Queries 5 08-15-2006 07:56 AM
Creating Table to Include Linked Tale Data mjm01 Tables 1 07-02-2003 05:55 AM
Automatically creating table using VB? Tawcan Tables 12 04-15-2003 02:22 PM
[SOLVED] Updating Table Via VBA Help TimH Modules & VBA 2 01-09-2003 12:26 PM




All times are GMT -8. The time now is 06:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World