Deleting oldest file

[alpapak]

Problem

code:

Option Compare Database
Option Explicit
Function Backup()
Dim fso As FileSystemObject
Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackUpPath As String
Dim sBackUpFile As String
Dim strSQL As String

sSourcePath = Application.CurrentProject.Path
sSourceFile = Application.CurrentProject.Name
sBackUpPath = Application.CurrentProject.Path & "\Odontiatreio BackUp\"
sBackUpFile = sSourceFile & Format(Date, "ddmmyyyy") & " " & Format(Time, "hhnnss") & ".mdb"
'Add new file to table.
'Assumes tblFile has 2 fields:
' FileID (AutoNumber)
' FileName (text)
strSQL = "INSERT INTO BackUp(BackUpFileName) " _
& "Values('" & sBackUpFile & "')"
CurrentDb.Execute strSQL
'Delete oldest filename if there are more than 5 records.
strSQL "DELETE * FROM BackUp " _ ============>Error<=========
& "WHERE BackUpID IN(" _
& "SELECT MIN(BackUpID) FROM BackUp " _
& "HAVING COUNT(*)>5"
CurrentDb.Execute strSQL, dbFailOnError
Set fso = New FileSystemObject
fso.CopyFile sSourcePath & sSourceFile, sBackUpPath & sBackUpFile, True
Set fso = Nothing
End Function


Error:
Compile error:
Expected Sub, Function, or Property

What is wrong???
__________________
Alexandros

 

[RuralGuy]

strSQL = "DELETE * FROM BackUp " _

 

[alpapak]

thxs for the help

i test it and works fine
but it doesn't delete the file ???
Where to insert kill statement??

 

[RuralGuy]

Delete what file? Your SQL only deletes records in the BackUp table.

 

[alpapak]

sorry my mistake

the table BackUp is holding the name of a file.
the code delete the last 5 backupfile

BackUp
BackUpID
BackUpFileName

i want to get that filename as a string so i can run kill statement

strSQL = "DELETE * FROM BackUp " _
& "WHERE BackUpID IN(" _
& "SELECT MIN(BackUpID) FROM BackUp " _
& "HAVING COUNT(*)>5"

This code delete the record, i want to get the BackUpFileName so i can delete that file too.

 

[RuralGuy]

Before you delete the records use a SELECT query with the same criteria and walk the RecordSet to delete the files first. Then run your DELETE query to clear the table.