• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13587
  • 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.
0
advlegals
Asked:
advlegals
2 Solutions
 
tygrus2Commented:
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
 
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.
0
 
advlegalsAuthor Commented:
Thanks chaps.
0
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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