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
  • 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 51

Expert Comment

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.

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert null in sql server 12 34
update all email addresses SQL 1 24
DCount Type Mismatch 2 22
Use .MoveNext in VBA but have it filter criteria 2 12
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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