Access 2007 saves a new file upon exiting...

[Adamowski]

Hello,

I have a 2007 access database named sales.accdb. If I open it, then immediately close it, my folder will have the following:

- sales.accdb
- sales1.accdb

If I open sales.accdb again, and immediately close it, I will have in my folder:

- sales.accdb
- sales1.accdb
- sales2.accdb

If I open sales1.accdb and immediately close it, I will have in my folder:

- sales.accdb
- sales1.accdb
- sales2.accdb
- sales11.accdb

Why is it doing this and how can I get it to stop? It is really annoying me. Any help would be greately appreciated!

[MMcCarthy]

Adam

I haven't used Access 2007 yet but I would guess that this is being caused by a compact and repair on closing. Check under options if the compact on close option is ticked.

Mary

[MMcCarthy]

Also the fact that a new file is being created indicates that the ldb file is not closing. Look for a file called sales.ldb when the database is closed. If it is there then delete it. It should only be there when the database is open. Hopefully microsoft haven't changed the extension for this as well.

[Adamowski]

Oddly enough, if I open the file on a different machine, I do not have this problem. I checked the options and the database is not being compacted upon close.

In addition, the temporary file you mentioned in 2007 has an extension *.laccdb.

Any other ideas? Thanks!

[MMcCarthy]

Oddly enough, if I open the file on a different machine, I do not have this problem. I checked the options and the database is not being compacted upon close.

In addition, the temporary file you mentioned in 2007 has an extension *.laccdb.

Any other ideas? Thanks!

Does sales.laccdb disappear when you close the database.

If it is only happening on the one machine I honestly don't think the problem is with the database.

[jtam]

In a test that I ran when compact on close was enabled everytime I closed the database that was on a mapped network drive it created the extra database numbered as you described. I verified I had all the correct permisions in the folder that the database resides.

I then copied the database to my local drive and the problem of creating the duplicate database whent away. I repoend the one on the network drive and again the problem occured.

Also it appeared that the new duplicate copy that it created was compacted but the original was not.

When I disabled compact on close it stopped.

[jtam]

In a test that I ran when compact on close was enabled everytime I closed the database that was on a mapped network drive it created the extra database numbered as you described. I verified I had all the correct permisions in the folder that the database resides.

I then copied the database to my local drive and the problem of creating the duplicate database whent away. I repoend the one on the network drive and again the problem occured.

Also it appeared that the new duplicate copy that it created was compacted but the original was not.

When I disabled compact on close it stopped.

I just did another test and when it is on the network drive it does not appear to delete the lock file in time and that is why it cannot overwrite the original database when the compact is done, thus creates another. When on the local drive the lock file deletes very quickly and thus it can overwright the original when the compact is complete.

Does anyone have a suggestion on how to set it where it waites a specified delay before compacting on exit, possibley giving time for it to clear the lock file.

[MMcCarthy]

I just did another test and when it is on the network drive it does not appear to delete the lock file in time and that is why it cannot overwrite the original database when the compact is done, thus creates another. When on the local drive the lock file deletes very quickly and thus it can overwright the original when the compact is complete.

Does anyone have a suggestion on how to set it where it waites a specified delay before compacting on exit, possibley giving time for it to clear the lock file.

I'm not aware of a solution to this problem. Any solution I can think of will have the same problem with the lock file not being deleted quickly enough. I'm sure there is code out there though. I'll see what the other experts think.

[puppydogbuddy]

Hi Mary,
It would be better to do a Jet Compact instead of an Access compact because a Jet Compact does not open or close any tables like an an access compact

Here is a function to do a Jet Compact from the tips page of www.aadconsulting.com

Compact a Database Using Jet

Function CompactDb(strSourceDB As String, strDestDB As String)
 
Dim jetEngine As JRO.JetEngine
Dim strSourceConnect As String
Dim strDestConnect As String
 
 
' Build connection strings for SourceConnection
' and DestConnection arguments
strSourceConnect = "Data Source=" & strSourceDB
strDestConnect = "Data Source=" & strDestDB
 
Set jetEngine = New JRO.JetEngine
 
jetEngine.CompactDatabase strSourceConnect, strDestConnect
 
Set jetEngine = Nothing
 
End Function
 

[Louverril]

Sp1 fixes this

Lou