Exporting MSAccess Tables as Unicode with Tilde delimiter

Question

I want to export the contents of several tables from MSAccess2003. The tables contain unicode Japanese characters. I want to store them as tilde delimited text files.

I can do this manually using File/Export and, in the 'Advanced' dialog selecting tilde as Field Delimiter and the Unicode as the Code Page.

I can store this as an Export Specification, but this seems to be table specific.

I want to export many tables using VBA Code.

So far I have tried:

Sub ExportTables()

Dim lTbl As Long
Dim dBase As Database
Dim TableName As String

Set dBase = CurrentDb

For lTbl = 0 To dBase.TableDefs.Count
     'If the table name is a temporary or system table then ignore it
    If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _
    Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then
         '~ indicates a temporary table
         'MSYS indicates a system level table
    Else
      TableName = dBase.TableDefs(lTbl).Name
      DoCmd.TransferText acExportDelim, "UnicodeTilde", TableName, "c:\" + TableName + ".txt", True
    End If
Next lTbl
Set dBase = Nothing

End Sub

When I run this I get an exception:

Run-time error '3011': The Microsoft Jet database engine could not find the object "Allowance1#txt'. Make sure the object exists and that you spell its name and the path name correctly.

If I debug at this point, TableName is 'Allowance1', as expected.

I guess my UnicodeTilde export specification is table specific, so I can't use it for multiple tables.

What is the solution? Should I use something else, other than TransferText, or perhaps create the export specification programatically?

Any help appreciated.

Answer 1

In relation to this thread I have stumbled across an incredibly simple solution for being able to use one specification across all table exports whereas normally you would have to create a separate one for each; or use the sub routine provided by Richard A.

The process is as follows:

Create a specification e.g Pipe | delimited with any table, then open a dynaset query in access using SQL SELECT * FROM MSysIMEXColumns and then simply delete all resulting rows. Now this spec will not give error 3011 when you attempt to use a different table to that which you used to create the original spec and is essentially a universal Pipe export spec for any table/query you wish.

This has been discovered/tested in access 2003 so I assume will work for later versions also.

Kind Regards,

Matt Donnan

Answer 2

I've got part of the answer:

I'm writing a schema.ini file with VBA, then doing my TransferText. This is creating an export format on the fly. The only problem is, although my schema.ini contains:

ColNameHeader = True
CharacterSet = Unicode
Format = Delimited(~)

Only the header row is coming out in unicode with tilde delimiters. The rest of the rows are ANSI with commas.

Answer 3

I have eventually solved this. (I am now using Access 2007 but had the same problems as with Access 2003.)

First, what didn't work:

TransferText would only make the Header Row unicode and tilde delimited, even with a correctly formatted schema.ini. (No, I didn't put it all on one line, that was just a formatting issue with the html on stackoverflow.)

[MyTable.txt]
CharacterSet = Unicode
Format = Delimited(~)
ColNameHeader = True
NumberDigits = 10
Col1= "Col1" Char Width 10
Col2= "Col2" Integer
Col3= "Col3" Char Width 2

Just using a select statement:

SELECT * INTO [Text;DATABASE=c:\export\;FMT=Delimited(~)].[MyTable.txt] FROM [MyTable]

Totally ignored the FMT. I found it very hard to find documentation on the format of the parameters. Whatever I typed in the FMT parameter, the only things I could get to work was Fixed. Everything else was treated as CSVDelimited. I could chech this as the select statement created a schema.ini file like this:

[MyTable.txt]
ColNameHeader=True
CharacterSet=1252
Format=CSVDelimited
Col1=Col1 Char Width 10
Col2=Col2 Integer
Col3=Col3 Char Width 2

My eventual solution was to create my own schema.ini then use the select statement. My Module code looks something like this:

Option Compare Database
Option Explicit

    Public Function CreateSchemaFile(bIncFldNames As Boolean, _
                                       sPath As String, _
                                       sSectionName As String, _
                                       sTblQryName As String) As Boolean


         Dim Msg As String
         On Local Error GoTo CreateSchemaFile_Err
         Dim ws As Workspace, db As Database
         Dim tblDef As TableDef, fldDef As Field
         Dim i As Integer, Handle As Integer
         Dim fldName As String, fldDataInfo As String
         ' -----------------------------------------------
         ' Set DAO objects.
         ' -----------------------------------------------
         Set db = CurrentDb()
         ' -----------------------------------------------
         ' Open schema file for append.
         ' -----------------------------------------------
         Handle = FreeFile
         Open sPath & "schema.ini" For Output Access Write As #Handle
         ' -----------------------------------------------
         ' Write schema header.
         ' -----------------------------------------------
         Print #Handle, "[" & sSectionName & "]"
         Print #Handle, "CharacterSet = Unicode"
         Print #Handle, "Format = Delimited(~)"
         Print #Handle, "ColNameHeader = " & _
                         IIf(bIncFldNames, "True", "False")
         Print #Handle, "NumberDigits = 10"
         ' -----------------------------------------------
         ' Get data concerning schema file.
         ' -----------------------------------------------
         Set tblDef = db.TableDefs(sTblQryName)
         With tblDef
            For i = 0 To .Fields.Count - 1
               Set fldDef = .Fields(i)
               With fldDef
                  fldName = .Name
                  Select Case .Type
                     Case dbBoolean
                        fldDataInfo = "Bit"
                     Case dbByte
                        fldDataInfo = "Byte"
                     Case dbInteger
                        fldDataInfo = "Short"
                     Case dbLong
                        fldDataInfo = "Integer"
                     Case dbCurrency
                        fldDataInfo = "Currency"
                     Case dbSingle
                        fldDataInfo = "Single"
                     Case dbDouble
                        fldDataInfo = "Double"
                     Case dbDate
                        fldDataInfo = "Date"
                     Case dbText
                        fldDataInfo = "Char Width " & Format$(.Size)
                     Case dbLongBinary
                        fldDataInfo = "OLE"
                     Case dbMemo
                        fldDataInfo = "LongChar"
                     Case dbGUID
                        fldDataInfo = "Char Width 16"
                  End Select
                  Print #Handle, "Col" & Format$(i + 1) _
                                  & "= """ & fldName & """" & Space$(1); "" _
                                  & fldDataInfo
               End With
            Next i
         End With
         CreateSchemaFile = True
CreateSchemaFile_End:
         Close Handle
         Exit Function
CreateSchemaFile_Err:
         Msg = "Error #: " & Format$(Err.Number) & vbCrLf
         Msg = Msg & Err.Description
         MsgBox Msg
         Resume CreateSchemaFile_End
      End Function

Public Function ExportATable(TableName As String)
Dim ThePath As String
Dim FileName As String
Dim TheQuery As String
Dim Exporter As QueryDef
ThePath = "c:\export\"
FileName = TableName + ".txt"
CreateSchemaFile True, ThePath, FileName, TableName
On Error GoTo IgnoreDeleteFileErrors
FileSystem.Kill ThePath + FileName
IgnoreDeleteFileErrors:
TheQuery = "SELECT * INTO [Text;DATABASE=" + ThePath + "].[" + FileName + "] FROM [" + TableName + "]"
Set Exporter = CurrentDb.CreateQueryDef("", TheQuery)
Exporter.Execute
End Function


Sub ExportTables()

    Dim lTbl As Long
    Dim dBase As Database
    Dim TableName As String

    Set dBase = CurrentDb

    For lTbl = 0 To dBase.TableDefs.Count - 1
         'If the table name is a temporary or system table then ignore it
        If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _
        Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then
             '~ indicates a temporary table
             'MSYS indicates a system level table
        Else
          TableName = dBase.TableDefs(lTbl).Name
          ExportATable (TableName)
        End If
    Next lTbl
    Set dBase = Nothing
End Sub

I make no claims that this is elegant, but it works. Also note that the stackoverflow code formatter doesn't like my \", so it doesn't pretty print my code very nicely.

Answer 4

I've got two suggestions for you:

1. Make sure you're putting each setting in your [schema.ini] file on a new line. (You've listed it here all on one line, so I thought I'd make sure.)

2. Don't forget to supply the CodePage argument (last one) when you call your TransferText. Here's a list of supported values if you need it:

http://msdn.microsoft.com/en-us/library/aa288104.aspx

Other than that, it looks like your approach should work.