Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009

    Exclamation creating batches - please help

    I have a large client database of 53000, and currently i have the need to split these into batches of 400 and export to an excel sheet or another table in the database.

    Please help.

  2. #2
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Custer, WA, USA
    One approach you could take is to add a yes/no field to your client table that records whether that client has been exported.

    Then create a select query (let's call it qryExportList) that goes something like

    Select Top 400 ClientID, ClientFName, ClientLName, (add in all the other fields you want here, seperated by commas)
    From tblClients
    Where [Client_Exported]=0
    Order By [ClientID];
    Then, use the docmd.transferspreadsheet vba command to export the results of that query to a spreadsheet. You may wish to prompt the user to provide a filename to be used for the export. Checkout access help on how to use this vba function, or do a google search.

    Then immediately followup that export event by running an update query that goes something this:

    Update [tblClients] 
    INNER JOIN [qryExportList] ON [tblClients].[ClientID] = [qryExportList].[ClientID]
    SET [tblClients].[Client_Exported] = -1;
    Then, after you have run the update query, repeat the process and you should get the next 400 clients.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums