If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: run query in vba

is there a way to run a query in vba. I don't mean "do.cmd openquery..." I mean define a query (Select * FROM tbl...) and then run it? I need to be able to define a WHERE statement in a query... Any ideas?

yeah- i stumbled on that problem already- Is there anything I can do to just run a simple query?

I guess I can use the 'Do.CMD openquery' command if there was a way to feed a WHERE stament in there. I need to run a query 56 times- each one will require a different location number. I hate to have to write 56 queries that are all duplicate except for the location number.

I am trying to feed an array of location numbers into a query- is that possible?

Could you not have a loop in your VBA?
Have the code find the number it needs and set that number.
Have the code open the query.
have the query refrence the form the code is attached to. (where num is forms!...)

Just a an initial thought of one ready for the holidays.
Maybe if you let us know more distinctly what you are rying to accomplish there are ways around what you need.

OK- Here's the whole story... I have a query that i need to transfer out of access and into excel. I send these spreadsheets (56 of them) to different files on our server that people all over the country can use. I have been manually doing this for about a week and it takes to long. I wrote some code in VBA that will open 56 different queries and send them to 56 different locations. That was easy enough. But we will be adding about 100 more locations so that means i would have to write 100 more queries for my 'looping code' to run through. I would rather include in the LOOP a section that references ONE query and just loops the location number.

Here's the short version; I need to figure out how to create a query (in VBA) that will allow me to manipulate the WHERE part of the SQL. I can then loop the location number in the SQL... right? HELP.

I think i understand the logic here- This looks like what I need. What i would like to do with the recordset as soon as it finds that specific location#'s stuff is send it to a spreadsheet. How would i reference this temporary recordset to be able to send it to a spreadsheet.

cc3658
Feetdontfailme in my view is in the right area....
Depends on how you are doing your excel export, the runsql commands have there uses but, as you are already finding they have their limitations. It sounds to me as if you are going to have to use the increased power and flexibility from using DAO, or ADO recordsets. It may sound daunting at first but if you are going to be increasing the number of sheets that are being exported (or imported) then for you own sanity start reading up on recordsets.

You can achieve what you want using Recodsets. Fundamentally you can write the SQL used in the queries on the fly, so you no longer need to have more than one extract query (in fact you don't actually need any queeries VBA can create the native SQL). However lets assume that you are doing an import/export of sales data, and the only thing that changes in the sales person ID and physical location then you could supply these as parameters to the query.