Solved

MS Access VBA - Run Executable Query Based Upon VBA Recordset

Posted on 2007-11-14
3
13,549 Views
Last Modified: 2013-11-27
I have the following:

strSQL = "Select a ton of stuff"
Set rstTonOfStuff = db.OpenRecordset(strSQL)

db.Execute "Insert Into table (field, field...) Select " &  rstTonOfStuff!Field & " , " &  rstTonOfStuff!Field & " , " &  rstTonOfStuff!Field & " & _
" From " & rstTonOfStuff & ""

I'm trying to use a VBA recordset as a basis for a secondary VBA query. Please disregard any syntax errors with the above; it's a representation of what I'd like to get working.

Is it possible to use a VBA recordset as the bases for a secondary VBA query?

Thank you.
0
Comment
Question by:advlegals
3 Comments
 
LVL 5

Accepted Solution

by:
tygrus2 earned 250 total points
Comment Utility
Using "db.OpenRecordset(strSQL)" creates a temporary object and connection to read records from the datadase.
You can create and save a Query in VBA using the "CreateQueryDef" method of the database object.
------
strSQL = "SELECT ..."
Debug.Print strSQL
DB.CreateQueryDef("NewQueryName", strSQL)
'Do tasks that reference "NewQueryName" query or use the "CreateTableDef" for repeated use.
'Can create 'insert', 'update' and 'delete' queries for 'db.execute' or 'querydef.execute'.
DB.QueryDefs.Delete "NewQueryName" 'It doesn't have to last forever
------
Check the Microsoft help&examples regarding "CreateQueryDef" et. al.
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 250 total points
Comment Utility
All you're ultimately doing is building a SQL statement.
It's entirely possible - the fact that you're using values from a recordset it almost irrelevant.
Since it's concept code above there's no way to be sure - but are you properly delimiting your field types in the Selection clause of your Insert statement?

i.e. you'll perhaps want to end up executing a string which looks like
"Insert Into table (field, field2, field3) Select 'Val1' , 'Val2' , 'Val3'"

So your concept code would be just
db.Execute "Insert Into table (field, field2, field3) Select '" &  rstTonOfStuff!Field & "' , '" &  rstTonOfStuff!Field & "' , '" &  rstTonOfStuff!Field & "'"

As you're providing values (from the recordset) - rather than making a selection from a table.

What you can't do is insert en masse from the recordset.
You work from a row at a time.
If you want to operate on multiple rows - you'd need the selection to be from a table or query which can be referenced in the statement.
0
 

Author Closing Comment

by:advlegals
Comment Utility
Thanks chaps.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now