Export Access VBA Query to CSV

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Barry CunneyCommented:
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"
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.