Variable from a Txt File

[jules00023]

I am a newbie. I have a spreadsheet structured like this:
id.......name......dpt1.........dpt2
001....bob........1/2/11......1/5/11

I need it to be like this
id........name......shottype.......date
001.....bob........dpt1...............1/2/11
001.....bob........dpt2...............1/5/11

I have a text file called C:\vacc.txt. In the file I have listed all the possible vaccinations like this:
dpt1
dpt2

I am trying to use the following code,but it doesn't work. The variable is read from the txt file but it is not recognized in the query. I've tried it with double quotes, single quotes, single and double quotes. I'm lost. I thought maybe it was because the field value is actually a date, but I'm passing a string into the query????

Sub ReadAsciiFile()

Dim sFileName As String
Dim iFileNum As Integer
Dim mysbuf As String

Dim strSQL2 As String

Set db = CurrentDb
sFileName = "C:\vacc.txt"

strSQL2 = "INSERT INTO VACCINATIONS ( SIS_NUMBER, SCHOOL_YEAR, SCHOOL_CODE, VACCINATION, DOSAGE_DATE, COMMENT, EXEMPT_REASON, SOURCE )SELECT DistrictImmun.StudentID, ""2010"", RIGHT(DistrictImmun.SchoolID,3), ""DTP1"", '" & mysbuf & "', DistrictImmun.Note, ""yyyy"", DistrictImmun.Documentation FROM DistrictImmun"

' does the file exist?
If Len(Dir$(sFileName)) = 0 Then
Exit Sub
End If

iFileNum = FreeFile()
Open sFileName For Input As iFileNum

Do While Not EOF(iFileNum)
Line Input #iFileNum, mysbuf

Debug.Print mysbuf

MsgBox mysbuf
MsgBox strSQL2
db.Execute strSQL2

Debug.Print strSQL2
Loop


' close the file
Close iFileNum

End Sub

 

[hgus393]

Hi if you have a text file you could just manipulate it by choosing what positions you want read... and how you want to read them...hard to help you without the actual txt file..

See below for an example of reading in a txt file:

Sub Txtrd   
 s As String
    Dim rs As Recordset
    Dim i As Integer
Dim sPath as string
      sPath = "C:\vacc.txt"
 
    If Dir(sPath) = "" Then
                Exit Sub
    End If
 
    Set rs = CurrentDb.OpenRecordset("Your table")
 
    i = 1
    Close #1
    Open sPath For Input Lock Read As #1
    Do While Not EOF(1)
        Line Input #1, s
            rs.AddNew
            rs!Sis_Number= left(s,3) 
            rs!SCHOOL_YEAR = Trim(Mid(s, START POSITION OF TEXT, LENGTH OF TEXT))
           etc..
            rs.Update
            i = i + 1
            Loop
 
    Close #1
    rs.Close
    Set rs = Nothing

Cheers
Rob