Using GetFiles Sample Database to Update table with existing data (1 Viewer)

[Faction21]

Hello all,

I have been playing with the GetFiles.mdb, featured in the Sample Databases section, and have come accross two issues I cant seem to adress alone.

First off, I would like to add the file path to an existing table called 'Indexing', to a certain field called 'Filepath'. There is another field called 'Filename' that must line up with the field 'Filepath'.
Example Table:
|[Index0]|[Index1]|[Filename]|[Filepath]__________
| abcd | 1234 | image | //Server/batch/image.tif |

I Modified the code to work with this table and field, but... For some reason, When I change ".AddNew" to ".Edit" it will not update all the records, only the first record updates with the last file name.

How can I accomplish updating the field without adding the file path in a new record?
--------------------Code from GetFiles------------------------
Private Function GetFile()
'Please note this is done with the Microsoft DAO 3.6 Object Library
'Your system may not have this version! A References error may occure
'if your version is different or if you have not put a Reference to a DAO
'Object library of any kind in the database.

Dim vFile As String
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strFile

strFile = Dir(Me.txtPath)
vFile = txtPath & strFile

'This seemed to be needed to add the first record from the path - without it
'it did not bring the first file in but missed it every time.

'If you wish to bypass the combo box option you can use the following
'line of code. Note this line will allow for two file types. Remove
'Or Right(strFile, 3) = "wma" if you wish to use only one file type.
'If Right(vFile, 3) = "mp3" Or Right(vFile, 3) = "wma" Then
If Right(vFile, 3) = Me.cboFileType Then

'Open Recordset.
Set dbs = DBEngine.Workspaces(0).Databases(0)
Set rst = dbs.OpenRecordset("Indexing", dbOpenTable)
'add the Path (file location)
With rst
.Edit
!Filepath = vFile
.Update
End With

'Close the recordset
rst.Close
Set rst = Nothing

'Increment the progress bar
Me.pbrProgress.Visible = True
Me.pbrProgress.Value = 1
End If

'Brings in the remaining files.
Do Until strFile = ""
strFile = Dir
vFile = txtPath & strFile
'If you wish to bypass the combo box option you can use the following
'line of code. Note this line will allow for two file types. Remove
'Or Right(strFile, 3) = "wma" if you wish to use only one file type.
'If Right(vFile, 3) = "mp3" Or Right(vFile, 3) = "wma" Then
If Right(vFile, 3) = Me.cboFileType Then

'Open Recordset.
Set dbs = DBEngine.Workspaces(0).Databases(0)
Set rst = dbs.OpenRecordset("Indexing", dbOpenTable)

'add the Path (file location)
With rst
.Edit
!Filepath = vFile
.Update
End With
'Close the recordset
rst.Close
Set rst = Nothing

'Incrementing the progress bar until completed
If Me.pbrProgress = Me.pbrProgress.Max Then

'If completed then pop up the completion message
MsgBox "Import Complete " & Me.pbrProgress.Max + 1 & " Files Found."

'Hide the progress bar
Me.pbrProgress.Visible = False
Exit Function
End If

'Increment the progress bar in not completed
Me.pbrProgress.Value = Me.pbrProgress.Value + 1
End If
Loop
End Function
-----------------------------------------------------------------
If I dont make any sense, ask and I will clarify.
-Thanks for any input.

 

[Tim L]

I'm not totally familar with the code used but will try;

I think that .AddNew automatically creates a new record at the end of the recordset, whilst .Edit just enables editing of the currently selected record. Therefore I think that if you intend to work through the whole recordset that you will need to move through the recordsets. I'm not sure how WITH works in loops but you will possibly need a .MoveNext in order to go from the default first record to the next record. This would, I think, need to be placed as the last line before the End With (or before Loop in the second iteration).

Talking about that, it looks like there is some repeated code, was that a copy/past typo? They way that I see the code working presently is that it iterates twice but doesn't actually work through the records in either iteration. Also, in the second loop the recordset is opened then closed, if the loop worked correctly this would mean that you repeatedly open and close, thus repeatedly going to the first record; perhaps the code is taking a value from the end of the first iternation (the With loop) and storing it in a record during the second iteration (the Do loop)?

Should the iteration not work something like this:

Open RecordSet
Start Loop (which goes to the first record)

Check criteria (If ...)
Carry out editing/updating as required
Update progress indicator
check for last record, if so exit, otherwise
move to next record
go to start of loop​

On Exit

Set Nulls and Nothings (close RecordSet)
update progress indicator, provide end message
exit function​

?

I don't think that you really need to open the recordset in two separate loops, it should be possible for you to do all the updates in one loop.

Tim