Solved TransferText (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 08:48
Joined
Oct 14, 2019
Messages
432
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?


Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:48
Joined
May 7, 2009
Messages
19,175
you need to save strSQL to a Query then use the Query name in your TransferText method.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:48
Joined
Oct 29, 2018
Messages
21,360
If you want to use a Recordset, maybe consider using an ADO Recordset and then use the GetString method.
 

ClaraBarton

Registered User.
Local time
Today, 08:48
Joined
Oct 14, 2019
Messages
432
Have a feeling this is a stupid question...
I already have my recordset filtered. Can I create a query from a recordset?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:48
Joined
Oct 29, 2018
Messages
21,360
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

Registered User.
Local time
Today, 08:48
Joined
Oct 14, 2019
Messages
432
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

I’m here to help
Staff member
Local time
Today, 08:48
Joined
Oct 29, 2018
Messages
21,360
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

Registered User.
Local time
Today, 08:48
Joined
Oct 14, 2019
Messages
432
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

I’m here to help
Staff member
Local time
Today, 08:48
Joined
Oct 29, 2018
Messages
21,360
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.
1601049882586.png


And here's what I get with using GetString.
1601049906229.png


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

Isaac

Lifelong Learner
Local time
Today, 08:48
Joined
Mar 14, 2017
Messages
8,738
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:

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

You've got your good things, and you've got mine.
Local time
Today, 11:48
Joined
May 21, 2018
Messages
8,463
& "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

Registered User.
Local time
Today, 08:48
Joined
Oct 14, 2019
Messages
432
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

Lifelong Learner
Local time
Today, 08:48
Joined
Mar 14, 2017
Messages
8,738
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:48
Joined
May 21, 2018
Messages
8,463
Sorry I said thread. I should have said "reply 11", by @Isaac.
 

ClaraBarton

Registered User.
Local time
Today, 08:48
Joined
Oct 14, 2019
Messages
432
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

I’m here to help
Staff member
Local time
Today, 08:48
Joined
Oct 29, 2018
Messages
21,360
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

You've got your good things, and you've got mine.
Local time
Today, 11:48
Joined
May 21, 2018
Messages
8,463
Here is my attempt at ExportDAO_R
Code:
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.
 
Last edited:

Users who are viewing this thread

Top Bottom