Access: Recordset, File I/O Example
By Richard Rost
18 years ago
Q: Hello, I need to insert a lot of of separate .HTML files into Access. Each HTML file would have to be inserted in a memo field. So that the memo field contains the HTML code. Until now I have been doing this with a keyboard macro which opens the HTML source code and copies the content into a memo field in an Access database. This is very time consuming and certainly not error free. I need to insert about 60000 HTML files which are all between 4 and 7 kb. Thank you for your time, Arthur
A: If these are stored as files on your hard drive (MyFile.HTM) then I would use basic File I/O to insert them into your database, using a RecordSet to do the actual input (you can't use a simple INSERT INTO query because of invalid characters like quotation marks).
I would do something like this:
' You must have Microsoft DAO x.x Object Library installed
' In your VB Editor, go to Tools > References.
' Make sure DAO is checked and has a HIGHER PRIORITY than
' the Microsoft ActiveX Data Objects x.x Library.
Dim FN As String
Dim S As String
Dim MyString As String
Dim db As database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("MyTable", dbopendynaset)
DoCmd.SetWarnings False
FN = Dir("C:\*.TXT", vbNormal)
While FN <> ""
Open "C:\" & FN For Input As #1
MyString = ""
While Not EOF(1)
Line Input #1, S
MyString = MyString & S & vbNewLine
Wend
rs.AddNew
rs!MyMemoField = MyString
rs.Update
Close #1
FN = Dir
Wend
DoCmd.SetWarnings True
A bit of an explanation... first you have to make sure you have the Microsoft DAO (Data Access Objects) library reference installed. I'm a little old-school. I like DAO better than ADO (ActiveX Data Objects). Yes, yes, yes, I'll cover both in my class, but when I need to throw something together quick, I still use DAO.
Then we DIM a bunch of local variables and set up a DB database connection and RS recordset connection to a table called MyTable (change to whatever your table is).
Next I turn off Access' warnings - otherwise each time you insert a record in a table you'll get a "you're about to insert 1 record" warning.
Then, I use the DIR command to loop through all of the TXT files in my C:\ folder. Now, if you're doing HTML files, you'll need to set this to either *.HTM or *.HTML whichever you're working with, and of course type in the complete folder name, like "C:\My Documents\*.HTML" or whatever.
Next, I open each file as we come to it, read in all of its lines, and save that information into the table using the recordset connection (rs.Addnew stuff).
Close the file, loop to the next record, and then you're done when you're out of files.
This whole thing took me about 10 minutes to write... and HALF AN HOUR to explain. Ha ha ha. Yes, I'll cover all of this in more depth when I cover RECORDSETS in my next Access tutorial.
I do cover the basic File I/O commands in my VISUAL BASIC 201 and 202 classes http://www.599cd.com?GOVB201 but I will also be going over them in the Access tutorials as well, since I have a lot of Access students who don't take (or need) just the VB lessons.
Hope this answers your question.
|
