MS Access VBA - Run Executable Query Based Upon VBA Recordset

Posted on 2007-11-14
Medium Priority
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.
Question by:advlegals

Accepted Solution

tygrus2 earned 1000 total points
ID: 20285198
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.
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1000 total points
ID: 20285240
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.

Author Closing Comment

ID: 31409250
Thanks chaps.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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