Dear all,

Is it possible in Access 2007 to create a delimited-data export
specification that is independent of the source table structure?

For example, if I want to export to a text file with a delimiter | and a
text qualifier of # (instead of the default , and "), I can create a spec to
allow automation of the task with TransferText.

However, if the table I use to create the spec has 7 fields and I try to
apply the spec to a different table with 8 fields I get a "The Microsoft
Office Access database engine could not find the object 'Filename#csv'. Make
sure the object exists and that you spell its name and the path name
correctly," error. (Aside: Least helpful Access error message *ever*)

Is there anyway to bypass this so that I can use code to cycle through an
array of export tables without having to specify a different spec for each
table?

Thanks,
Alex.

Well, here's a little routine that will export a table or query to a a text
file, with whatever delimiter, text qualifier, and with or without field
names:

'-----------------------------
Sub ExportTextFileDelimited(FileName As String, _
DataSet As String, _
Delimiter As String, _
TextQualifier As String, _
WithFieldNames As Boolean)
On Error GoTo ExportTextFile_Err

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim Directory As String
Dim MyString As String, strSQL As String
Dim strDS As String
Dim I As Integer

Open FileName For Output As #1
Set cnn = CurrentProject.Connection

rst.Open DataSet, cnn, adOpenForwardOnly, adLockReadOnly
If WithFieldNames Then
For I = 0 To rst.Fields.Count - 1
MyString = MyString & rst(I).Name & Delimiter
Next I
MyString = Left(MyString, Len(MyString) - 1)
Print #1, MyString
End If
rst.MoveFirst
Do While Not rst.EOF
MyString = ""
For I = 0 To rst.Fields.Count - 1
'check for text datatype (202)
If rst(I).Type = 202 Then
MyString = MyString & TextQualifier & _
rst(I) & TextQualifier & Delimiter
Else
MyString = MyString & rst(I) & "|"
End If
Next I
MyString = Left(MyString, Len(MyString) - 1)
Print #1, MyString
rst.MoveNext
Loop

ExportTextFile_Exit:
' Close text file.
Close #1
rst.Close
Set cnn = Nothing
Exit Sub
ExportTextFile_Err:
MsgBox Err.Description
Resume ExportTextFile_Exit
End Sub
'-----------------------------

Call it like this:

Call ExportTextFileDelimited2("C:\Table1.txt", "Table1", "|", "#", true)

Of course, you could put it in a loop and replace the table (or query) and
filename each time.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L




"Tokyo Alex" <(E-Mail Removed)> wrote in message
newsD370AF4-660F-4B69-95CB-(E-Mail Removed)...
> Dear all,
>
> Is it possible in Access 2007 to create a delimited-data export
> specification that is independent of the source table structure?
>
> For example, if I want to export to a text file with a delimiter | and a
> text qualifier of # (instead of the default , and "), I can create a spec
> to
> allow automation of the task with TransferText.
>
> However, if the table I use to create the spec has 7 fields and I try to
> apply the spec to a different table with 8 fields I get a "The Microsoft
> Office Access database engine could not find the object 'Filename#csv'.
> Make
> sure the object exists and that you spell its name and the path name
> correctly," error. (Aside: Least helpful Access error message *ever*)
>
> Is there anyway to bypass this so that I can use code to cycle through an
> array of export tables without having to specify a different spec for each
> table?
>
> Thanks,
> Alex.

Whups. Couple of corrections in the main loop:

Do While Not rst.EOF
MyString = ""
For I = 0 To rst.Fields.Count - 1
'check for text datatype (202)
If rst(I).Type = 202 Then
MyString = MyString & TextQualifier & _
rst(I) & TextQualifier & Delimiter
Else
MyString = MyString & rst(I) & Delimiter '<----
End If
Next I
MyString = Left(MyString, Len(MyString) - 2) '<---
Print #1, MyString
rst.MoveNext
Loop


--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com
http://rogersaccessblog.blogspot.com/


"Roger Carlson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Well, here's a little routine that will export a table or query to a a
> text file, with whatever delimiter, text qualifier, and with or without
> field names:
>
> '-----------------------------
> Sub ExportTextFileDelimited(FileName As String, _
> DataSet As String, _
> Delimiter As String, _
> TextQualifier As String, _
> WithFieldNames As Boolean)
> On Error GoTo ExportTextFile_Err
>
> Dim cnn As ADODB.Connection
> Dim rst As New ADODB.Recordset
> Dim Directory As String
> Dim MyString As String, strSQL As String
> Dim strDS As String
> Dim I As Integer
>
> Open FileName For Output As #1
> Set cnn = CurrentProject.Connection
>
> rst.Open DataSet, cnn, adOpenForwardOnly, adLockReadOnly
> If WithFieldNames Then
> For I = 0 To rst.Fields.Count - 1
> MyString = MyString & rst(I).Name & Delimiter
> Next I
> MyString = Left(MyString, Len(MyString) - 1)
> Print #1, MyString
> End If
> rst.MoveFirst
> Do While Not rst.EOF
> MyString = ""
> For I = 0 To rst.Fields.Count - 1
> 'check for text datatype (202)
> If rst(I).Type = 202 Then
> MyString = MyString & TextQualifier & _
> rst(I) & TextQualifier & Delimiter
> Else
> MyString = MyString & rst(I) & "|"
> End If
> Next I
> MyString = Left(MyString, Len(MyString) - 1)
> Print #1, MyString
> rst.MoveNext
> Loop
>
> ExportTextFile_Exit:
> ' Close text file.
> Close #1
> rst.Close
> Set cnn = Nothing
> Exit Sub
> ExportTextFile_Err:
> MsgBox Err.Description
> Resume ExportTextFile_Exit
> End Sub
> '-----------------------------
>
> Call it like this:
>
> Call ExportTextFileDelimited2("C:\Table1.txt", "Table1", "|", "#", true)
>
> Of course, you could put it in a loop and replace the table (or query) and
> filename each time.
>
>
> --
> --Roger Carlson
> MS Access MVP
> Access Database Samples: www.rogersaccesslibrary.com
> Want answers to your Access questions in your Email?
> Free subscription:
> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>
>
>
>
> "Tokyo Alex" <(E-Mail Removed)> wrote in message
> newsD370AF4-660F-4B69-95CB-(E-Mail Removed)...
>> Dear all,
>>
>> Is it possible in Access 2007 to create a delimited-data export
>> specification that is independent of the source table structure?
>>
>> For example, if I want to export to a text file with a delimiter | and a
>> text qualifier of # (instead of the default , and "), I can create a spec
>> to
>> allow automation of the task with TransferText.
>>
>> However, if the table I use to create the spec has 7 fields and I try to
>> apply the spec to a different table with 8 fields I get a "The Microsoft
>> Office Access database engine could not find the object 'Filename#csv'.
>> Make
>> sure the object exists and that you spell its name and the path name
>> correctly," error. (Aside: Least helpful Access error message *ever*)
>>
>> Is there anyway to bypass this so that I can use code to cycle through an
>> array of export tables without having to specify a different spec for
>> each
>> table?
>>
>> Thanks,
>> Alex.
>
>

Thanks very much for the response.

I was hoping to be able to use TransferText, but it looks like I can't, and
your code will be very helpful.

Thanks again,
Alex.


"Roger Carlson" wrote:

> Whups. Couple of corrections in the main loop:
>
> Do While Not rst.EOF
> MyString = ""
> For I = 0 To rst.Fields.Count - 1
> 'check for text datatype (202)
> If rst(I).Type = 202 Then
> MyString = MyString & TextQualifier & _
> rst(I) & TextQualifier & Delimiter
> Else
> MyString = MyString & rst(I) & Delimiter '<----
> End If
> Next I
> MyString = Left(MyString, Len(MyString) - 2) '<---
> Print #1, MyString
> rst.MoveNext
> Loop
>
>
> --
> --Roger Carlson
> MS Access MVP
> www.rogersaccesslibrary.com
> http://rogersaccessblog.blogspot.com/
>
>
> "Roger Carlson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Well, here's a little routine that will export a table or query to a a
> > text file, with whatever delimiter, text qualifier, and with or without
> > field names:
> >
> > '-----------------------------
> > Sub ExportTextFileDelimited(FileName As String, _
> > DataSet As String, _
> > Delimiter As String, _
> > TextQualifier As String, _
> > WithFieldNames As Boolean)
> > On Error GoTo ExportTextFile_Err
> >
> > Dim cnn As ADODB.Connection
> > Dim rst As New ADODB.Recordset
> > Dim Directory As String
> > Dim MyString As String, strSQL As String
> > Dim strDS As String
> > Dim I As Integer
> >
> > Open FileName For Output As #1
> > Set cnn = CurrentProject.Connection
> >
> > rst.Open DataSet, cnn, adOpenForwardOnly, adLockReadOnly
> > If WithFieldNames Then
> > For I = 0 To rst.Fields.Count - 1
> > MyString = MyString & rst(I).Name & Delimiter
> > Next I
> > MyString = Left(MyString, Len(MyString) - 1)
> > Print #1, MyString
> > End If
> > rst.MoveFirst
> > Do While Not rst.EOF
> > MyString = ""
> > For I = 0 To rst.Fields.Count - 1
> > 'check for text datatype (202)
> > If rst(I).Type = 202 Then
> > MyString = MyString & TextQualifier & _
> > rst(I) & TextQualifier & Delimiter
> > Else
> > MyString = MyString & rst(I) & "|"
> > End If
> > Next I
> > MyString = Left(MyString, Len(MyString) - 1)
> > Print #1, MyString
> > rst.MoveNext
> > Loop
> >
> > ExportTextFile_Exit:
> > ' Close text file.
> > Close #1
> > rst.Close
> > Set cnn = Nothing
> > Exit Sub
> > ExportTextFile_Err:
> > MsgBox Err.Description
> > Resume ExportTextFile_Exit
> > End Sub
> > '-----------------------------
> >
> > Call it like this:
> >
> > Call ExportTextFileDelimited2("C:\Table1.txt", "Table1", "|", "#", true)
> >
> > Of course, you could put it in a loop and replace the table (or query) and
> > filename each time.
> >
> >
> > --
> > --Roger Carlson
> > MS Access MVP
> > Access Database Samples: www.rogersaccesslibrary.com
> > Want answers to your Access questions in your Email?
> > Free subscription:
> > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> >
> >
> >
> >
> > "Tokyo Alex" <(E-Mail Removed)> wrote in message
> > newsD370AF4-660F-4B69-95CB-(E-Mail Removed)...
> >> Dear all,
> >>
> >> Is it possible in Access 2007 to create a delimited-data export
> >> specification that is independent of the source table structure?
> >>
> >> For example, if I want to export to a text file with a delimiter | and a
> >> text qualifier of # (instead of the default , and "), I can create a spec
> >> to
> >> allow automation of the task with TransferText.
> >>
> >> However, if the table I use to create the spec has 7 fields and I try to
> >> apply the spec to a different table with 8 fields I get a "The Microsoft
> >> Office Access database engine could not find the object 'Filename#csv'.
> >> Make
> >> sure the object exists and that you spell its name and the path name
> >> correctly," error. (Aside: Least helpful Access error message *ever*)
> >>
> >> Is there anyway to bypass this so that I can use code to cycle through an
> >> array of export tables without having to specify a different spec for
> >> each
> >> table?
> >>
> >> Thanks,
> >> Alex.
> >
> >
>
>
> .
>