Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    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?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    DoCmd.RunSQL "SELECT * FROM ... WHERE ( ... );"

    BUT, RunSQL requires an action query ...
    Last edited by M Owen; 12-22-04 at 14:25.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    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?

  4. #4
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Warning: A Bad answer follows.

    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.
    Darasen

  5. #5
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Red face

    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.

  6. #6
    Join Date
    Dec 2004
    Posts
    4
    I think I understand what you are trying to do. See if this helps any.

    Code:
    Dim rst As New ADODB.Recordset
    
    For location = 1 To 100 'Substitute 100 with the highest location number 
         strSQL = "Select * From YourTable Where YourLocationField = " & location
         rst.Open strSQL, CurrentProject.Connection, adOpenStatic
         'Do what ever you want with the results here
         rst.Close
         Set rst = Nothing
    Next
    That will change the location number each time it goes through the for loop. Not sure if that was what you were looking for, but good luck!

  7. #7
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    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.

    by the way- Thanks for your response!

  8. #8
    Join Date
    Nov 2002
    Posts
    154

    Wink Look into the ADODB Command object also.

    You could try something like this:

    Dim cmd As ADODB.Command
    Dim strSQL As String
    Dim lngRecs As Long

    Set cmd = New ADODB.Command

    strSQL = "your SQL statement"

    Set cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = strSQL
    cmd.CommandType = adCmdText

    cmd.Execute lngRecs, , adExecuteNoRecords
    Set cmd = Nothing

    if you want to do this for an already made query cmd.commandtext = the name of the query and cmd.commandtype = adcmdstoredprocedure. Good luck to you.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    Last edited by healdem; 12-23-04 at 05:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •