Solved

MS Access VBA - Run Executable Query Based Upon VBA Recordset

Posted on 2007-11-14
3
13,551 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
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.
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 250 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.
0
 

Author Closing Comment

by:advlegals
ID: 31409250
Thanks chaps.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to execute an access 2007 module from vb6 2 34
Modal form 11 29
update all email addresses SQL 1 23
Experience with VBA code that changes unexplicably? 8 22
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

785 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