importing large txt file to multiple tables

[Sarah]

Is it possible to import a .txt file with more than 255
delimited values to multiple tables? I have a Word form
with 300+ data entry values per form, and need to import
the form data to Access. Since Access has a max 255
fields per table, I know I need to somehow split the .txt
or tell Access to import the first half to one table and
the second half to a second table. Any suggestions or
sample code?

 

[Joe Fallon]

You cannot do it with the wizard.

You should be able to write code for it.

Here is an outline that should get you started:
(Hint - use 2 recordsets if you need to add data to 2 tables.)
====================================

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub