FileSystemObject drive error

[Guest]

I am trying to create a routine to gather information about files. I found
the following code provided by the MS Scripting Guy:


Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Search.CollatorDSO;Extended
Properties='Application=Windows';"

'objRecordset.Open "SELECT System.FileName, System.Photo.DateTaken FROM
SYSTEMINDEX " & _
' "Where System.ItemFolderPathDisplay = 'C:\Europe' and
System.FileExtension = '.jpg'", _
' objConnection

Set fs = CreateObject("Scripting.FileSystemObject")

objRecordset.MoveFirst



‘Loop to gather file data…
‘


It works for folders and files on the C: drive. However, I have additional
drives and if I change the location from ‘C:\Europe’ to another drive (such
as ‘H:\Europe’) then I get an error 3021 in the line: objRecordset.MoveFirst

Why the error and how do I change the code do handle other drives?

Thanks,

David

 

[Dirk Goldgar]

In

I am trying to create a routine to gather information about files. I
found the following code provided by the MS Scripting Guy:


Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Search.CollatorDSO;Extended
Properties='Application=Windows';"

'objRecordset.Open "SELECT System.FileName, System.Photo.DateTaken
FROM SYSTEMINDEX " & _
' "Where System.ItemFolderPathDisplay = 'C:\Europe' and
System.FileExtension = '.jpg'", _
' objConnection

Set fs = CreateObject("Scripting.FileSystemObject")

objRecordset.MoveFirst



'Loop to gather file data.
'


It works for folders and files on the C: drive. However, I have
additional drives and if I change the location from 'C:\Europe' to
another drive (such as 'H:\Europe') then I get an error 3021 in the
line: objRecordset.MoveFirst

Why the error and how do I change the code do handle other drives?

Thanks,

David

That error seems to be saying that your recordset is empty. You should
guard against that by enclosing the recordset-navigation code in an If
block:

If objRecordset.EOF = False Then

' ... loop through the recordset ...

End If

Incidentally, you don't need to do the objRecordset.MoveFirst if you've
just opened the recordset, as it will automatically be positioned at the
first record if there is one.

Now, the remaining question is, why is the recordset empty? I can't
answer that, unless the "H:\Europe" folder doesn't actually exist.

 

[Guest]

Hi Dirk,

Thank you for your help.

I copied the folder from the C: drive to the K: drive so that the folder on
the K: drive has exactly the same files. Also, I opened the folder in
Explorer and then used copy/paste for the path name to be sure I don't have a
typo. Per your suggestion I have removed the "objRecordset.MoveFirst"
statement. With the MoveFirst statement gone there is no error statement but
the recordset is still empty and it doesn't process any of the files in the
folder.

David

 

[Dirk Goldgar]

In

Hi Dirk,

Thank you for your help.

I copied the folder from the C: drive to the K: drive so that the
folder on the K: drive has exactly the same files. Also, I opened
the folder in Explorer and then used copy/paste for the path name to
be sure I don't have a typo. Per your suggestion I have removed the
"objRecordset.MoveFirst" statement. With the MoveFirst statement
gone there is no error statement but the recordset is still empty and
it doesn't process any of the files in the folder.

You remembered to uncomment the lines that open the recordset, I hope?

I'm not familiar with the Windows Search provider, so I can't verify
much beyond the basic syntax to open and process an ADO recordset. If
you post your full code. maybe something will pop out at me.

 

[Guest]

Hi Dirk,

I have continued to research this problem and found an alternative in the
literature that does work. I switched to the VBA Dir function. Repeated
calls to Dir in a loop will find all the files in a folder. I am still
curious about what I did wrong with FileSystemObject but for now I have a
functional alternative.

Thanks again for all your help.

David