importing large txt file to multiple tables

  • Thread starter Thread starter Sarah
  • Start date Start date
S

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?
 
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
 
Back
Top