Export Current Form to CSV - VBA question

Posted on 2010-11-08
Last Modified: 2012-05-10
I have access 2007. Capricorn1 did a great job helping me export a current form using two queries to csv, one of which was a subform.  I had a second subform in the stripped down form of the database.  Tried to modify the vba script (as seen below), but I keep getting an error, "the text file specification 'QryCSVSubform2 Export Specification' does not exist. You cannot import, export, or link using the specification.

I'm a bit of a noob, and feel like an idiot for not being able to solve this.

Attached is the modified database Cap1 worked on, as well as the script I tried to modify (which is basically adding Subform2.

Form: Assistance Request Form (vba attached to CSV button on top)
Queries: qryCSVMAIN, qryCSVSubform, qryCSVSubform2

Thank you.

Option Compare Database
Option Explicit
Private Sub cmdExportAsCSV_Click()
Dim qdMain As DAO.QueryDef, qdSub As DAO.QueryDef, qdSub2 As DAO.QueryDef, db As DAO.Database, varID As Long
Dim omSql As String, osSql As String, os2Sql As String, strWhere As String, strWhere1 As String, strWhere2 As String
omSql = "SELECT Assistance_Request.Assistance_ID, Date_Received, Received_by, Referred_By_Type, Referred_By_Name, Contact_Name, Contact_Company,"
omSql = omSql & " Contact_Address_Line1, Contact_Address_Line2, Contact_Address_City, Contact_Address_State, Contact_Address_Zip,"
omSql = omSql & " Contact_Phone, Contact_Fax, Contact_E_Mail, Requestor_Type, Facility_Agency_Interest_ID, Facility_Name,"
omSql = omSql & " Facility_Company, Facility_Address_Line1, Facility_Address_Line2, Facility_Address_City, Facility_Address_State,"
omSql = omSql & " Facility_Address_Zip, Facility_Type, Facility_County, Assistance_Types.Assistance_Type, Facility_Type1,"
omSql = omSql & " Lead_DCA, Request, Final_Response, Date_Close"
omSql = omSql & " FROM Assistance_Request INNER JOIN Assistance_Types ON Assistance_Request.Assistance_ID = Assistance_Types.Assistance_ID"
osSql = "SELECT Referred_to_Agencies.* FROM Referred_to_Agencies"
varID = Me.Assistance_ID
os2Sql = "SELECT Communication Log.* FROM Communication Log"
varID = Me.Assistance_ID
strWhere = " Where Assistance_Request.[Assistance_ID]=" & varID
strWhere1 = " Where [Assistance_ID]=" & varID
strWhere2 = " Where [Assistance_ID]=" & varID

Set db = CurrentDb
Set qdMain = db.QueryDefs("qryCSVMain")
qdMain.SQL = omSql & strWhere
Set qdSub = db.QueryDefs("qryCSVSubform")
qdSub.SQL = osSql & strWhere1
Set qdSub2 = db.QueryDefs("qryCSVSubform2")
qdSub2.SQL = os2Sql & strWhere2
DoCmd.TransferText acExportDelim, "QryCSVMain Export Specification", "qryCSVMain", CurrentProject.Path & "\main.csv", -1
DoCmd.TransferText acExportDelim, "QryCSVSubform Export Specification", "qryCSVSubform", CurrentProject.Path & "\subform.csv", -1
DoCmd.TransferText acExportDelim, "QryCSVSubform2 Export Specification", "qryCSVSubform2", CurrentProject.Path & "\subform2.csv", -1

'restore querdy def to original
qdMain.SQL = omSql
qdSub.SQL = osSql
qdSub2.SQL = os2Sql
Set qdMain = Nothing
Set qdSub = Nothing
Set qdSub2 = Nothing
End Sub
Private Sub Command85_Click()
Dim x, topsql As String, qd As DAO.QueryDef, db As DAO.Database
x = InputBox("Enter Number Of Records")
If Len(x & "") > 0 Then
    topsql = "select top " & x & " Contact_Name,Contact_Company, Contact_Address_Line1, Contact_Address_Line2, Contact_Address_State, Contact_Address_Zip, Contact_Phone, Contact_Fax, Contact_E_Mail, Requestor_Type, Facility_Agency_Interest_ID, Facility_Type, Facility_Name, Facility_Company, Facility_Address_Line1, Facility_Address_Line2, Facility_Address_City, Facility_Address_State, Facility_Address_Zip, Facility_County, Facility_Type1"
    topsql = topsql & " FROM Assistance_Request"
    topsql = topsql & " ORDER BY Assistance_ID DESC"
End If
Set db = CurrentDb
Set qd = db.QueryDefs("qryTopX")
qd.SQL = topsql
DoCmd.OpenQuery "qryTopX"
End Sub
Private Sub Contact_Address_City_AfterUpdate()
[Contact_Address_State] = "KY"
End Sub
Private Sub Facility_Address_City_AfterUpdate()
[Facility_Address_State] = "KY"
End Sub
Private Sub Form_Load()
Application.SetOption "Default Find/Replace Behavior", 1
End Sub
Private Sub Received_by_AfterUpdate()
[Lead_DCA] = [Received_by]
End Sub
Private Sub Command82_Click()
' Print Current record
 If IsNull(Me!Assistance_ID) Then
 MsgBox "Please select a valid record", _
 vbOKOnly, "Error"
 Exit Sub
 End If
 DoCmd.OpenReport "Assistance_Request1", , , _
 "Assistance_ID = " & Me!Assistance_ID
 End Sub

Question by:flashinglemur
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
  • 5
  • 3
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 34086579
you have to create the Export  Specification first..

here is how to create the export specification

1.right click on the table or query export > Text file
   click on Browse and locate the destination folder
3. (you can accept the proposed name or change it)
click Save, then click OK
4. In the export text wizard select the type (Delim or Fixed) width
5. Follow the wizard, before clicking on Finish
     5a .Click Advanced
6. In the Export Specification dialog box Field Information List, correct any descrepancies

7. click save as, give the specification a name <-- this is the specification name that you will use in the command line below

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myCsv.csv", True
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34086584

Author Comment

ID: 34086964
Capricorn1, you explained it nicely.  Problem and question...

When exporting the text file, it only indicates .txt, not .csv.  Do I just rename my txt file as csv? My exported files name from this process is qryCSVSubform2.txt

These are the current DoCmd lines.  The first two added by you, and the third by me:

DoCmd.TransferTcExportDelim, "QryCSVMain Export Specification", "qryCSVMain", CurrentProject.Path & "\main.csv", -1

DoCmd.TransferText acExportDelim, "QryCSVSubform Export Specification", "qryCSVSubform", CurrentProject.Path & "\subform.csv", -1

DoCmd.TransferText acExportDelim, "QryCSVSubform2 Export Specification", "qryCSVSubform2", CurrentProject.Path & "\subform2.csv", -1

The following is the line you suggest adding - actually replacing my subform2 line ...

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myCsv.csv", True

So ...

Is my current subform2 line not correct?

Also, now that I have exported the qryCSVSubform2.txt (csv) file, what am I supposed to do with that file?  I didn't see the two other files that must have been generated when you created main.csv and subform.csv.

As things are now, it's still giving me the same error, "the text file  specification 'QryCSVSubform2 Export Specification' does not exist. You  cannot import, export, or link using the specification."

Thank you again.

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34087037
with this error

<As things are now, it's still giving me the same error, "the text file  specification 'QryCSVSubform2 Export Specification' does not exist. >

you probably missed a step in the procedure i posted above..

the suggestion from the step posted above

   DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myCsv.csv", True

is just a format that you have to follow..

your transfer text  code for subform2 format is correct

DoCmd.TransferText acExportDelim, "QryCSVSubform2 Export Specification", "qryCSVSubform2", CurrentProject.Path & "\subform2.csv", -1

except that the export specification is incorrect..

try to do it again, reading and following the steps carefully..


Author Comment

ID: 34087387
The attachment contains screen captures of the steps I followed for the export specification.

Still getting same error.

I am not sure why you said the export specification was not correct for subform2.  It's the same format as subform and main, which you successfully created and worked beautifully.

Finally, the file I generated for export specification - after creation is it necessary to keep it or refer to it?

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34087487
i meant to say  "you are getting the error because the export spec for subform2 does not exists."

Assisted Solution

flashinglemur earned 0 total points
ID: 34087815
Ugh.  I found the problem.  I had a table name with a space, "communication log".  Apparently that's not good.  Changed the table name to "communication_log", made corresponding changes on the command line, and now it works like a charm. Still, full points to Capricorn1.

Author Comment

ID: 34087907
Capricorn1 guided me to the answer.  With further research I found I could only have one name for the table, not two (communication log)

Author Closing Comment

ID: 34126709
communication log table should be one word instead of two.

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

691 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