Searching for file properties/metadata from within access 1

[Tarnish]

Hi all,

I need to be able to do a full text search of a folder of files (with subfolders) including, if possible, searching things like file properties/metadata.

I'm primarily concerned with pdf files and word documents.

Is this possible in MS Access 2003?

thanks,
T

 

[Remou]

Is an additional utility an option?

Microsoft offers a scriptable search utility, it can be downloaded here:
Windows Desktop Search 3.01 (WDS 3.01)

http://www.microsoft.com/windows/products/winfamily/desktopsearch/default.mspx

 

[Tarnish]

Thanks for the reply, Remou. (long-time no see).

I actually have downloaded that windows desktop search, but I don't know a great deal about it. I'll have to see if I can figure out the scripting for it. If there

Thanks for the reply.
T

 

[Remou]

Here are the properties:

http://www.microsoft.com/technet/scriptcenter/topics/desktop/wdsprops.mspx

Here is an example adapted from Microsoft:

Private Sub cmdSearch_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim fn, dn, md, slist

'The ADO connection and recordset
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

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

'What to find
strtext = Chr(34) & Me.txtSearchFor & Chr(34)

'An SQL string with a selection of fields, there are more
strSQL = "SELECT System.ItemFolderPathDisplay, " _
& "System.ItemPathDisplay,System.DateModified " _
& "FROM SYSTEMINDEX WHERE (Contains('" & strtext & "') " _
& "OR System.ItemPathDisplay LIKE '%" _
& Replace(Me.txtSearchFor, "'", "''") & "%') " _
& "ORDER BY System.FileName"

rs.Open strSQL, cn

rs.MoveFirst

'Loop through the recordset
Do While Not rs.EOF
    fn = rs.Fields.Item("System.ItemPathDisplay")
    md = rs.Fields.Item("System.DateModified")
    
    slist = slist & md & "  " & fn & vbCrLf
    rs.MoveNext
Loop

'Display the list
MsgBox slist

End Sub

Have fun.

 

[Tarnish]

Hey, thanks Remou!

I'll have a go at it...

T

 

[Tarnish]

Hey Remou,

That code fails on the rs.moveFirst.

I don't think it knows what the recordset is, for whatever reason....

T

 

[Remou]

rs.MoveFirst will fail if there are no records returned. The code is just a sample from a form, it needs tinkering to make it suit your application.

 

[Tarnish]

Hi Remou,

Yeah, I tried adding a form, and adding the code to a command button on the form. I also added a text box to type in the search phrase. I named the text box/command button the same as you used in the code and I added a variable declaration that was missing.

I'm assuming the recordset is a list of windows files in the desktop search's index file. If so, it should work because I do have desktop search and it has had the indexing run.

I also tried some code from the ms site, but I think that's meant for .vbs files and not access, and it fails on a line that begins: Wscript.echo (despite the fact that I added a reference to the windows script hosting object library).

I played around and get it to not fail by replacing that with the variable set to be a recordset, and I can get some stuff to debug.print to the imediate window, but I don't get the records to display anywhere for the user, yet, and many of the lines returned look like hidden system files (though, that's to be expected because the basic code I started with from MS site is meant to just list ALL the files in index).

I'll get it figured out eventually.

Again, thanks for the help,
T

 

[Remou]

Wscript.echo is just a line print. You can use MsgBox or Debug.Print anywhere you see it. If you wish, you can post back the modified code, or you may prefer to have fun fixing it

 

[Tarnish]

Remou,

I have two forms I'm working on at this point. The first one I described above with a command button and a text box. It is based on the code you posted slightly modified (if there are 'extra' Dim statements, ignore them):

Option Compare Database
Option Explicit

Private Sub cmdSearchFiles_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim fn, dn, md, slist
    Dim strtext As String

'The ADO connection and recordset
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

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

'What to find
strtext = Chr(34) & Me.txtSearchFor & Chr(34)

'An SQL string with a selection of fields, there are more
strSQL = "SELECT System.ItemFolderPathDisplay, " _
& "System.ItemPathDisplay,System.DateModified " _
& "FROM SYSTEMINDEX WHERE (Contains('" & strtext & "') " _
& "OR System.ItemPathDisplay LIKE '%" _
& Replace(Me.txtSearchFor, "'", "''") & "%') " _
& "ORDER BY System.FileName"

rs.Open strSQL, cn

rs.MoveFirst

'Loop through the recordset
Do While Not rs.EOF
    fn = rs.Fields.Item("System.ItemPathDisplay")
    md = rs.Fields.Item("System.DateModified")
    
    slist = slist & md & "  " & fn & vbCrLf
    rs.MoveNext
Loop

'Display the list
MsgBox slist

End Sub

The second form is based on this webpage at ms:

http://www.microsoft.com/technet/scriptcenter/topics/desktop/wdsearch.mspx

There's code at the bottom that I tried to modify. ATM, I'm just working with the very first block of code on that website. My code looks like:

Option Compare Database
Option Explicit

Private Sub cmdSearchFiles_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim fn, dn, md, slist
    Dim strtext As String
    Dim objConnection As ADODB.Connection
    Dim objRecordset As ADODB.Recordset
    
On Error Resume Next

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

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

objRecordset.Open "SELECT System.FileName FROM SYSTEMINDEX", _
    objConnection

objRecordset.MoveFirst

Do Until objRecordset.EOF
    fn.Echo objRecordset.Fields.Item("System.FileName")
    Debug.Print objRecordset.Fields.Item("System.FileName")
    objRecordset.MoveNext
Loop

End Sub

I think if I can hammer out any problems with the second bit of code (the code from MS), I could use the rest of that page as a framework for getting to something that's actually useful.

You modification would be an equally attractive alternative that I could manipulate if I could just get it to work once.

Right now, with yours, I get this error:

"Runtime Error 3021: Either BOF or EOF is true, or the current record has been deleted. Requested operation requires current record."

Bear in mind, both of these code blocks were just put into new blank, unbound forms, with the ms code form having just a command button and your code form having a command button and a text box.

Any direction would be GREATLY appreciated. I'm sure I'm doing something really dumb...

Tahnks,
T

 

[Remou]

The second bit of code works for me, but it returns a huge number of files. What would you like it to do?

As for the sample I posted, debug.print strSQL - it should help us find where the problem is.

 

[Tarnish]

Hi Remou,

Kind of crazy day around here.

The second code works for me, except it doesn't present the data in any useful manner for users. I think maybe I need to create some kind of form or table or use some kind of msgbox or something to get the results to display for the user. The only thing I get now is a 'printout' in the immediate window because of that debug.print line I added near the bottom.

I do appreciate the help.
T

 

[Remou]

The way it stands, you will get far too many files returned, so it would be best to write them to a table.

Private Sub cmdSearchFiles_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strSQL As String
    Dim fn, dn, md, slist
    Dim strtext As String
    Dim objConnection As ADODB.Connection
    Dim strS As String
    Dim objRecordset As ADODB.Recordset
    
On Error Resume Next

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

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

Set cn = CurrentProject.Connection

'This was far too many records so Where
objRecordset.Open "SELECT System.FileName FROM SYSTEMINDEX Where System.Filename Like 'D%'", _
    objConnection

objRecordset.MoveFirst

Do Until objRecordset.EOF
'****Don't forget to create a table tblTable with a field 
'FileName****
    strS = "INSERT INTO tblTable (FileName) Values ('" _
    & objRecordset.Fields.Item("System.FileName") & "')"
    cn.Execute strS
    objRecordset.MoveNext
Loop

End Sub

 

[Tarnish]

Thanks Remou!

I'll give that a whirl. Of course, when it's all done I hope I'll have options to narrow the number of records returned and maybe even a way to use the path to open the files directly, but all in due time...

Thanks again,
T

 

[Remou]

The table, of course, can be used in a subform of the search form.