Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Help finishing a query

    I have one view being pulled into an Access Database (2013). This view is linked to a SSMS database.

    I set a parameter on it which works fine.

    I also have some VBA to refresh the links, they work fine.

    I am able to run the query from VBA via button

    I am able to save the file using VBA from Access to Excel.

    My questions is, are there anyways to loop through a access query and save your findings?

    I know you can do it in Excel, but Access is a lot more complex.

    Here is what I have, it's not much but it save the query.

    HTML Code:
    Dim strfile As String
    
    strfile = "C:\Users\user\Desktop\tableexport.xls"
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryREPORTDate", strfile
    
    MsgBox "File has been exported to " & strfile
    
    End Sub

    My end game is to loop through the query building a collection or some contain that can be accessed and then output to Excel. Column 1 is the column I am trying to filter by.

    Thanks for any recommendations.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If I understand correctly the question you could use:

    Code:
    Sub LoopThroughQueryDef()
    
        Dim qdf As dao.QueryDef
        Dim rst As dao.Recordset
        Dim var As Variant
        
        ' For a Query using a Stored Procedure
        '
        Set qdf = CurrentDb.QueryDefs("QueryName") ' QueryName is the name of the pass-through query that uses the View.
        
        ' For a Query based on a (linked) Table (SQL Table or View).
        '
        Set qdf = CurrentDb.CreateQueryDef("", "SELECT * FROM TableName;") ' TableName is the name of the (linked) Table.
        
        ' Common code for both kinds of Queries.
        '
        Set rst = qdf.OpenRecordset
        With rst
            Do Until .EOF
                var = .Fields(0).Value
                '
                ' Do whatever you want with the value in the first column of a row.
                '
                .MoveNext
            Loop
            .Close
        End With
        qdf.Close
        Set rst = Nothing
        Set qdf = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id try to avoid iterating through a query, unlezs you have to. What id be tempted to is run a second query which does that for you. But as we dont know your query and we dont know what you are trying to achieve. So its hard to give a better answer.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Sorry for the incoherent babble It was 1 am! ***Note I haven't tried to code I just wanted to follow up.

    So essentially I have a view that I am pulling into Access. I ran a query with a parameter (date range) which chokes down the results to just that date range (EG 2014-11). Once that query returns records, I would like this to save off into excel documents. I can size the ranges and set the naming convention for the file. I am just not sure how to piece the loop with the output together. Nor did I understand how to build the loop with a query that has a parameter set in access.

    The one good thing is the query isn't a pass through, it's linked to a linked table/view in Access itself.

    End game is to save the results from the loop into excel.

    Thanks I'll try the code now with mine.
    Last edited by VLOOKUP; 11-07-14 at 10:56.

Posting Permissions

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