Solved

Export Current Form to CSV - VBA question

Posted on 2010-11-08
9
763 Views
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
db.Close
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
 

exchange.accdb
0
Comment
Question by:flashinglemur
  • 5
  • 3
9 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
you have to create the Export  Specification first..

here is how to create the export specification

1.right click on the table or query
2.select 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
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
0
 
LVL 1

Author Comment

by:flashinglemur
Comment Utility
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.

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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..

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:flashinglemur
Comment Utility
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?

Sorry...
steps.zip
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
i meant to say  "you are getting the error because the export spec for subform2 does not exists."
0
 
LVL 1

Assisted Solution

by:flashinglemur
flashinglemur earned 0 total points
Comment Utility
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.
0
 
LVL 1

Author Comment

by:flashinglemur
Comment Utility
Capricorn1 guided me to the answer.  With further research I found I could only have one name for the table, not two (communication log)
0
 
LVL 1

Author Closing Comment

by:flashinglemur
Comment Utility
communication log table should be one word instead of two.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now