W
Wendy Clarkson
Hi
I am trying to read a text file into a recordset. It starts out ok, finds
the file in the folder, opens it copies the data into a storage buffer but
then fails to put it into the recordset.
I am using Windows XP SP1, Access 2003 SP1, this is a copy of the code, can
anyone help me with upgrading it please?
Wendy
Public FILEPATH As String
Public CURRENTFILE As String
Public INTCOUNT As Integer
Public RECBUFF(67)
Option Compare Database
Option Explicit
Function LTR_Filter()
FILEPATH = DLookup("LTRPATH", "TBLPREFS") & DLookup("FILEFILTER",
"TBLPREFS")
CURRENTFILE = Dir$(FILEPATH)
While CURRENTFILE <> ""
FILEPATH = DLookup("LTRPATH", "TBLPREFS") & DLookup("FILEFILTER",
"TBLPREFS")
CURRENTFILE = Dir$(FILEPATH)
Open DLookup("LTRPATH", "TBLPREFS") & CURRENTFILE For Input As #1
While Not EOF(1)
LTR_READ
Wend
Close #1
Kill DLookup("LTRPATH", "TBLPREFS") & CURRENTFILE
CURRENTFILE = Dir$(FILEPATH)
Wend
DoCmd.OutputTo acOutputQuery, "TEST", acFormatXLS, "F:\Letters\TEST.xls"
End Function
Sub LTR_READ()
On Error GoTo err_readfile
' READ RECORD
For INTCOUNT = 1 To 50
Line Input #1, RECBUFF(INTCOUNT)
Next INTCOUNT
' SETUP DATASET
Dim dbs As Database
Dim rstLTR As Recordset
Set dbs = CurrentDb
Set rstLTR = CurrentDb.OpenRecordset("TBLDETAILS", dbOpenDynaset)
'UPDATE RECORD (Parse Record)
With rstLTR
.AddNew
!issuedate = Mid$(RECBUFF(4), 5, 10)
!name = Mid$(RECBUFF(14), 5, 32)
!addline = Mid$(RECBUFF(15), 5, 32)
!notes01 = RECBUFF(43)
!extractedfrom = CURRENTFILE
.Update
Forms!frmimport!Account = Mid$(RECBUFF(5), 5, 9)
Forms!frmimport!File = CURRENTFILE
Forms!frmimport.Repaint
End With
OK:
Exit Sub
err_readfile:
Resume OK
End Sub
I am trying to read a text file into a recordset. It starts out ok, finds
the file in the folder, opens it copies the data into a storage buffer but
then fails to put it into the recordset.
I am using Windows XP SP1, Access 2003 SP1, this is a copy of the code, can
anyone help me with upgrading it please?
Wendy
Public FILEPATH As String
Public CURRENTFILE As String
Public INTCOUNT As Integer
Public RECBUFF(67)
Option Compare Database
Option Explicit
Function LTR_Filter()
FILEPATH = DLookup("LTRPATH", "TBLPREFS") & DLookup("FILEFILTER",
"TBLPREFS")
CURRENTFILE = Dir$(FILEPATH)
While CURRENTFILE <> ""
FILEPATH = DLookup("LTRPATH", "TBLPREFS") & DLookup("FILEFILTER",
"TBLPREFS")
CURRENTFILE = Dir$(FILEPATH)
Open DLookup("LTRPATH", "TBLPREFS") & CURRENTFILE For Input As #1
While Not EOF(1)
LTR_READ
Wend
Close #1
Kill DLookup("LTRPATH", "TBLPREFS") & CURRENTFILE
CURRENTFILE = Dir$(FILEPATH)
Wend
DoCmd.OutputTo acOutputQuery, "TEST", acFormatXLS, "F:\Letters\TEST.xls"
End Function
Sub LTR_READ()
On Error GoTo err_readfile
' READ RECORD
For INTCOUNT = 1 To 50
Line Input #1, RECBUFF(INTCOUNT)
Next INTCOUNT
' SETUP DATASET
Dim dbs As Database
Dim rstLTR As Recordset
Set dbs = CurrentDb
Set rstLTR = CurrentDb.OpenRecordset("TBLDETAILS", dbOpenDynaset)
'UPDATE RECORD (Parse Record)
With rstLTR
.AddNew
!issuedate = Mid$(RECBUFF(4), 5, 10)
!name = Mid$(RECBUFF(14), 5, 32)
!addline = Mid$(RECBUFF(15), 5, 32)
!notes01 = RECBUFF(43)
!extractedfrom = CURRENTFILE
.Update
Forms!frmimport!Account = Mid$(RECBUFF(5), 5, 9)
Forms!frmimport!File = CURRENTFILE
Forms!frmimport.Repaint
End With
OK:
Exit Sub
err_readfile:
Resume OK
End Sub