Cycle through all the records in a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I done this before but for some reason I can't seem to find my mistake tonight. I would like to go through each record in the table and perform a file copy. My code look something like:

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Backup Information")
With rst
If .RecordCount > 0 Then
..MoveFirst
Do
Dim SourceFile, SourceFileName, SourceFilePath As String
Dim DestinationFile As String, DestinationFilePath As String, DestinationFileName As String, MDestinationFile As String, MDestinationFilePath As String

'*****Routine to retrieve the name of the SourceFileFile name function
SourceFile = [File to be Backed Up] ' Define the file to be Backed up
SourceFilePath = Left(SourceFile, InStrRev(SourceFile, "\"))
SourceFileName = Mid(SourceFile, InStrRev(SourceFile, "\") + 1, InStrRev(SourceFile, ".") - InStrRev(SourceFile, "\") - 1)
SourceFileType = Mid(SourceFile, InStrRev(SourceFile, ".") + 1)

'*****Rework destination file to be based on SourceFile
DestinationFilePath = [Backup Location] ' Location where backup is to be stored
If IsNull([Backup File Name]) Then
DestinationFileName = SourceFileName & "-" & Format(Date, "dd_mmm_yyyy") ' Filename be to attributed to the backup file
Else
DestinationFileName = [Backup File Name]
End If
DestinationFile = DestinationFilePath & DestinationFileName & "." & SourceFileType ' Identifies that backup file name & location with a date code
..MoveNext
FileCopy SourceFile, DestinationFile
Loop Until .EOF
End If
..Close
End With

It seems to continually loop on the first record. Can't someone please point out my mistake!

Thank you

Daniel
 
Daniel,

Dim db As Database
Dim rst As DAO.Recordset
Dim SourceFile As String
Dim SourceFileName As String
Dim SourceFilePath As String
Dim DestinationFile As String
Dim DestinationFilePath As String
Dim DestinationFileName As String
Dim MDestinationFile As String
Dim MDestinationFilePath As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("Backup Information")
Do While Not rs.EOF
'*****Routine to retrieve the name of the SourceFileFile name
function
'
'Very little would be gained by using the With...End With
construct here, so I've removed it.
'If I am to assume that [File to be Backed Up], [Backup File
Name] and [Backup Location] are all columns in the recordset, then they must
be treated as such.
'
SourceFile = rst("File to be Backed Up") ' Define the file to be
Backed up
SourceFilePath = Left(SourceFile, InStrRev(SourceFile, "\"))
SourceFileName = Mid(SourceFile, InStrRev(SourceFile, "\") + 1,
InStrRev(SourceFile, ".") - InStrRev(SourceFile, "\") - 1)
SourceFileType = Mid(SourceFile, InStrRev(SourceFile, ".") + 1)

'*****Rework destination file to be based on SourceFile
DestinationFilePath = rst("Backup Location") ' Location where
backup is to be stored
If IsNull(rst("Backup File Name")) Then
DestinationFileName = SourceFileName & "-" & Format(Date,
"dd_mmm_yyyy") ' Filename be to attributed to the backup file
Else
DestinationFileName = rst("Backup File Name")
End If

DestinationFile = DestinationFilePath & DestinationFileName &
"." & SourceFileType ' Identifies that backup file name & location with a
date code
FileCopy SourceFile, DestinationFile

.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Daniel said:
Hello,

I done this before but for some reason I can't seem to find my mistake
Click to expand...
tonight. I would like to go through each record in the table and perform a
file copy. My code look something like:
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Backup Information")
With rst
If .RecordCount > 0 Then
.MoveFirst
Do
Dim SourceFile, SourceFileName, SourceFilePath As String
Dim DestinationFile As String, DestinationFilePath As String,
Click to expand...
DestinationFileName As String, MDestinationFile As String,
MDestinationFilePath As String
'*****Routine to retrieve the name of the SourceFileFile name function
SourceFile = [File to be Backed Up] ' Define the file to be Backed up
SourceFilePath = Left(SourceFile, InStrRev(SourceFile, "\"))
SourceFileName = Mid(SourceFile, InStrRev(SourceFile, "\") + 1,
Click to expand...
InStrRev(SourceFile, ".") - InStrRev(SourceFile, "\") - 1)
SourceFileType = Mid(SourceFile, InStrRev(SourceFile, ".") + 1)

'*****Rework destination file to be based on SourceFile
DestinationFilePath = [Backup Location] ' Location where backup is to be stored
If IsNull([Backup File Name]) Then
DestinationFileName = SourceFileName & "-" & Format(Date, "dd_mmm_yyyy")
Click to expand...
' Filename be to attributed to the backup file
Else
DestinationFileName = [Backup File Name]
End If
DestinationFile = DestinationFilePath & DestinationFileName & "." &
Click to expand...
SourceFileType ' Identifies that
backup file name & location with a date code
 
Back
Top