importing folder path and filename info into table??? (1 Viewer)

[Robert88]

Hi All,

I am hoping soemone can help me?

I have code which will allow me to access files and folders form a folder with various files and folders within it @ "C:\CD Data", eventually I will change the path to read a CD but temporarily the information is on my hard disk. The code below sends it to message boxes based on what it has found within the folder;

Private Sub [B]CmdSearch[/B]_Click()

    Set fs = Application.FileSearch
    With fs
        .LookIn = "C:\CD Data"
        .FileName = "*.*"
        .SearchSubFolders = True
        If .Execute(SortBy:=msoSortByFileName, _
                SortOrder:=msoSortOrderAscending) > 0 Then
            MsgBox "There were " & .FoundFiles.Count & _
                " file(s) found."
            For I = 1 To .FoundFiles.Count
                MsgBox [B].FoundFiles(I)[/B]
            Next I
        Else
            MsgBox "There were no files found."
        End If
    End With

End Sub

I have also found an SQL statement which allows me to add data to a table;

INSERT INTO tblFilename (fldFieldname)
Values ('[B][Variable FoundFiles(I) from above][/B]');

I am wondering if the two can be merged together in order to place the data from the files and folderpath's can be imported into the table tblFilename with one field fldFieldname? The table only has one field.

I have tried;

Private Sub [B]CmdSearch[/B]_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("C:\CD Labeller - Microsoft Access 2003\CDlabel.mdb")
    
    Set fs = Application.FileSearch
    With fs
        .LookIn = "C:\CD Data"
        .FileName = "*.*"
        .SearchSubFolders = True
        If .Execute(SortBy:=msoSortByFileName, _
                SortOrder:=msoSortOrderAscending) > 0 Then
            MsgBox "There were " & .FoundFiles.Count & _
                " file(s) found."
            For I = 1 To .FoundFiles.Count
                MsgBox .FoundFiles(I)
                [COLOR="Red"]dbs.Execute " INSERT INTO tblFilename "_
                    & "(fldfieldname) Values "_
                    & "('.FoundFile(I)');"[/COLOR]
                dbs.Close
            Next I
        Else
            MsgBox "There were no files found."
        End If
    End With

End Sub

With no success, syntax error on red above......

I got the idea from a microsoft example;

This example creates a new record in the Employees table.

Sub InsertIntoX2()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a new record in the Employees table. The 
    ' first name is Harry, the last name is Washington, 
    ' and the job title is Trainee.

    dbs.Execute " INSERT INTO Employees " _
        & "(FirstName,LastName, Title) VALUES " _
        & "('Harry', 'Washington', 'Trainee');"
    dbs.Close
End Sub

But maybe this is better in the module rather than the CmdSearch button

If anybody can help, I would appreciate it.

Robert88

 

[Bat17]

looks like your quote wrapping


dbs.Execute " INSERT INTO tblFilename "_
& "(fldfieldname) Values "_
& "('" & .FoundFile(I) & "');"

Found file is a variable so needs to be kept outside of the strings.

HTH

Peter

 

[DB7]

Yes, Bat17 seems to have hit the nail on the head.
Small point, maybe irrelavent but Robert, your variable .FoundFile(), should be plural (.FoundFiles), in the INSERT statement.

 

[Robert88]

Thanks for the suggestions.

The code is still giving syntax errors, in red below.......

Private Sub CmdSearch_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("C:\CD Labeller - Microsoft Access 2003\CDlabel.mdb")
    
    Set fs = Application.FileSearch
    With fs
        .LookIn = "C:\CD Data"
        .FileName = "*.*"
        .SearchSubFolders = True
        If .Execute(SortBy:=msoSortByFileName, _
                SortOrder:=msoSortOrderAscending) > 0 Then
            MsgBox "There were " & .FoundFiles.Count & _
                " file(s) found."
            For I = 1 To .FoundFiles.Count
                MsgBox .FoundFiles(I)
                [COLOR="Red"]dbs.Execute " INSERT INTO tblFilename "_
                    & "(fldfieldname) Values "_
                    & "('" & .FoundFiles(I) & "');"[/COLOR]
                dbs.Close
            Next I
        Else
            MsgBox "There were no files found."
        End If
    End With

End Sub

I hope anyone can help?

Robert88

 

[DB7]

Let a message box, dislpay the sql. Is a file name, fouling things up?

...
For I = 1 To .FoundFiles.Count
MsgBox .FoundFiles(I)

Sql = " INSERT INTO tblFilename "_
& "(fldfieldname) Values "_
& "('" & .FoundFiles(I) & "');"

MsgBox SQL

dbs.Execute SQL

dbs.Close
Next I
.....

 

[Bat17]

You got your line continuation thingies back to front!

             dbs.Execute " INSERT INTO tblFilename " & _
                     "(fldfieldname) Values " & _
                     "('" & .FoundFiles(I) & "');"

Peter

 

[Robert88]

Hi Gentlemen,

I tried both methods and both gave the same error.

I moved the dbs.Close outside of the FOR...NEXT loop (shown in red below) as it kept only writing one file, closing before it could complete writing the remainder files within the loop.

Now all is working, UHUH!!!!

Thanks to both of you for your help, it seems both methods skin the same cat, just get a slightly different response in the message box!!! But I suppose the message box will be removed so effectively the same response in the end.

Private Sub CmdSearch_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("C:\CD Labeller - Microsoft Access 2003\CDlabel.mdb")
    
    Set fs = Application.FileSearch
    With fs
        .LookIn = "C:\CD Data"
        .FileName = "*.*"
        .SearchSubFolders = True
        If .Execute(SortBy:=msoSortByFileName, _
                SortOrder:=msoSortOrderAscending) > 0 Then
            MsgBox "There were " & .FoundFiles.Count & _
                " file(s) found."
            For I = 1 To .FoundFiles.Count

                '[COLOR="DarkOrchid"]*** DB7 Method ***[/COLOR]
                'send data from Sql to variable Sql.
                SQL = "INSERT INTO tblFilename" & _
                "(fldFieldname) Values " & _
                "('" & .FoundFiles(I) & "');"
                'send data from sql to message box
                MsgBox SQL
                'send data form Sql to database tblFilename.
                dbs.Execute SQL
                
                '[COLOR="DeepSkyBlue"]*** Bat17 Method ***[/COLOR]
                'send data to a message box for test purpose.
                'MsgBox .FoundFiles(I)
                'dbs.Execute " INSERT INTO tblFilename " &
                '"(fldFieldname) Values " & _
                '"('" & .FoundFiles(I) & "');"

            Next I
[COLOR="red"]                'close database connection.
                dbs.Close[/COLOR]
        Else
            MsgBox "There were no files found."
        End If
    End With

End Sub

Thanks for your help along the way.

Robert88