How to get external data into a table/form

[Bill W.]

I almost hate to ask this, it's so trivial. I am writing some code to
read a text file and place certain records (based on key words in the
file) into a form and table. I have the code written that reads the
text-file and parses out the keywords. What I can't remember (and all
my books are at home at the moment) is how to get the data into my
table and ultimately into my form.

Can someone point me to a code snippet someplace ? I sure would
appreciate it as I don't do this very often and just can't remember
the syntax.

TIA
Bill W

[Pieter Linden]

bw******@lucent.com (Bill W.) wrote in message news:<c3**************************@posting.google. com>...

I almost hate to ask this, it's so trivial. I am writing some code to
read a text file and place certain records (based on key words in the
file) into a form and table. I have the code written that reads the
text-file and parses out the keywords. What I can't remember (and all
my books are at home at the moment) is how to get the data into my
table and ultimately into my form.

Can someone point me to a code snippet someplace ? I sure would
appreciate it as I don't do this very often and just can't remember
the syntax.

TIA
Bill W

Whoa. Tables *store* data. Forms are for people to read/modify/enter
data into a table. Or are you automating data entry somehow here?

If you have code that reads and parses text file, why not just create
an append-only recordset and write your data straight to the table?
Is there a reason you need the form? (Well, except for indicating to
the user that "yes, my code is running. Please do not turn this
computer off!")

Look up the .AddNew method in the help. You may have to open a module
so you can get to the VBA help. There are tons of examples there. If
you're writing through to a linked SQL Server table, use ADO,
otherwise, it doesn't matter - you can use DAO.

[Rich P]

The easiest thing to do is to create a data pickup table with one field
- a memo field so that it doesn't matter how much data is in one row of
data from your text file. Then read the data in like this:

Dim TextLine as String, RS As Recordset

Set RS = CurrentDB.OpenRecordset("tblDataPickup")

Open "C:\Dir1\TESTFILE.txt" For Input As #1 'Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
RS.AddNew
RS(0) = TextLine
RS.Update
Loop
Close #1 ' Close file.

The Line Input function reads one entire row at a time. Once the data
is in your table you can parse it much more easily using the Instr
function. Instr and Line Input are in the Access Help files.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

You could try the Line Input Method for reading data from a text file.
The following example is based on an example from the Access Help Files.
Create a data pickup table that consists of just one field, a memo field
so that you don't have to deal with string size. You will be reading
each row of data in the textfile as one long string which you will parse
later inside of Access.

Sub GetTextFileData()
Dim MyString As String, MyNumber
Dim RS As Recordset
Set RS = CurrentDB.OpenRecordset("tblDataPickup")
Open "C:\dir1\Test1.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, MyString
RS.AddNew
RS(0) = MyString
RS.Update
Loop
Close #1 ' Close file.
End Sub

Then you can loop through the table and parse each field out based on
whatever the delimiter is - use Instr function.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!