Hi Sierk,
This is such a common issue - I thought I'd write a skeleton function to do
it.
There's a way, it's called VBA!
Remarks and the assumptions - tsv or tab delimited file I will assume a tab
is going to be our field delimiter. Text happens to be a type 10 field. Now,
we hope/assume there aren't any humongous memo fields here, right? But just
in case, maybe we better test for a type 12 and skip over it if we find one.
The most common text delimiters would be the single quote chr(39) or double
quotes chr(34). I tend to favor the single quote but either way works just
fine as long as the user doesn't embed on into a field's contents. There are
some other field types that could ruin our export as well, but I assume if
you have a table that contains blobs or whatever, you know you can't export
that column to a tsv file. Create a select query (and save it), one that
doesn't include the 'no go' columns and export that recordset instead.
If this was to be bulletproof - i.e. someone was paying me a chunk of money
to make this a seamless, very clean export - I might have to search for and
substitute 'safe chars' in the place of 'bad chars' like comma's or quotes
or various brackets or slashes or pipe symbols - how much scrubbing is
needed depends on how you going to use the exported file. RegEx offers
superb ways to handle the scrubbing but RegEx is not the tool us
double-digit IQ types readily scribble out functions for. I have to RTFM
everytime I want use RegEx to parse some string data.
The rather spartan and unprotected quoted below code works... all you have
to do is pass it the complete path & file name, and the table name, and
don't mess up (don't send it a non-existent drive designation or a folder
name that doesn't exist or an illegal file name or a bad/wrong table name).
What programmers get paid the big bucks for is wiping the users little noses
for them and protecting them from paying the price for their bad data
inputs. I'm sure it will wrap badly in the news group but if you copy and
paste it into notepad, with wordwrap turned off, it will probably be easy to
straighten it out (for the most part).
I'm using the ancient QuickBasic style output to file as # instead of the
filescriptingobject. The filescriptingobject works and is probably more
efficient but it requires a reference to the appropriate scripting library
and some security minded folks get nervous about letting that library get
loaded.
If you want to, you could enumerate the tables collection using the
tabledefs object and export each and every table - using the tablename as
the filename and add the .tsv extension to it.
==============================
Function myExport(strFile As String, strTable As String) As Boolean
Dim rs As DAO.Recordset, f As Variant, i As Integer
Dim fNum As Integer, myRow As String, EndOfRow As Integer
fNum = FreeFile()
i = 0
Set rs = CurrentDb.OpenRecordset(strTable)
EndOfRow = rs.Fields.Count
If rs.RecordCount > 0 Then
'we have records, lets open up our target file
' and get the header labels
Open strFile For Output As #fNum
For Each f In rs.Fields
i = i + 1
If i < EndOfRow Then
myRow = myRow & Chr(39) & f.Name & Chr(39) & vbTab
Else
myRow = myRow & Chr(39) & f.Name & Chr(39)
End If
Next f
Print #fNum, myRow
rs.MoveFirst
Do While Not rs.EOF
i = 0
myRow = ""
For Each f In rs.Fields
Select Case rs.Fields(i).Type
Case Is = 12
'ut oh - we have a memo, could insert null
'or a space but I'm just tabbing past
If (i + 1) < EndOfRow Then
myRow = myRow & vbTab
End If
Case Is = 10
'we have text
If i + 1 < EndOfRow Then
myRow = myRow & Chr(39) & f.Value & Chr(39) &
vbTab
Else
myRow = myRow & Chr(39) & f.Value & Chr(39)
End If
Case Else
'we have numeric
If i + 1 < EndOfRow Then
myRow = myRow & f.Value & vbTab
Else
myRow = myRow & f.Value
End If
i = i + 1
End Select
Next f
Print #fNum, myRow
rs.MoveNext
Loop
End If
Close #fNum
Set rs = Nothing
'should write some error trapping stuff
'and return false if the export failed.
'along with bringing up a msgbox to show the err.no
'and err.description
myExport = True
End Function
Hope this helps you - (or someone)..
Gordon
