importing test file

[jlocke]

I need to inport this file into a table, but having troulble with the code. I would like to use the first part of the line as the table header using the semi colon to separate fields.

 

[crosmill]

Do you have to use code, access provide some pretty usefull importing tools that can be automated.

In any case always post your code.

 

[WayneRyan]

Joe,

Assuming that you have an existing table, the following is
a start.

I just typed it into NotePad, but I hope it helps.

' *******************************************
Private Sub ReadText_OnClick() ' Assign to a button on a form
Dim dbs As Database
Dim rst As RecordSet
Dim Buffer As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordSet("MyTable")

Open "Test.txt" for input as #1

Line Input #1, Buffer
While Not EOF(1)
If InStr(Buffer, "Name:", 1) > 0 Then
rst.AddNew
rst!Name = Mid(Buffer, 19, Len(Buffer)
End If
'
If InStr(Buffer, "Address:", 1) > 0 Then
rst!Address = Mid(Buffer, 19, Len(Buffer)
End If
'
' Repeat for rest of fields ...
'
If InStr(Buffer, "USLacrosseNumber:", 1) > 0 Then
rst!USLacrosseNumber = Mid(Buffer, 19, Len(Buffer)
rst.Update
End If
'
Line Input #1, Buffer
Wend

Close #1
Set rst = Nothing
Set dbs = Nothing
End Sub
' *******************************************

Regards,
Wayne

 

[Fornatian]

Quick tip

rst!Name = Mid(Buffer, 19, Len(Buffer)

It is unwise to use Name as a field name because it can conflict with the inbuilt Name property thus leading to unexpected results.

e.g. Me.Name would normally return the name of the relative form.

 

[WayneRyan]

Fornation,

You're right, I just did that quickly and used what preceded
the ":" as the rst!Name (drats, did it again).

Good advice.

Wayne