0

Sorry for my English, I write from Italy. I use the Access 2007 DB in a business data management procedure in VB Net. As is known, Access DBs expand during use and consequently I need to compact them when the program is closed. I perform the compaction in this way

Dim MioEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine

MioEngine.CompactDatabase(myAccesDB, newAccessDB,)
Application.DoEvents()

but often and not on all PCs on the network, the compaction does not finish and gives me the following error message: The process cannot access the 'C:\myAccesDB.accdb' file because it is being used by another process Analyzing in depth what happens, I see that when the error occurs, the .laccdb file is not closed at the time of compacting. Is there any way other than the one I used to do the compacting safely? I specify that all the PCs in our network are Windows 10 pro and all updated; when Windows 10 was in its infancy, I didn't get this error.

RickyCast
  • 11
  • You could probably try to open `.laccdb` file and catch exception. If exception gets thrown, file is in use. So, try something like [this](https://stackoverflow.com/questions/11287502/vb-net-checking-if-a-file-is-open-before-proceeding-with-a-read-write) – Nino May 14 '21 at 10:06
  • Many thanks, but could you give me an explicit example? I haven't had a chance to use exceptions yet. – RickyCast May 14 '21 at 14:30

2 Answers2

0

Since my comment was not enough, here's an answer:

The idea is to try to opetn .laccdb file before you try to compact the database. You can do something like this:

Dim file as System.IO.FileInfo = New System.IO.FileInfo("c:\myAccesDB.laccdb")
Dim stream As FileStream = Nothing
Try
    stream = file.Open(FileMode.Open, FileAccess.ReadWrite, FileShare.None)
    stream.Close()
    'if stream is successfully closed, file is not in use and database can be compacted
    MioEngine.CompactDatabase(myAccesDB, newAccessDB,)
Catch ex As Exception
    'show some message to user that file is in use so it can try again
End Try

I worked with VB and Access ages ago, so this is only a direction that you should take.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Nino
  • 6,931
  • 2
  • 27
  • 42
0

Thanks, I'll try. I also had a look to this:
https://support.microsoft.com/en-us/topic/office-error-accdb-remains-locked-after-oledb-connection-is-closed-37d42348-9edf-4493-a5f4-35c685af3160?ui=en-us&rs=en-us&ad=us

Where I find: This issue is now fixed. If you launch Access, click 'File', then 'Account', then 'Update Options', and 'Update Now', this will ensure that you have the latest version, and all versions should have the fix available.

But I'm not able to find 'File' opening Access,....

RickyCast
  • 11