0

Hello I am working in MS Access VBA I am writing code to loop through all query defs and append them to a text file. When the query sql contains utf-8 characters, my output file contains "???" where the UTF-8 characters should appear. I would like the utf-8 characters appear in the text file. My Code is below, any help would be appreciated.

Sub PrintQueries()
Dim strSeparator    As String       
Dim strFileName     As String       
Dim returnvalue     As Variant      
Dim qdef            As QueryDef
Dim qdefs           As QueryDefs
Dim strQueryName    As String       
Dim strQuery        As String       
Dim strdate         As String       

    
    strdate = Replace(Replace(Now(), "/", "-"), ":", ".")
    Set qdefs = Application.CodeDb.QueryDefs
    
    strFileName = "C:\temp\Queries_" & strdate & ".txt"
    For Each qdef In qdefs
        strQueryName = qdef.Name
        If Left(strQueryName, 1) <> "~" Then
            Call TxtAppend(strFileName, strQueryName)
            strQuery = qdef.SQL                          '<-- What if strQuery contains utf-8?
            Call TxtAppend(strFileName, strQuery)        '<-- Call function
            strSeparator = "===================="
            Call TxtAppend(strFileName, strSeparator)
        End If
    Next
    returnvalue = Shell("notepad.exe" & strFileName, vbNormalFocus)
End Sub

Public Function TxtAppend(strFileName As String, strText As String)

Dim intFileNumber   As Integer 
   
    intFileNumber = FreeFile

    Open strFileName For Append As #intFileNumber

    Print #intFileNumber, strText

    Close #intFileNumber

End Function
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Thribben
  • 13
  • 3
  • 1
    The bug is using `FreeFile` in the first place. This is a remnant of 1970s BASIC and replaced **in the late 1990s* by [FileSystemObject](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object). There's no problem with Unicode. VBA strings are Unicode. This page is Unicode. The archaic file commands know nothing about Unicode though. – Panagiotis Kanavos Jul 02 '21 at 12:41
  • You can also use ADO to achieve this without using `FileSystemObject`, see this answer: https://stackoverflow.com/a/18982841/2169762 – Martin Jul 02 '21 at 12:49
  • @Martin that's an abuse of ADO Stream. It was never meant for this. ADO is a data access library. FSO was is the class used for text. Sure, it can do the job, but it's a just a liiiitle bit too heavy – Panagiotis Kanavos Jul 02 '21 at 13:06
  • 1
    @PanagiotisKanavos: Example added. – Gustav Jul 02 '21 at 13:08
  • 1
    @Gustav that's a *far* better duplicate. In the original the correct answer appeared last – Panagiotis Kanavos Jul 02 '21 at 13:10
  • There's no bug. Text-mode connections just use the system charset (in contrast to VBA itself), and that likely doesn't support the unicode characters you wanted to include. If you want to write UTF-16, just use binary mode, if you want to use UTF-8, use a stream (or WinAPI to do the UTF-8 to UTF-16 conversion for slightly better performance) – Erik A Jul 02 '21 at 15:01

0 Answers0