Solved TransferText

[ClaraBarton]

I have a popup window that takes a recordset from the form and does optional things with it.
The following gives me a Data Type conversion error
Is it ok to pull out specific fields from an already existing recordset to export?
Or is there a better way?

Dim strSql As String
            Dim rstExport As Recordset
                        
            
            strSql = "SELECT [Contacts].[Company], [Contacts].[LastName], [Contacts].[FirstName], " _
                & "[Contacts].[EMail], [Contacts].[Business], [Contacts].[Home], [Contacts].[Mobile], " _
                & "[Contacts].[Address], [Contacts].[City], [Contacts].[State], [Contacts].[ZIP], " _
                & "[Contacts].[Country], [Contacts].[Notes], [Contacts].[Category], " _
                & "[Contacts].[NonGMO], [Contacts].[Organic] " _
                & "FROM rst9;"
                
            strPath = CurrentProject.Path & "\ExportTo.csv"
            
            Set rstExport = rst9.OpenRecordset(strSql)
            
            DoCmd.TransferText acExportDelim, , "rstExport", strPath, True

 

[arnelgp]

you need to save strSQL to a Query then use the Query name in your TransferText method.

 

[theDBguy]

If you want to use a Recordset, maybe consider using an ADO Recordset and then use the GetString method.

 

[ClaraBarton]

Have a feeling this is a stupid question...
I already have my recordset filtered. Can I create a query from a recordset?

 

[theDBguy]

Have a feeling this is a stupid question...
I already have my recordset filtered. Can I create a query from a recordset?

Hi. Did you not like the suggestion to use an ADO recordset? Just curious...

 

[ClaraBarton]

Well I always use DAO and ADO is foreign to me. Why would you suggest that?
The recordset created from the form is DAO and it just seems like that's what I should work with but I'm open here...

 

[theDBguy]

Well I always use DAO and ADO is foreign to me. Why would you suggest that?

I was suggesting to use ADO in this case because you can use the GetString method to export the recordset into a CSV file with it. Unfortunately, that's not possible with a DAO recordset.

 

[ClaraBarton]

The reason I want to use the SQL string is because I want to limit the fields in the recordset. GetString doesn't do that, it returns the complete record.

 

[theDBguy]

GetString doesn't do that, it returns the complete record.

Are you sure? I guess I'll have to check. Hang on...

 

[theDBguy]

The reason I want to use the SQL string is because I want to limit the fields in the recordset. GetString doesn't do that, it returns the complete record.

Okay, unless I misunderstood what you're trying to do, here's the result of my little test.

Here's what my table looks like.

And here's what I get with using GetString.

Is that any close to what you wanted? Please let us know. Thanks.

 

[Isaac]

The reason I want to use the SQL string is because I want to limit the fields in the recordset. GetString doesn't do that, it returns the complete record.

It might only take a few lines of code:

dim qd as dao.querydef
set qd=currentdb.createquerydef("NewQueryName",strSQL)
...do your xfer text using the named query

(aircode..might have a typo).

 

[MajP]

& "FROM rst9;

I already have my recordset filtered. Can I create a query from a recordset?

No you can not do this. How did you filter rst9? Can the same process be used to make a sql string and follow thread 11 method.

 

[ClaraBarton]

1. No... Your SQL strings are coming from a table and not from a recordset.
2. No... Doesn't work. Your database cannot find the table named "recordset" for the querydef.

 

[Isaac]

1. No... Your SQL strings are coming from a table and not from a recordset.
2. No... Doesn't work. Your database cannot find the table named "recordset" for the querydef.

Not sure if that was directed at me or who.
If so, my apologies - I thought that from your original post, you had a well-formed SQL string, strSql. If so, you could use the method Arnel suggested and I expounded on - if not, then you can't. If you have the sql string, the simplest method would appear to be to create a query def and then refer to that new saved query name in transfer text. If you don't have the sql string, then I misunderstood ... Sorry.

 

[ClaraBarton]

eh... another stupid question... how do I find thread 11?

 

[Isaac]

post 11

 

[MajP]

Sorry I said thread. I should have said "reply 11", by @Isaac.

 

[ClaraBarton]

Oh, sorry. That was point #1 I made. He is pulling his SQL from a table. I only have a recordset. I want to use it to export records but I want to limit the fields that are in the already filtered recordset. The reason is because I am importing it into another database. I don't want any calculated fields, any ID fields, etc. etc. I want it perfect and I'm failing miserably.
I'm really confused... Isaac was point #2. Querydef does not recognize a recordset

 

[theDBguy]

1. No... Your SQL strings are coming from a table and not from a recordset.

Oh, I think I see what you mean now. You were trying to use rst9 for a new recordset. I'm afraid you may have to walk through the recordset to do something like that. Either way, I suspect you might still end up using GetString after you have your correct recordset data to export it to a Text file.

I'll give it another try and let you know if I get anything closer. Cheers!

 

[MajP]

Here is my attempt at ExportDAO_R

Public Sub ExportDAO_Recordset(rs As DAO.Recordset, Optional FilePath As String = "", Optional Delimiter As String = ",")
   Dim fld As DAO.Field
   'References to Office and Microsoft Runtime Scripting
   Dim fso As New Scripting.FileSystemObject
   Dim txtFile As Scripting.TextStream
   Dim LineOut As String
   'Dim SaveLocation As String
   If FilePath = "" Then
     FilePath = GetFileDialog()
   End If
   If Not FilePath = "" Then
      'MsgBox FilePath
      Set txtFile = fso.CreateTextFile(FilePath, True)
      For Each fld In rs.Fields
        If LineOut = "" Then
          LineOut = fld.Name
        Else
          LineOut = LineOut & delimiter & fld.Name
        End If
      Next fld
      txtFile.WriteLine LineOut
      LineOut = ""
      Do While Not rs.EOF
        For Each fld In rs.Fields
          If LineOut = "" Then
            LineOut = fld.Value
          Else
            LineOut = LineOut & Delimiter & fld.Value
          End If
        Next fld
        Debug.Print LineOut
         txtFile.WriteLine LineOut
        LineOut = ""
        rs.MoveNext
      Loop
      txtFile.Close
   End If

End Sub
Public Function GetFileDialog() As String

   ' Requires reference to Microsoft Office 11.0 Object Library.

   Dim fDialog As Office.FileDialog
   Dim intChoice As Integer
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogSaveAs)

   fDialog.Title = "Save Export As"
   intChoice = fDialog.Show
  'determine what choice the user made
 
   If intChoice <> 0 Then
     'get the file path selected by the user
     GetFileDialog = fDialog.SelectedItems(1)
     'displays the result in a message box
    ' Call MsgBox(strPath, vbInformation, "Save Path")
  Else
       MsgBox "You clicked Cancel in the file dialog box."
  End If
End Function

Public Sub TestIt()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblstudents")
  ExportDAO_Recordset rs
End Sub

ecordset. Not highly tested, but worked on a simple table.