Problem with an exportscheme

[DJRave]

Hey there,

I'm having some problems with an exportscheme. I've made a scheme by which
some files need to be exported as textfiles. It works great but the only
problem is that the "" as textseperators keep on being exported eventhough
I've turned them off in the scheme.

the content of the textfile I export looks like this:
"D1";"20070701";"20070701";"1234567890";"";"";""
"D2";"20070701";"20070701";"0987654321";"";"";""
etc.

It needs to look like this:
D1;20070701;20070701;1234567890;;;
D2;20070701;20070701;0987654321;;;
etc.

Does anybody know how I can prevent this from happening?

Thanks a lot

 

[Roger Carlson]

I'm not following what you mean by "scheme". If you create an Export File
Specification (or even do it manually), you should be able to set the Text
Qualifier to {none} and the quote marks won't be exported.

--
--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/wa.exe?SUBED1=ACCESS-L

 

[DJRave]

Hey Roger,

Thanks for your reply. I meant specification and I had allready created one
where the quotes are turned off the quotes but still it does. When I manually
export the entire table according to the specification everything works but
as soon as I run my exportscript according to the specification it doesn't.
Maybe a copy of the script can help?

Function Export() As Long

Dim db As DAO.Database
Dim rsProduct As DAO.Recordset
Dim strSql As String
Dim strfile As String
Dim lngCount As Long
Const strcPath = "B:\Export\"
Const strcQuery4Export = "ExportFiles"

Const strcStub = "SELECT .Prefix, Sales. Date1, Sales.Date2,
Sales.Code1, Sales.Code2, Sales.SupplierID, Sales.Category, Sales.Remark,
Sales.Producttype, Sales.Name " & _
"FROM Sales WHERE (((SupplierID = '"
Const strcTail = "')AND(Producttype='E') AND (Code1= '"
Const strcTail2 = "'))) ORDER BY SupplierID, Code1, Producttype;"

Set db = CurrentDb()
strSql = "SELECT DISTINCT SupplierID, Producttype, Date1, Code1 FROM
Sales " & _
"WHERE ((SupplierID Is Not Null) AND (Producttype = 'E'));"
Set rsProduct = db.OpenRecordset(strSql)

Do While Not rsProduct.EOF
strSql = strcStub & rsProduct![SupplierID] & strcTail &
rsProduct![Code1] & strcTail2
db.QueryDefs(strcQuery4Export).SQL = strSql
strfile = strcPath & rsProduct![Date1] & "_" &
rsProduct![SupplierID] & "_" & rsProduct![Code1] & "_" &
rsProduct![Producttype] & ".txt"


DoCmd.TransferText acExportDelim, ExportMismatch, strcQuery4Export,
strfile
lngCount = lngCount + 1
rsProduct.MoveNext
Loop

rsProduct.Close
Set rsProduct = Nothing
Set db = Nothing

ExportProducts = lngCount
End Function

 

[Roger Carlson]

Well, I don't see anything off hand that would affect your export, assuming
that ExportMismatch is the correct Export Spec. If you can't figure this
out, you could try creating your own export routine. It's not all that
difficult. Something like this in place of the TransferText line:

Open strFile For Output As #1
Set rsQuery4Export = db.OpenRecordset("strcQuery4Export")
'Print #1, "This is where header information would go if needed"
rsQuery4Export.MoveFirst
Do While Not rsQuery4Export.EOF
MyString = rsQuery4Export!Field1 & ", " & _
rsQuery4Export!Field2 & ", " & _
rsQuery4Export!Field3 & ", " & _
rsQuery4Export!Field4 & ", " & _
rsQuery4Export!Field5
Print #1, MyString
rsQuery4Export.MoveNext
Loop
Close #1

Of course, you would replace Field1, Field2, etc with your actual field
names and you would build your string expression with whatever formatting
you desire. Also, you wouldn't actually need to save the strcQuery4Export
query, you could create the recordset directly from the strSQL variable.
Lastly, the most elegant thing would be to create this as a separate
subroutine and send in the parameter values just like the TransferText
method.

--
--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/wa.exe?SUBED1=ACCESS-L

Hey Roger,

Thanks for your reply. I meant specification and I had allready created
one
where the quotes are turned off the quotes but still it does. When I
manually
export the entire table according to the specification everything works
but
as soon as I run my exportscript according to the specification it
doesn't.
Maybe a copy of the script can help?

Function Export() As Long

Dim db As DAO.Database
Dim rsProduct As DAO.Recordset
Dim strSql As String
Dim strfile As String
Dim lngCount As Long
Const strcPath = "B:\Export\"
Const strcQuery4Export = "ExportFiles"

Const strcStub = "SELECT .Prefix, Sales. Date1, Sales.Date2,
Sales.Code1, Sales.Code2, Sales.SupplierID, Sales.Category, Sales.Remark,
Sales.Producttype, Sales.Name " & _
"FROM Sales WHERE (((SupplierID = '"
Const strcTail = "')AND(Producttype='E') AND (Code1= '"
Const strcTail2 = "'))) ORDER BY SupplierID, Code1, Producttype;"

Set db = CurrentDb()
strSql = "SELECT DISTINCT SupplierID, Producttype, Date1, Code1 FROM
Sales " & _
"WHERE ((SupplierID Is Not Null) AND (Producttype = 'E'));"
Set rsProduct = db.OpenRecordset(strSql)

Do While Not rsProduct.EOF
strSql = strcStub & rsProduct![SupplierID] & strcTail &
rsProduct![Code1] & strcTail2
db.QueryDefs(strcQuery4Export).SQL = strSql
strfile = strcPath & rsProduct![Date1] & "_" &
rsProduct![SupplierID] & "_" & rsProduct![Code1] & "_" &
rsProduct![Producttype] & ".txt"


DoCmd.TransferText acExportDelim, ExportMismatch, strcQuery4Export,
strfile
lngCount = lngCount + 1
rsProduct.MoveNext
Loop

rsProduct.Close
Set rsProduct = Nothing
Set db = Nothing

ExportProducts = lngCount
End Function