Export Whole table to text file with UTF-8

[Auto]

hi, i am having a table in access 2007 named AllText, i need to get that table exported into a text file which needs to be Charset UTF-8 without BOM.
i searched all over, and i made some code with help from other sites, but i am getting into type mismatch error, can anyone help me why it happens?

here is the code

Private Sub Command6_Click()DoCmd.SetWarnings False




Dim MyTable As DAO.Recordset
Set MyTable = CurrentDb.OpenRecordset("SELECT * FROM AllText")


Dim sqlStream As Object
Set sqlStream = CreateObject("ADODB.Stream")
'Init stream
sqlStream.Open
sqlStream.Position = 0
sqlStream.Charset = "UTF-8"
sqlStream.writetext MyTable   '< HERE I GET TYPE MISMATCH


Dim binaryStream As Object
Set binaryStream = CreateObject("ADODB.Stream")
binaryStream.Type = 1
binaryStream.Mode = 3
binaryStream.Open


'Skip BOM bytes
sqlStream.Position = 3
sqlStream.CopyTo binaryStream
sqlStream.Flush
sqlStream.Close
binaryStream.SaveToFile "E:\Update web\text\all.txt", 2
binaryStream.Flush
binaryStream.Close


DoCmd.SetWarnings True
End Sub

[ssanfu]

sqlStream.writetext MyTable

"The WriteText method is used to write a text to a text Stream object."
"This method (writetext) is used only with text Stream objects, for binary Stream objects, use the Write method."
re: http://www.w3schools.com/ado/met_stream_writetext.asp


You appear to be trying to write TO a DAO record set. I do not see where the data is being read from.

...table exported into a text file...

You want to write the data in the table "AllText" to a text file? What is the structure of the table?

[Auto]

thanks ssanfu for your reply
the AllText table has about 25 columns and 232000 records, i need to export the whole table as is with the column names into a text file, but it should get converted to UTF-8 Without BOM.

can you help me fix the code?
thanks again

[ssanfu]

can you help me fix the code?

Not asking for much, are you?????

(smile, I'm only joking!!!!)



OK, so I have never used the ADO Stream Object before.
With around 232,000 records, I don't know how long this will take. I would suggest you first try this code with a table that has 100 - 200 records.

Option Compare Database  'these two lines should be at the top of every code page
Option Explicit          'these two lines should be at the top of every code page

Private Sub Command6_Click()
   On Error GoTo HandleErr

   Dim MyTable As DAO.Recordset
   Dim binaryStream As Object
   Dim ADOStream As Object
   Dim sString As String
   Dim i As Integer
   Dim FieldCount As Integer
   Dim FileName As String
   Dim Processing As Long
   Dim ModNum as Integer

   'text file name
   FileName = "E:\Update web\text\all.txt"

   Processing = 0

   'open recordset
   Set MyTable = CurrentDb.OpenRecordset("SELECT * FROM AllText")

   If MyTable.BOF And MyTable.EOF Then
      MsgBox "No records"
      MyTable.Close
      Set MyTable = Nothing
      Exit Sub
   End If

   'populate record set
   MyTable.MoveLast

   'report after how many records??
   If MyTable.Recordcount >500 then
      ModNum = 100
   Else
      ModNum = 10
   End If

   MyTable.MoveFirst

   'get number of fields in record set
   FieldCount = MyTable.Fields.count - 1

   'create the stream
   Set ADOStream = CreateObject("ADODB.Stream")
   'Init stream
   ADOStream.Open
   ADOStream.Position = 0
   ADOStream.Charset = "UTF-8"
   ADOStream.LineSeparator = adCRLF

   'write field names
   For i = 0 To FieldCount
      sString = sString & MyTable.Fields(i).Name & ","
   Next

   'remove trailing comma
   If Len(Trim(sString)) > 0 Then
      sString = Left(sString, Len(sString) - 1)
   End If

   'write the header (field names)
   ADOStream.writetext sString, adWriteLine

   'get recordset data
   Do While Not MyTable.EOF
      sString = ""
      Processing = Processing + 1
      'tell me every ModNum of lines
      If Processing Mod ModNum = 0 Then
         ' you need to create a text box on the form and
         ' un-comment the following line and another line at the bottom 
         ' to see the record count
         'Me.tbRecordCount = Processing
      End If
      'put the data in fields in a string
      For i = 0 To FieldCount
         sString = sString & MyTable.Fields(i) & ","
      Next

      'remove trailing comma
      If Len(Trim(sString)) > 0 Then
         sString = Left(sString, Len(sString) - 1)
      End If

      ADOStream.writetext sString, adWriteLine

      MyTable.MoveNext
   Loop

   'save text file
   ADOStream.SaveToFile FileName, 2

   'Tell Me
  '              Me.tbRecordCount = Processing        ' don't forget me.....
   MsgBox "Done"

HandleErr_Exit:
   'clean up
   On Error Resume Next
   ADOStream.Close
   MyTable.Close
   Set MyTable = Nothing
   Exit Sub

HandleErr:
   MsgBox Err.Number & " / " & Err.Description
   Resume HandleErr_Exit

End Sub

If you create a text box control on the form, name it tbRecordCount and uncomment the two lines with blue.



Whew!!!! This should get you started....