Not looping through records

[daze]

So, my code does not loop through records - it backs up just the first file...

Here is my code
- - - - - - - - - - - - - - - - - - - -

Private Sub backup_Click()
Dim rs As Recordset
Dim strSql As String

strSql = "SELECT * FROM files;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

rs.MoveFirst
Do While Not rs.EOF
Call BackUpAndCompactFE
rs.MoveNext
Loop
MsgBox "End of backup process!"
rs.Close
Set rs = Nothing
End Sub

 

[vbaInet]

We need more info. What does your function do? Let's see the code.

 

[daze]

Teis is what it does:


Public Sub BackUpAndCompactFE()

Dim oFSO As Object
Dim strDestination As String

strDestination = Me.path2backup & Me.FileName & ".accdb" '<-You will have set to the name you want!!

'Flush the cache of the current database
DBEngine.Idle

'Create a file scripting object that will backup the db
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile Me.file4backup, strDestination
Set oFSO = Nothing

'Compact the new file, ...
Name strDestination As strDestination & ".cpk"
DBEngine.CompactDatabase strDestination & ".cpk", strDestination
Kill strDestination & ".cpk"

End Sub

 

[vbaInet]

I'm guessing file4Backup is a textbox?

 

[daze]

Yes it is.

Clearly this code is missing something...

 

[vbaInet]

You're looping through the recordset, not through the values of the textbox.

You can either:

* set the value of the textbox for each loop from the recordset just before you call the function

OR

* set a variable for each loop from the recordset and use the variable in the function

By the way, what you're doing is an overkill. Split the function into two parts. One would be to perform the back and the other to compact the db. You will then call the compact function after looping.