VB Access Query/Export Loop

[pliskers]

I have an Access file that contains a Data table (which includes a Cost Center field), as well as another table called Cost Center, which contains a list of certain Cost Centers for which I'd like to extract and export all fields of data from the Data table.

Instead of writing a series of queries, one for each item in the Cost Center table, is there a loop that could be used? I would join the Cost Center table to the Data table, and just want to export to Excel the full contents of the Data table, creating a separate Excel file containing the records applying to each Cost Center. The Tables could be just named after the cost centers, which for purposes of my question could just be numbered 1,2,3, etc.

Would appreciate any help ASAP. This is a time sensitive project and I'm trying to save steps and time.

Thanks in advance!

 

[SydneyGeek]

Hi Jeff,

Here's a generic routine -- adjust to suit your tables / fields.

qryExport can be anything to start with; built a single-field query on a small table, and save it
In the same folder as the database, create a new ExportCheck directory
Place this code in a new module (Alt+F11, Insert > module, and paste).
Tools > References, and check the reference to Microsoft DAO 3.6 Object Library (it should be near the top of the list, with a tick; if not, scroll down and select it)

Change the code for your tables / fields. The query in this case filters on a text string. If you are using numbers, change this

            sQry = "SELECT * FROM SomeTable WHERE YourID='" & !YourID & "'"

to this

            sQry = "SELECT * FROM SomeTable WHERE YourID=" & !YourID

Sub QueryLoop()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim sQry As String
    Dim sPath As String
    
    sPath = CurrentProject.Path & "\ExportCheck\"
    
    Set dbs = DBEngine(0)(0)
    sSQL = "SELECT DISTINCT YourID FROM SomeTable"
    Set rst = dbs.CreateQueryDef("", sSQL).OpenRecordset
    With rst
        .MoveFirst
        Do Until .EOF
            sQry = "SELECT * FROM SomeTable WHERE YourID='" & !YourID & "'"
            dbs.QueryDefs("qryExport").SQL = sQry
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
                "qryExport", sPath & !YourID & ".xls", True
            .MoveNext
        Loop
    End With
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Sub

On a table with about 5K rows this created 100 workbooks in a touch over a minute.

Denis

 

[pliskers]

Many thanks! I will try this at work tomorrow. Just wanted to mention that I'm running Excel 2003. If that affects any portion of the code, please let me know.

I will be filtering on a text field, so will leave the code as you wrote it. Will reach out if I have any problems with it, but thanks so much for your quick and helpful reply.

Jeff

 

[pliskers]

Just as a follow up - I want to be sure I was clear about the fact that there will be a join in this query, between the Data table and the Cost Center table that contains the list of Cost Centers for which I'll be producing the series of exported files. Does your code take that into account?

Thanks again,

 

[SydneyGeek]

The join will only matter if the filter field is present in both tables. Then you will need to specify the table as well;

            sQry = "SELECT * FROM SomeQuery WHERE [FirstTable].YourID='" & !YourID & "'"

Denis

 

[pliskers]

Thanks again, I'll follow your example and give it a try.