Reading dat file from CD (1 Viewer)

[Robert88]

Hi All,

Another challenge to my life, reading a dat file from CD.

I have looked at other posts on reading .dat files and it is clear that to import a dat file is not possible through the import of files.

I have a standard file which for the purpose of the exercise I will call test.dat which always resides on the root folder of the CD.

I was wondering if it is possible since this file is on CD and I want to use a button on a form to read this files contents (it only contains one number EG. 2154578787454).

So if I break this process down I was wondering if the following is possible using VBA?

1. Copy the file from CD to local machine for temporary use.
2. change name extension from .dat to .txt

This will then allow me to read in the file using the import wizard after I create an import spec for the .txt file if the above is possible????

I hope it is, but shoot me down if it is not.

Look forward to any suggestions.

Robert88

 

[ghudson]

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

FileCopy "G:\test.dat", "C:\test.txt"

 

[Robert88]

Thank you

Hi GHudson,

Thanks for your code, sensational. You make everything so easy!!!

So I suppose the code below is my long winded method of doing it, I should of checked for your response earlier. I did not end up using the import as I found something new called "Text Streaming". I think in my case since there is only one number within the file this is possible but if I had to chop up the text stream I think I would of had to use an import spec with an import statement.

Like I did in this link http://www.access-programmers.co.uk/forums/showthread.php?t=96996

If I had of used your line it would of taken a whole lot less code, oh well I am learning......

Private Sub CmdReadTest_dat_Click()

    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Dim Drivename As String
    Dim fs, f, ts, s, n
    Dim dbs As Database, rst As Recordset

    Set dbs = OpenDatabase("C:\CD Labeller - Microsoft Access 2003\CDlabel.mdb")
    'Combo box is a list of Drive letters to select the CD Player Drive name.
    Drivename = Forms!frmMediaLabeller!CboDriveName & ":\" 

    Set fs = Application.FileSearch
    With fs
        .LookIn = Drivename
        .Filename = "*.dat"
        .SearchSubFolders = False
        If .Execute(SortBy:=msoSortByFileName, _
            SortOrder:=msoSortOrderAscending) > 0 Then
                    
        n = .FoundFiles(1)
                    
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.GetFile("n")
        Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
        s = ts.ReadLine
        'MsgBox s
         ts.Close

        'Insert the contents of Test.dat into tblTest_dat
        dbs.Execute " INSERT INTO tblTest_dat " _
                    & "(fldTest_dat)VALUES " _
                    & "('" & s & "');"
        MsgBox "File transfer from Test.dat to tblTest_dat_one was successful."
        Else
            MsgBox "There was no Test.dat found on the media."
        End If
    End With
    'close the database
    dbs.Close

End Sub

I appreciate you responding. This coding I have to say seems to be getting easier after many months of reading and learning, it is starting to roll off my fingers, maybe too much!!! I have to say the people on this site are very helpful, it is great.

Robert88