ACC 2000: VBA Import Text Files into Memo field

[Guest]

With Access 2000, is there a way to use VBA to import the contents of a Text
file into a Memo field of a table?

 

[Dirk Goldgar]

In

With Access 2000, is there a way to use VBA to import the contents of
a Text file into a Memo field of a table?

It's easy enough to do using a bound text box on a form. For example:

'----- start of example code -----
Dim strFileName As String
Dim intFileNo As Integer

strFileName = Me.txtFilePath ' full path to file

intFileNo = FreeFile()
Open strFileName For Input As #intFileNo
Me.txtFileData = Input(LOF(intFileNo), intFileNo)
Close #intFileNo

'----- end of example code -----

Note that the example code doesn't check the size of the file to make
sure it's not too big to save.

 

[Guest]

I actually accomplished this using the FileSystem Object along with a
recordset object to put the text file's contents into the table. My sample
code is below:

Dim streamCurrent As TextStream
Dim fso As FileSystemObject
Dim objFolder As Folder
Dim objTextFilesIncoming As Files
Dim objTextFile As File
Dim rst As DAO.Recordset

Dim sPathText As String
sPathText = "C:\TEMP\VBAPDFTester\Export Text"
Set rst = CurrentDb.OpenRecordset("Table1")

' Loop through the Text files to insert the data into the Table
For Each objTextFile In objTextFilesIncoming
' Open the file as a stream object
Set streamCurrent = objTextFile.OpenAsTextStream(ForReading,
TristateUseDefault)
' Insert the Data from the Text File. Use a Recordset so that quotes
get inserted properly.
rst.AddNew
rst!FullText = streamCurrent.ReadAll
rst.Update

' Close the Text Stream object so we can delete the file after we
inserted the text into the database
streamCurrent.Close
Set streamCurrent = Nothing

'objTextFile.Copy sProcessedFileName
'objTextFile.Delete True
Next

 

[Dirk Goldgar]

In

I actually accomplished this using the FileSystem Object along with a
recordset object to put the text file's contents into the table. My
sample code is below:

Dim streamCurrent As TextStream
Dim fso As FileSystemObject
Dim objFolder As Folder
Dim objTextFilesIncoming As Files
Dim objTextFile As File
Dim rst As DAO.Recordset

Dim sPathText As String
sPathText = "C:\TEMP\VBAPDFTester\Export Text"
Set rst = CurrentDb.OpenRecordset("Table1")

' Loop through the Text files to insert the data into the Table
For Each objTextFile In objTextFilesIncoming
' Open the file as a stream object
Set streamCurrent = objTextFile.OpenAsTextStream(ForReading,
TristateUseDefault)
' Insert the Data from the Text File. Use a Recordset so that
quotes get inserted properly.
rst.AddNew
rst!FullText = streamCurrent.ReadAll
rst.Update

' Close the Text Stream object so we can delete the file after we
inserted the text into the database
streamCurrent.Close
Set streamCurrent = Nothing

'objTextFile.Copy sProcessedFileName
'objTextFile.Delete True
Next

Yes, that's a perfectly valid way of doing it. If I'd known exactly
what you had in mind, I'd have written similar code. I would use the
simple VB I/O statements I posted, in preference to the scripting
objects, in the interest of simple efficiency and
reference-independence, but it's no big deal.