findfirst question

[tiger0268 via AccessMonster.com]

As a startup script, I am wanting to scan a drive for files and copy the
filenames into my table, which I have running fine, but I can seem to stop it
from copying files that are already in the table. My table is called
"tblFiles" and the first field is called "files", which has a text value. I
am just a little inexperienced with recordsets so any help would be greatly
appreciated.

Dim fldr, fls, fl
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.GetFolder("FileLocation")
Set fls = fldr.files
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblFiles")

On Error Resume Next
For Each fl In fls

rst.MoveFirst
rst.FindFirst "[files] = '" & fl.name & "'"

If rst.NoMatch Then
rst.AddNew
rst.Fields(0) = fl.Name
rst.Update
DoEvents
End If

Next fl
On Error GoTo 0
rst.close
Set rst = Nothing
Set db = Nothing

 

[tiger0268 via AccessMonster.com]

Alright, I changed out some code and got it to work, but is there a faster
method out there because it is scanning through a lot of files:

I changed:

rst.MoveFirst
rst.FindFirst "[files] = '" & fl.name & "'"

To:

rst.Index = "files"
rst.Seek "=", fl.Name

Open to any suggestions.

As a startup script, I am wanting to scan a drive for files and copy the
filenames into my table, which I have running fine, but I can seem to stop it
from copying files that are already in the table. My table is called
"tblFiles" and the first field is called "files", which has a text value. I
am just a little inexperienced with recordsets so any help would be greatly
appreciated.

Dim fldr, fls, fl
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.GetFolder("FileLocation")
Set fls = fldr.files
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblFiles")

On Error Resume Next
For Each fl In fls

rst.MoveFirst
rst.FindFirst "[files] = '" & fl.name & "'"

If rst.NoMatch Then
rst.AddNew
rst.Fields(0) = fl.Name
rst.Update
DoEvents
End If

Next fl
On Error GoTo 0
rst.close
Set rst = Nothing
Set db = Nothing

 

[Jason Lepack]

I removed:
On Error Resume Next

And got this error:
3251 - "Operation is not supported for this type of object" on the
findFirst statement.

So, I modified this line and it worked fine:
Set rst = db.OpenRecordset("tblFiles", dbOpenDynaset)

Cheers,
Jason Lepack

 

[tiger0268 via AccessMonster.com]

Thanks...it works with the findfirst command now. One more question though:

The drive it is scanning files from has about 3000 files and increases daily..
..is their a quicker way for this routine to take place because it currently
takes it a while to loop through all the files and it hogs the computers
processor?

I removed:
On Error Resume Next

And got this error:
3251 - "Operation is not supported for this type of object" on the
findFirst statement.

So, I modified this line and it worked fine:
Set rst = db.OpenRecordset("tblFiles", dbOpenDynaset)

Cheers,
Jason Lepack

As a startup script, I am wanting to scan a drive for files and copy the
filenames into my table, which I have running fine, but I can seem to stop it

[quoted text clipped - 34 lines]