Export Access VBA Query to CSV

Posted on 2013-09-27
Medium Priority
Last Modified: 2013-10-02
Hi Experts,

I get a csv file from my company's bank that contains daily activity for 50+ bank accounts.  I import this file into MS Access.  I am trying to figure out if there is a way to filter this table based on account nbr and export that filtered data to an CSV file that I can share with others in my company.

I know that I can create a query and then export the results to a CSV file, but I'm not too crazy about having all those queries in my database.  I was wondering if there was a way to use the RunSQL command in VBA to create a filtered set of data and then export the results to a CSV.
Question by:JJINFM
LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 668 total points
ID: 39527324
<< RunSQL command in VBA to create a filtered set of data and then export the results to a CSV. >>

  Sure, you could empty and fill a temp table and export that.   You also could just open a recordset in code and write the CSV directly.

 But both those are extra work.  Really no reason why you could not use a query on the imported table and export off that.

LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 668 total points
ID: 39527325
Using  QueryDef may be an option

dim qd as dao.querydef ' Make sure that Microsoft DAO checked on under Tools/References!

set qd = currentdb.querydefs("your_query_name")
qd.SQL = "select x from tblY where BANKID=" & BankID_variable

docmd.transfertext ... "your_query_name"
LVL 41

Assisted Solution

PatHartman earned 664 total points
ID: 39528294
To expand on the variable in the query.  The best way to do that is to use a form field so you won't get prompted.

1. Create a form.  Name it frmExport
2. Add a combo or text box.
3. Give it an appropriate name such as txtBankID or cboBankID
4. Create a query that references that control
Select ...
From ...
Where BankID = Forms!frmExport!cboBankID OR Forms!frmExport!cboBankID Is Null;

Just make sure you populate the form field with BankID before you run the export if you want only a single ID.   The OR condition allows the query to export all ids if you want it to.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question