By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,730 Members | 1,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,730 IT Pros & Developers. It's quick & easy.

Access 2007 saves a new file upon exiting...

P: 5
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!
Jul 6 '07 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jul 7 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jul 7 '07 #3

P: 5
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!
Jul 8 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jul 8 '07 #5

P: 2
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.
Jul 11 '07 #6

P: 2
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.
Jul 11 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jul 11 '07 #8

puppydogbuddy
Expert 100+
P: 1,923
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
Expand|Select|Wrap|Line Numbers
  1. Function CompactDb(strSourceDB As String, strDestDB As String)
  2.  
  3. Dim jetEngine As JRO.JetEngine
  4. Dim strSourceConnect As String
  5. Dim strDestConnect As String
  6.  
  7.  
  8. ' Build connection strings for SourceConnection
  9. ' and DestConnection arguments
  10. strSourceConnect = "Data Source=" & strSourceDB
  11. strDestConnect = "Data Source=" & strDestDB
  12.  
  13. Set jetEngine = New JRO.JetEngine
  14.  
  15. jetEngine.CompactDatabase strSourceConnect, strDestConnect
  16.  
  17. Set jetEngine = Nothing
  18.  
  19. End Function
  20.  
Jul 11 '07 #9

P: 3
Sp1 fixes this

Lou
Mar 12 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.