?
Solved

Creating a CSV File from a SQL Table via a MS Access Form

Posted on 2012-03-20
11
Medium Priority
?
339 Views
Last Modified: 2012-08-14
I have a MS Access Form that add, updates data in a SQL Table.  On the form, I want to place a button that <WHEN PRESSED> will query the table and create a CSV file with the information in the table.  How can I do this?
0
Comment
Question by:Elroy Taulton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37742927
You first have to manually export the table and click the "Advanced" button (during the Export Wizards steps) and create an Export Specification.

Then use code like this on your button:
DoCmd.TransferText acExportDelim, "YourExportSpecificationName", "YourTableName", "C:\YourFolder\Yourfile.csv"
0
 

Author Comment

by:Elroy Taulton
ID: 37742961
Please keep in mind that we are trying to create this form for my end-user group who will not have a clue on how to manually export a table.  We are hoping to have all of this embedded in the actions of the button. (IE, it exports the data and places it in the appropriate location.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743051
The export needs only to be done once (probably by you).
The manual Export is only needed to create the Export Specification.

After the Export Spec is created you simply run the code I posted on a button...
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Elroy Taulton
ID: 37743167
Okay, understand.  Is there a way to format the output?  For example, I would like the export file data to be a specific order, not necessarily how they are laid out in the database table.  Also, I would like to present only returned without any additional spaces, if possible.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743195
The data will be in the text file in the same order it is in the table.

If you want to sort the text file in a specific order, you can create a sorted query, then export the query..
<Also, I would like to present only returned without any additional spaces, if possible.>
It is not clear what this means...?


Try it first, then report back...
0
 

Author Comment

by:Elroy Taulton
ID: 37743695
Perfect.  The sorted query worked great and I now have the items in the order that I need.  The only other item is that each field is a set length and is seperated by a 'dashed line'.  My intent for this file is to use it as an import into another application so if there is any way to provide just the data without any leading or following blank spaces.  Thoughts?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 37743745
That seems to be an issue either with the source data, or the way you set up the import Spec.
0
 

Author Comment

by:Elroy Taulton
ID: 37744789
Okay. lots of progress so far.  Looks like I was creating a formatted file so I have fixed that and am now created a file on button click.  However, the file I am creating is coming out with comma's as my delimiter instead of pipes, even though I changed it to pipes and am calling the export file specifications.  See below.  Any thoughts?

Private Sub cmdExportButton_Click()

DoCmd.TransferText acExportDelim, ZMatrixTable_Export, "ZMatrixTable Query", "C:\Work\Projects\Export\ZMatrix.txt"

End Sub

Also, is there a way to present a message back stating that the export has taken place and/or completed?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37745657
<Any thoughts?>
Can't know without a sample database...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.



<Also, is there a way to present a message back stating that the export has taken place and/or completed?>
This is really getting away from the original question, ...lets get the export working first.

JeffCoachman
0
 

Author Closing Comment

by:Elroy Taulton
ID: 37745668
I have been able to successfully create the CSV file in the proper format.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37748690
;-)
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 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