Hi,

Does anyone know how to export data from access to a .dat file using vba. I'm able to export to a .csv file but can't seem to find the way to export to a .dat file.

Thank you.

Chantal

Can I ask why you want to export to .dat? Aren't those index files that the system creates?

mmcdonal

The content of the file is a simple text file.
We simply need to rename a .csv file to a .dat file

To the best of my knowledge, the .dat extension is simply a generic extension used by many programs to indicate a plain ascii text file. It doesn't indicate any particular file format. You need to establish the format yourself. If you already have your data in a .csv file, simply changing the file extension to .dat should'nt alter the contents of your .csv file in any way. A little string parsing code that lops off the .csv extension and replaces it with .dat might be all you need.

Or, you could write code to export your data to a .dat file, and format the data as comma seperated values yourself. The code below uses basic I/O Open, Print and Close methods.

Public Sub ExportDatFile()

    Dim rst As ADODB.Recordset
    Dim strLine As String

    Set rst = New ADODB.Recordset

    With rst
        .ActiveConnection = CurrentProject.Connection
        .Source = "Categories"
        .Open Options:=adCmdTableDirect
    End With

    Open "C:\Data\Categories.dat" For Output As #1

    Do Until rst.EOF
        strLine = _
            rst.Fields("CategoryID").Value & ", " & _
            rst.Fields("CategoryName").Value & ", " & _
            rst.Fields("Description").Value

        Print #1, strLine
        rst.MoveNext
    Loop

    Close #1
    rst.Close

    Set rst = Nothing

End Sub

What you get is a csv formatted .dat (ascii text) file that can be viewed in any text editor, such as NotePad, and looks like:

1, Beverages, Soft drinks, coffees, teas, beers, and ales
2, Condiments, Sweet and savory sauces, relishes, spreads, and seasonings
3, Confections, Desserts, candies, and sweet breads
4, Dairy Products, Cheeses
5, Grains/Cereals, Breads, crackers, pasta, and cereal
6, Meat/Poultry, Prepared meats
7, Produce, Dried fruit and bean curd
8, Seafood, Seaweed and fish

When working with a .dat file, you need to know how it has been formatted up front in order to parse it correctly.

HTH,

Bob

Perhaps a better way to change to extension would simply be to make a copy of the file as follows:

Public Sub ChangeFileExtension()

    Dim SourceFile, DestinationFile

    SourceFile = "C:\Data\Categories.csv"
    DestinationFile = "C:\Data\Categories.dat"
    FileCopy SourceFile, DestinationFile

End Sub

The content of the two files will be identical when opened in a text editor.

A final sub that accepts arguments to convert multiple .csv files in a directory to .dat files in the same or another directory:

Sub CopyFiles(strFilesToCopy As String, strDestinationDirectory As String)

    ' Call with CopyFiles "C:\Data\*.csv", "C:\Data"

    Dim strSourceFile As String
    Dim strDestinationFile As String
    Dim strSourceDirectory As String ' "C:\Data\"

    strSourceDirectory = Left$(strFilesToCopy, (InStr(strFilesToCopy, "*") - 1))

    strSourceFile = Dir(strFilesToCopy)

    ' loop through files in directory locating all .csv files
    Do While strSourceFile <> ""

        ' parse file name
        strDestinationFile = Replace$(strSourceFile, ".CSV", ".DAT")
        FileCopy strSourceDirectory & strSourceFile, _
            strDestinationDirectory & "\" & strDestinationFile

        strSourceFile = Dir

    Loop

End Sub

Thank you very much,

I used your second suggestion: it is short and simple and works very well.

Chantal