Compacting Error

I am using VB6 Pro with SP4 and use Access 97 & Win ME.
When I try to compact database using following code I get,

Error 3356
You attempted to open a database that is already open exclusively by user 'admin' on computer 'spud'.
Try again when the database is available.

Code

Dim db As Database

Set db = DBEngine.Workspaces(0).OpenDatabase _("c:\shop\stock.mdb")

db.Close

DBEngine.CompactDatabase "c:\shop\stock.mdb", _
"c:\shop\new.mdb","

Name "c:\shop\stock.mdb" As "c:\shop\old.mdb"
Name "c:\shop\new.mdb" As "c:\shop\stock.mdb"

End Sub

Please tell me where I am going wrong as nothing in VB Code or Access says database is for exclusive use ( that I can find )
I have used this code before and now doesn't want to work after doing the updates to VB.

Many thanks,
Trevor

You might try deleting the statement:
Set db = DBEngine.Workspaces(0).OpenDatabase _("c:\shop\stock.mdb")

Ah yesss.......

Compressing files, renaming files, and/or deleting files all require EXCLUSIVE access to the database in question. You must open the database exclusively to perform these tasks. However, comma, the CompactDatabase method/statement cannot even be used by THE client that has the file openned exclusively.

The best concept I've found (an god almighty I hope someone has a better algorithm) is to open the file exclusively, quickly close it, and then run the CompactDatabase method, then close it again.

Also, be aware that if some chump has the file openned in Access you will get this error as well.

Later,

Roger Bogh
Yak & Quack Software Incorporated...

A copy of FMS Total Access Agent sorts all known problems with compacting and backing up Access Dbs.

Worth every penny.

Where do I get a copy of FMS Total Access Agent ?

FMS are a software company that produce "stuff" for Access and VB developers.

Some of it is fairly pricey but the quality is 100%.

Do a few Web searchs your bound to find their Site.