• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13578
  • Last Modified:

MS Access VBA - Run Executable Query Based Upon VBA Recordset

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.
2 Solutions
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.
Leigh PurvisDatabase DeveloperCommented:
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.
advlegalsAuthor Commented:
Thanks chaps.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now