importing a .dat file into access

[philfer]

Hello,

I have been importing a few files into access using DoCmd.TransferSpreadsheet. This has worked well for Excel 2007 files.

I now want to import a .dat file but have had no luck using this or DoCmd.TransferText

Any ideas

Cheers
Phil

[OBP]

Phil, what problem are you having with the transfer text?
Have you considered opening the file and "reading" the data?
What type of .dat file is it?

[philfer]

Hi

Thanks for your post

When i try transfertext i have tried using the delim and the fixed option but it doesnt work in either case. The transferspreadsheet works like a dream for excel files

How would i open the file and read from it?

Thanks again
Phil

[OBP]

Here is an example of Importing from a .txt or .dat file but it requires you to know the structure of the file.

Dim fs, f, teststring As String, linecount As Integer, maxcount As Integer, rs As Object
Open "c:\Users\tammy\Desktop\blanktest.txt" For Input As #1 ' Open file.
linecount = 0

Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
linecount = linecount + 1
Loop
Close #1 ' Close file.

maxcount = linecount
linecount = 0
MsgBox maxcount
Open "c:\Users\tammy\Desktop\blanktest.txt" For Input As #1 ' Open file.
Set rs = CurrentDb.OpenRecordset("Table1")

For linecount = 1 To maxcount - 1 ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
If linecount <> 1 Then
teststring = TextLine ' Print to the Immediate window.
MsgBox teststring
With rs
.AddNew
!Field1 = Left(TextLine, 9)
!Field2 = Mid(TextLine, 10, 11)
!Field3 = Mid(TextLine, 22, 5)
.Update
.Bookmark = .LastModified
End With
rs.MoveNext
End If
Next linecount
rs.Close
Set rs = Nothing
Close #1 ' Close file.

You can of course import it 1 character at a time.