This problem is not uncommon. Wizards are quite useful but can only be
so generic. The workaround for this limitation is to develop a routine
which is a little bit less generic for your purposes (vb.Net is
notorious for limited wizardry - but a great platform!).
I am sharing a somewhat generic VBA code routine which uses DAO code
which you can copy and pasted into an Access Standard Code Module or a
Form class module in a command button. What this routine does is to
bulk read data from a text file, line by line, into a table called
"Table1". Table1 contains only 1 field (you can name the field anything
you want) which is a Memo data type (memo so that if the line is several
thousand chars in length the field will still accept all of the text
from the line). Then, of course, you have to parse the data. In my
example I am reading data from a comma delimited text file. Table2 will
contain the parsed data from table1. You need to know how many columns
are in your text file in advance for this routine (I could make it more
custom, but it is just an example). Then you create Table2 with the
same number of columns. Dont worry about the datatypes. Just make
everything Text datatype, and allow for zero length string and
everything is "Not Required". Here is the routine:
Sub GetDataFromTextFile()
Dim DB As Database, RS1 As Recordset, RS2 As Recordset
Dim strPath As String, str1 As String, str2 As String
Dim i As Integer, j As Integer, k As Integer
Dim bContinue As Boolean
'empty out Tables1 and 2 incase you reuse this routine
DoCmd.RunSQL "Delete Rows From Table1"
DoCmd.RunSQL "Delete Rows From Table2"
Set DB = CurrentDb
Set RS1 = DB.OpenRecordset("Table1")
strPath = Left(DB.Name, Len(DB.Name) - Len(Dir(DB.Name)))
Open strPath & "\TestFile.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, str1
RS1.AddNew
RS1(0) = str1
RS1.Update
Loop
Close #1
RS1.MoveFirst 'move record point to beginning of Table1
Set RS2 = DB.OpenRecordset("Table2")
Do While Not RS1.EOF
j = 1 'j demarks the place after each delimiter
k = 0 'k is the field counter for Table2
bContinue = True 'continue while the line contains
str1 = RS1(0) 'a delimiter, str1 will contain the
RS2.AddNew 'line from Table1
Do While bContinue 'here you iterate through str1 and
i = InStr(j, str1, ",") 'use Instr Function to demark
If i = 0 Then 'comma delimeter position
bContinue = False 'if i=0 that means end of delimiter
str2 = Mid(str1, j, Len(str1) - j) 'use Mid function
Else 'to retrieve contents between delimeters
str2 = Mid(str1, j, i - j)
End If
Debug.Print str2
RS2(k) = str2
j = i + 1
k = k + 1
Loop
RS2.Update
RS1.MoveNext
Loop
RS1.Close
RS2.Close
End Sub
To test this out, create a table, tableX, with say 5 fields. Add some
fake test data, test1, test2, ... Export this to a text file called
"TestFile.txt". So now you know that you have 5 fields. Create Table1
with one field, memo datatype, Table2 with 5 field, all text datatype,
zero length allowed, everything not required. Oh, this routine also
assumes that the text file reside in the same directory as your mdb
(strPath & "\TestFile.txt"). Note: if your text file is Tab delimited,
replace "," with vbTab in the InStr function
i = InStr(j, str1, vbTab)
Good luck.
Rich
*** Sent via Devdex
http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
