Export .DAT file (1 Viewer)

toribiolm · Aug 20, 2014

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 · Aug 20, 2014

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

toribiolm · Aug 21, 2014

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 · Aug 21, 2014

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

toribiolm · Aug 21, 2014

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 · May 20, 2018

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 · May 20, 2018

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 · May 20, 2018

anski said:
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 · May 20, 2018

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 · May 20, 2018

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

anski · May 20, 2018

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.

Export .DAT file (1 Viewer)

toribiolm

New member

Uncle Gizmo

Nifty Access Guy

toribiolm

New member

JHB

Have been here a while

toribiolm

New member

anski

Registered User.

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

anski

Registered User.

isladogs

MVP / VIP

anski

Registered User.

Similar threads

Users who are viewing this thread