I have a problem with my back end data file staying open, LACCDB file stays open. I have narrowed it down to a few lines of code that is causing the problem. I have tried a number of solutions but nothing works for me. The only option is to close the front end but that's not practical.

I want to copy this BE file but cant if the LACCDB file is open. No other users. I have tried deleting both the Query and the table(linked) but that still does not resolve the issue. Running MS Access 2007 and tried on various PC's. I have also tried moving the ACCDB file from Mapped drive to local drive.

The code:

Function TestMyLinks()
MyQuery="Test-Filter"
MyTable="Test"
MyCriteria = "[TestId]> 1"
MyCriteria = "SELECT [" & MyTable & "].*" & " FROM [" & MyTable & "] Where " & MyCriteria

DoCmd.DeleteObject acQuery, MyQuery    'Delete new query.
Set qdf = DataDb.CreateQueryDef(MyQuery, MyCriteria) ' Create new query
DoCmd.Close acQuery, MyQuery, acSaveYesDoCmd.Close acTable, "Test", acSaveYes

' This following code does not make any difference
'DoCmd.DeleteObject acQuery, "Test-Filter"
'DoCmd.DeleteObject acTable, "Test"
'DoCmd.TransferDatabase TransferType:=acLink, _
'        DatabaseType:="Microsoft Access", _
'        DatabaseName:="C:\MyClock\Test.accdb", _
'        ObjectType:=acTable, _
'        Source:="Attendance", _
'        Destination:="Attendance"
'Application.SetOption "Auto compact", True
'DoCmd.RunCommand acCmdCompactDatabase
'DBEngine.CompactDatabase "C:\MyClock\Test.accdb", "C:\MyClock\TestA.accdb" ' This fails1 as file locked
End Function

I have tried closing the tables using code:

DoCmd.Close acQuery, "Test-Filter", acSaveNo
DoCmd.Close acTable, "Test", acSaveNo

I have also tried as per another post to release memory:

'release memory
qdf.Close 'i changed qdef to qdf here and below
Set qdf = Nothing