Inconsistency of Export to a CSV file.

  • Thread starter Thread starter Jack K.
  • Start date Start date
J

Jack K.

Hi, all the expert. Please help...
I code the Docmd statement like this:
dim a, sTableName, sBackupFile as string

a = "TblExportToMIP Export Specification"
sTableName = "tblExportToMIP"
sBackupFile = "N:\Child Support Expense
Data\MIPExport\Willits2202004.csv"

DoCmd.TransferText acExportDelim, a, sTableName,
sBackupFile, False, ""

Result: Sometimes work, sometimes don't. I did not change
anything at all. However if I debugged and had it stopped
executing at the DoCmd, it will work every time. I am
really confuse what ACCESS has inconsistency.
Is any way I can solve this problem?
Thanks so much if you can help me!
Jack K.
 
You can't code variables like this and expect them all to be Strings. (The
first 2 are Variants.)
dim a, sTableName, sBackupFile as string

Split them up on one line each or use the As String on each of them.
 
Hi, Mr. Fallon
I did try both of your suggestions but it still has the
same problem. Is it possible the export data has the date
format caused the problem?
I am really no confidence in ACCESS. I am using the
ACCESS 2002. Is it possible any fix from MicroSoft.
Please give me some idea or different way to copy or
export my table to a CSV file only.
Thank you,
Jack
 
Here is one way using code:

Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub
 
However if I debugged and had it stopped
executing at the DoCmd, it will work every time.
Click to expand...

If code works when you step through it in the debugger but fails when
run normally, it could be a timing isssue of some kind. It won't do any
harm to try
DoEvents
before the DoCmd.TransferText.
 
Hi, Mr. Fallon
I will try it. However I wonder about your sample
coding. I want the file as a CSV file, not TXT file. Is
your code will give me a CSV file.
Also when I typed the Set rs = CurrentDb.OpenRecordset
(strTable, dbopensnapshot), the word dbopensnapshot did
not give me the Upper case like dbOpenSnapshot. Why? Do I
have to have the reference to some DAO dll or what else?
Thank you for your help.
Jack K.
 
A CSV file is a txt file with a different extension.
The data is comma delimited is all.

The code is an outline not a copy/paste solution.
You have to do some work! <g>
 
Back
Top