Probably the best way to do this is through VB Low-level I/O. Basically,
you read each line of your text file into a string variable and then parse
each field yourself. Something like this:
(NOTE: this assumes a comma delimited text file. Other delimiters or a
fixed-width file require modifications. Also, assume your textfile is named
LineInput.txt and is on the root of the C drive, and your tables are Table1,
Table2, and Table3)
Open "C:\LineInput.txt" For Input As #1
' Create a dynaset-type Recordset object based on Shoes table.
Set rst1 = dbs.OpenRecordset("Table1")
Set rst2 = dbs.OpenRecordset("Table2")
Set rst3 = dbs.OpenRecordset("Table3")
Do While Not EOF(1)
Line Input #1, MyString
'Add a new Record
rst1.AddNew
rst1!Field1 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst1!Field2 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst1!Field3 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst1.Update
rst2.AddNew
rst2!Field1 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst2!Field2 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst2!Field3 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst2.Update
rst3.AddNew
rst3!Field1 = Left(MyString, InStr(MyString, ",") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst3!Field 2= MyString
rst3.Update
Loop
' Close text file.
Close #1
On my website (
www.rogersaccesslibrary.com), is a small Access database
sample called "InputLineInput.mdb" which illustrates how to do this.
--
--Roger Carlson
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L