Load files from a directory into a database

R

Richard ROV

Hello

I have been looking at samples and such and still cannot find an example for
this.

What I would like to do is loop through a directory and add files to a
recordset.

From what I understand this should be possible with a loop and an additem,
am I on the right track?
 
D

Douglas J. Steele

Are you talking about a disconnected recordset, or do you actually want to
add the files to a table in your database?

For the latter, I'd do something like the following untested air-code (which
uses DAO, because that's what I always use with Jet databases):

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strFile As String
Dim strFolder As String

strFolder = "C:\MyData\" ' Note that the closing slash is important here

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT Folder, File FROM MyTable")

strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
With rsCurr
.AddNew
!Folder = strFolder
!File = strFile
.Update
End With
strFile = Dir
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

On the other hand, I'd probably just use an INSERT query, rather than a
recordset:

Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:\MyData\" ' Note that the closing slash is important here

strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
strSQL = "INSERT INTO MyTable (Folder, File) " & _
"VALUES('" & strFolder & "', '" & strFile & "')
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir
Loop

I don't normally use disconnected recordsets, so it would take me a little
longer to cobble together an example, but hopefully the first sample above
gives you the general idea.
 
R

Richard ROV

Thanks for the response.

I found this example in the Knowledge Base

http://support.microsoft.com/default.aspx?scid=kb;en-us;198466

This example helped and if I comment out the [OLEFile].Class it runs.

--
Peace,

Richard


Douglas J. Steele said:
Are you talking about a disconnected recordset, or do you actually want to
add the files to a table in your database?

For the latter, I'd do something like the following untested air-code (which
uses DAO, because that's what I always use with Jet databases):

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strFile As String
Dim strFolder As String

strFolder = "C:\MyData\" ' Note that the closing slash is important here

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT Folder, File FROM MyTable")

strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
With rsCurr
.AddNew
!Folder = strFolder
!File = strFile
.Update
End With
strFile = Dir
Loop

rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

On the other hand, I'd probably just use an INSERT query, rather than a
recordset:

Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:\MyData\" ' Note that the closing slash is important here

strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
strSQL = "INSERT INTO MyTable (Folder, File) " & _
"VALUES('" & strFolder & "', '" & strFile & "')
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir
Loop

I don't normally use disconnected recordsets, so it would take me a little
longer to cobble together an example, but hopefully the first sample above
gives you the general idea.
Click to expand...
 
Top