Export .DAT file

[toribiolm]

Hi,

I need to export a .dat file with delimited format (using ";")

I have tried the following:

DoCmd.TransferText acExportDelim, "ED File", "Employee Data Output", "c:\testfiles\file1.dat", False, ""

But when I execute the code the folloing error is shown:

Run-time error '3027'
Cannot update. Database or object is read-only

Using the same structure "DoCmd.TransferText" but with the .txt extension in the file works perfect.

Also I have tried export the .dat file using DoCmd.OutputTo, but even if the export is done, the format is not kept.

How can I export the .dat file keeping the delimited format needed?

Thanks,

Luis

:banghead:

 

[Uncle Gizmo]

have you tried changing the file extension from *.dat to *.txt ?

 

[toribiolm]

Hi Uncle Gizmo,

I have tried and when I use *.txt with DoCmd.TransferText works Ok. The only problem is when I try to export with the .dat file extension.

I need to export *.dat with format as later on the data is used by another program to import and is a third party which I cannot change.

thanks for your reply

Luis

 

[JHB]

Can't you just change the "txt" extension to "dat" or is the format of data different?

 

[toribiolm]

Hi, The error just appears when I change the "txt" extension to "dat" with DoCmd.TransferText

In the end I have found this solution that works for me:


Public Sub ChangeFileExtension()

Dim SourceFile, DestinationFile

SourceFile = "c:\testfiles\file1.txt"
DestinationFile = "c:\testfiles\file1.dat"
FileCopy SourceFile, DestinationFile
Kill "c:\testfiles\file1.txt"

End Sub


So basically, I create the .txt file using DoCmd.TransferText (which keep the format) and later I save a copy of the file using the .dat and delete the original .txt


Probably not the best solution, but it works. Happy to listen other options.

Thanks,

Luis

 

[anski]

i know this is an old thread. have you found a simpler solution to this?

i am in the same situation. i need to export a query to a csv format. docmd.transfertext /transfertext macro works as long as the file extension name is .CSV. i just need the final filename extension to be .XYZ. the formatting of the exported file is fine.

is there a way for me to do this without user intervention (user renames the exported file to myfilename.XYZ (from myfilename.csv)? tia.

 

[The_Doc_Man]

There is a verb you can use in VBA. Look up the "Name As" statement, which is the equivalent of the command window's "RENAME" verb.

 

[isladogs]

i know this is an old thread. have you found a simpler solution to this?

i am in the same situation. i need to export a query to a csv format. docmd.transfertext /transfertext macro works as long as the file extension name is .CSV. i just need the final filename extension to be .XYZ. the formatting of the exported file is fine.

is there a way for me to do this without user intervention (user renames the exported file to myfilename.XYZ (from myfilename.csv)? tia.

Why can't the renaming be done using code. No user intervention needed.

 

[anski]

i found the solution here: https://www.access-programmers.co.u...?t=235843&highlight=export+csv+file+extension

this is the code he used: Name "C:\Exports\MyFile.txt" As "C:\Export\MyFile.rpd"

 

[isladogs]

Which is exactly what the doc suggested and what I also meant.

 

[anski]

i am not articulate with syntax or code. it is easier for me when i see a sample syntax/code and i just tweak the latter based on our requirements. thanks anyway.