Importing text files into a table

[Stephen English]

We have three text files from a till every day - they are just text like what
would print on a cash registrer slip.
I have been able to read them line by line and extract the data into a table.
I now want to copy the text files into OLE Objects in a table.
If I create Table1 with an OLE object field and open the table and then go
to Windows Explorer and copy the file, I can paste it into the OLE object
field and then delete the original file and I have a copy in the database
which is what we want.

However, I want to do this in my code
I can get the file
Set f = fsoSysObj.GetFile(strPath & strFile)
and then
f.copy
copies it.
My question is how do I now insert that into my OLE Object field in the table?

Thanks
Stephen

 

[John Nurick]

Hi Stephen,

With text files, it's much simpler to store the contents of the file in
a memo field. The FileContents() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm will read the file
into a VBA variable. If you are using a bound form the code you use can
be as simple as this - which assumes you have the memo field bound to a
textbox txtMemo:

Me.txtMemo.Value = Filecontents(strPath & strFile)

or, with error reporting

Dim varText As Variant
Dim ErrCode As Long

varText = FileContents(strPath & strFile, 0, ErrCode)
If ErrCode <> 0 Then
MsgBox "Error " & ErrCode & ": " & Error(ErrCode), _
vbExclamation + vbOKOnly, "Problem reading file"
Else
Me.txtMemo.Value = varText
End If

 

[Stephen English]

Hi John
Thank you for your suggestion.
However, I was really looking for how to get it into the table field from
code not one at a time on a form.

However, I would still prefer to drop the text file into the database as an
OLE object rather than put the contents into a Memo field. The auditors
like it better.
Regards
Stephen

 

[John Nurick]

Hi Stephen,

The standard way of programmatically inserting something into an OLE
field is by automating a BoundObjectFrame control on a form. That way,
Access takes care of packing the something - whether a text file, Word
document, picture or whatever - into an OLE object.

Otherwise, you'll need (AIUI) to write or find code that puts an OLE
wrapper around the file and puts the result into a string variable; then
you open a recordset on the table and use AppendChunk() to insert the
string into the OLE field. Possibly there's something at www.lebans.com
that will help with the packing.

I still feel that it would be simpler to store the contents of the file
in a memo field, and placate the auditors by also storing a checksum
calculated from the contents of the original file. That way, any change
to the memo field can be detected by comparing its checksum with the
stored one.

BTW, the code snippets I posted assumed you'd be using a form, but you
can also use FileContents in an Access query or in recordset operations.