Line Input Problem

W

Wendy

I am importing data into recordset from a text file the only problem is I
don't want the data which is on every other line. I tried to kill the line
if the data = what I didn't want but that didn't work. This is my code so
far.

Thanks

Wendy

Private Function UPDPA()

Dim RSTPar As Recordset 'recordset name
Set dbs = CurrentDb
Set RSTPar = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)
'select table and db
Filename = "C:\import\ddirtest.txt" 'specifies file name
Open Filename For Input As #1
Do While Not EOF(1)
Line Input #1, Dataline
If Left(Dataline, 5) = "STORE" Then
dirnameline = Mid(Dataline, 30, 4)
Else
Dataline = Mid(Dataline, 16, 25)
End If
'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = dirnameline
!Filename = Trim(Dataline)
.Update
End With
Loop
Close #1
Kill Filename
End Function
 
R

Roger Carlson

Well, if you are **absolutely certain** that you only want ever other line,
you can just add an addtional Line Input line just before Loop statement:
Do While Not EOF(1)
Line Input #1, Dataline
If Left(Dataline, 5) = "STORE" Then
dirnameline = Mid(Dataline, 30, 4)
Else
Dataline = Mid(Dataline, 16, 25)
End If
'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = dirnameline
!Filename = Trim(Dataline)
.Update
End With
Line Input #1, Dataline
Loop

However, I'm always a little leery of hard coded solutions. Is there some
way of identifying the lines that you don't want? If so, you could put an
IF statement around your current IF statement to skip the line.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
W

Wendy

Hi Roger

I thought missing every other line would be the easiest way, but what I
would really like would be delete the line if it contains "BACKUPPRI" or
"RECOVERYF". I know a little bit about vb buts thats getting beyond me!

Thanks

Wendy
 
R

Roger Carlson

Well in that case, you'd just use the InStr function to test for those
values. Something like this:

Private Function UPDPA()

Dim RSTPar As Recordset 'recordset name
Set dbs = CurrentDb
Set RSTPar = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)
'select table and db
Filename = "C:\import\ddirtest.txt" 'specifies file name
Open Filename For Input As #1
Do While Not EOF(1)
Line Input #1, Dataline
If (InStr(Dataline,"BACKUPPRI")=0 AND InStr(Dataline,"RECOVERYF")=0)
Then
If Left(Dataline, 5) = "STORE" Then
dirnameline = Mid(Dataline, 30, 4)
Else
Dataline = Mid(Dataline, 16, 25)
End If

'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = dirnameline
!Filename = Trim(Dataline)
.Update
End With
End If
Loop
Close #1
Kill Filename
End Function


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
W

Wendy

Roger

Once its tested for those values can it delete the line(s) with those
values on them? I'm now getting a error because its trying to update the
field with a zero length string. Run-time error '3315' Field
'tblPrintArchive.Filename' cannot be a zero length string.

Wendy
 
R

Roger Carlson

You don't need to delete the line. You just need to skip it. (That is NOT
import it.)

As for the error, I don't see how it coud be happening. Are you using the
function as I wrote it? Or is it modified? Is the Update code within the
IF statement? Can you post your current code?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
W

Wendy

Hi Roger

The only thing I've changed is I had to move the 'Then' after the IF Instr
command because it red-lined it. I do have the fields set as required = Yes
in the table design. When I press the button to run the function it gives
the error "Run-time error '3315' Field 'tblPrintArchive.Filename' cannot be
a zero length string", when I click the debug button it highlights the
..Update line.

Wendy


Private Function UPDPA()

Dim RSTPar As Recordset 'recordset name
Set dbs = CurrentDb
Set RSTPar = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)
'select table and db
Filename = "C:\Import\ddirtest.txt" 'specifies file name
Open Filename For Input As #1
Do While Not EOF(1)
Line Input #1, Dataline
If (InStr(Dataline, "BACKUPPRI") = 0 And InStr(Dataline,
"RECOVERYF") = 0) Then
If Left(Dataline, 5) = "STORE" Then
dirnameline = Mid(Dataline, 30, 4)
Else
Dataline = Mid(Dataline, 16, 25)
End If

'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = dirnameline
!Filename = Trim(Dataline)
.Update
End With
End If
Loop
Close #1
Kill Filename
End Function
 
R

Roger Carlson

OK, all we were testing for was the presence of BACKUPPRI and RECOVERYF. By
our logic, if it found either of those two strings, it skipped the line.
Otherwise it continued to parse it. So now, we also have to test for a
blank line. Something like this:

If (InStr(Dataline, "BACKUPPRI") = 0 And InStr(Dataline, "RECOVERYF") = 0
And Len(Dataline) > 0) Then

(Again, you'll have to put them all on the same line because the newgroup
post will split it.)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help changing programming 14
Excel ignoring legitimate code, AGAIN! 1
Blank lines on line input 1
DAO edit and update problem 0
Invalid Use of Null 3
Need help with Application.FileSearch change 2
Line Input 3
Recordset/Line Input Problem 2

Top