Option Compare Database
Option Explicit
Dim TextLine As String
Dim FieldCount As Integer
Private Sub SpecialImport_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim InFile As String
Dim Counter As Integer
'-- I'll leave it to you to point to the correct input file in InFile. I normally use this API...
'-- ...http://www.mvps.org/access/api/api0001.htm
'InFile = the correct file to import
Open InFile For Input As #1 ' Open the import file.
Set db = CurrectDB
Set rs = db.OpenRecordset("TheTableToFillWithTheImportData")
With rs
While Not EOF(1)
If GetRecord Then '-- Another record available
.AddNew '-- Start a new record in the import table
For Counter = 1 To FieldCount
Select Case left(GetField(Counter), 5) '-- Walk through the fields in the input
Case "AC120"
'-- Name Field
!NameField = Mid(GetField(Counter), 6)
Case "AC130"
'-- Address field
!AddressField = Mid(GetField(Counter), 6)
Case "BC120"
'-- Shipping Address
!ShipField = Mid(GetField(Counter), 6)
Case "BC130"
'-- Bankruptcy Info
!BankField = Mid(GetField(Counter), 6)
Case "AF120"
'-- Connection Status
!ConnField = Mid(GetField(Counter), 6)
Case Else
'-- Unknown field identity
End Select
Next Counter '-- Loop for the next field
.Update '-- Fields done, save the record
End If
Wend
.Close
End With
Set rs = Nothing
Close #1 ' Close file.
MsgBox "Import complete!"
End Sub
Function GetField(FieldNumber As Integer) As String
'-- Return the field in the TextLine buffer pointed to by FieldNumber
'-- FieldNumber is guaranteed to be a valid value.
GetField = Mid(TextLine, FieldNumber * 25, 25)
End Function
Function GetRecord() As Boolean
If Not EOF(1) Then
Line Input #1, TextLine '-- Read the next line into variable.
GetRecord = True '-- Indicate record available
FieldCount = Len(TextLine) \ 25 '-- Integer division
Else
GetRecord = False '-- Indicate no more records
End If
End Function
