Cycle through all records (Newbie)

[TeneQuodBene]

Hi,

I'm making a database for used cars...
Example : I've got 15 records with used car information. One of the fields contains path information to a file on my harddrive with a picture of the car (eg: record1 = c:\vwgolf.jpg; record2 = d:\cars\opel.jpg; etc..). What I want to do now is create a button that loops from start of the recordset till the last record and does a filecopy of the photopath field... to a specified directory where I bundle my webimages before publication.

I know it can be managed manually but I want to keep it as simple as possible for the user.

I'm not familiar with VBA, but I help myself with some paste and copy and some logic thinking...

This is what I want to perform (It's not VBA, but this way it's easier to understand than my description above):

Procedure start :
Record = 1
Do While Record Not EOF
Filecopy (Record.Photopaht, "d:\web\images\")
Record = Record + 1
Loop
End Procedure

 

[TeneQuodBene]

With some try and error I managed to get this code working...
Maybe some remarks.... (I'm Newbie with no knowledge of VBA)



Private Sub btn_publicatie_Click()
On Error GoTo Err_btn_publicatie_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("Select Foto, Fotopad from Wagens")

rs.MoveFirst
Do While Not rs.EOF
On Error Resume Next
FileCopy rs!FotoPad, "e:\websites\occasi~1\images\temp\" & rs!Foto
rs.MoveNext
Loop

Exit_btn_publicatie_Click:
Exit Sub

Err_btn_publicatie_Click:
MsgBox Err.Description
Resume Exit_btn_publicatie_Click

End Sub

 

[muttsylove]

Loop through SQL results and copy files

Hi! - Need to do something similiar to this post and tried a few things (esp this logic) but with no luck. Hoping anyone can help?!

I have a sql which returns the list of filenames I need archived. I want to loop through each filename and do the following

• 1)Copy/archive the file, then
• 2)Update a flag in the table to indicate the file has been archived.

The first file archives just fine. However, when it reaches archiving the second file, I get "File Not Found". I figured out from my Debug.Print this was because the first filename was still being included/attached for some reason! (see below...)

I.e. 1st File Archive (WORKS) - C:\Backup\2005-08-ABC.xls
I.e. 2nd Fille Archive (FAILS for the second) - C:\Backup\2005-08-ABC.xls2005-10-LISTS.xls

Below are two trials I did, with no luck! Anyone have a clue as to how to resolve? I've been trying to figure this out for awhile and it's driving me nuts (Plus to add..I'm new to Access and VBA..)!! Appreciate your help!!

TRIAL ONE CODE:

Dim db As DAO.Database
Dim rsLookup As DAO.Recordset
Dim strSQLFileNames As String
Dim FileName As String
Dim strSource As String
Dim strStaging As String
Dim strArchive As String
Dim fso As Scripting.FileSystemObject
Dim strSQLUpdateLog As String

strSQLFileNames = "SELECT FILE_NAME " & _
                  "  FROM FILETABLE " & _
                  " WHERE FILE_ARCHIVED IS NULL "
                  
strSource = sourcePath [color=green]'declared as constants earlier...[/color]
strStaging = stagingPath [color=green]'declared as constants earlier...[/color]
strArchive = archivePath [color=green]'declared as constants earlier...[/color]
                  
Set db = CurrentDb()
Set rsLookup = db.OpenRecordset(strSQLFileNames)

[color=green]'Set fso = New Scripting.FileSystemObject[/color]
Set fso = CreateObject("Scripting.FileSystemObject")

rsLookup.MoveFirst
Do While Not rsLookup.EOF
[color=green]             
            'thought if I did a vbNullString at the beginning, it would work. 
            'However, it didn't...!
             'FileName = vbNullString [/color]
	FileName = rsLookup![File_Name]
	strSource = "" & strSource & FileName & fextension
	strStaging = "" & strStaging & FileName & fextension
        
	fso.CopyFile strStaging, strArchive
[color=green]
'        fso.DeleteFile strSource, True
'        fso.DeleteFile strStaging, True[/color]
        
	DoCmd.SetWarnings False
        
	strSQLUpdateLog = "UPDATE FILETABLE " & _
	                  "   SET FILE_ARCHIVED = 'Y' " & _
	                  "   AND FILE_NAME = '" & FileName & "'"
                          
	'Update Log Table
	DoCmd.RunSQL strSQLUpdateLog
	        
	DoCmd.SetWarnings True
        
rsLookup.MoveNext
Loop


rsLookup.Close
Set rsLookup = Nothing
strSource = vbNullString
strStaging = vbNullString
strArchive = vbNullString
Set fso = Nothing

db.Close
Set db = Nothing

TRIAL TWO CODE (got the same filename problem/issue):

With rsLookup
    rsLookup.MoveLast
    rsCount = rsLookup.RecordCount

    rsLookup.MoveFirst
    For i = 1 To rsCount
        FileName = rsLookup![File_Name]
        strSource = "" & strSource & FileName & fextension
        strStaging = "" & strStaging & FileName & fextension
 
       fso.CopyFile strStaging, strArchive

       DoCmd.SetWarnings False

        strSQLUpdateLog = "UPDATE FILETABLE " & _
                          "   SET FILE_ARCHIVED = 'Y' " & _
                          "   AND FILE_NAME = '" & FileName & "'"

        [color=green]'Update Log Table[/color]
        DoCmd.RunSQL strSQLUpdateLog
        DoCmd.SetWarnings True

    rsLookup.MoveNext
    Next i
End With

 

[yellow]

The problem lies in these lines:

strSource = "" & strSource & FileName & fextension
strStaging = "" & strStaging & FileName & fextension

The first time it runs strSouce is equal to whatever your constant is, then you're adding your filename.

Then the second time it runs strSource is equal to "" & strSource & FileName & fextension & then it's adding "" & strSource & FileName & fextension to it again. So your variables are growing and growing.

Also I can't see where you're setting strArchive.

This would do it for you, if you declare the variables sSource and sDestination.

FileName = rsLookup![File_Name]
sSource = strSource & FileName & fextension
sDestination = strStaging & FileName & fextension
fso.CopyFile sSource, sDestination

 

[muttsylove]

Thanks!

Thanks SOOOO MUCH Yellow! That did the trick (never would've been able to see that w/o your help!) I don't know why I had those there in the first place! ha!

Thanks for your good eyes and explanation!!
I'm on my way again..! (whew!)