COMPACT AND REPAIR ACCESS DATABASE

[BPL062]

I want to compact and repair a database after i delete all the records.
All works when i click the first time, but if i click this code a second time it gives me an error
Error message- You attempted to open a database that is already opened exclusively by user 'xxx'
So even though i close everything it still maintains a connection to the database open. I really dont know why
Any help would be appreciated

Here is the code

Public Sub Test()
Dim jro As jro.JetEngine
Set jro = New jro.JetEngine

jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\TemporarioDB.mdb;", _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\TempDB.mdb;Jet OLEDB:Engine " _
& "Type=5"
Kill App.Path & "\TemporarioDB.mdb"
Name App.Path & "\TempDB.mdb" As App.Path & "\TemporarioDB.mdb"
Set jro = Nothing
End Sub

 

[MichaelRed]

Why all of this? Just click the compact & repair (when you have exclusive access to the db).

The entire op is done and the db is then available for use.


MichaelRed

 

[BPL062]

I HAVE TO DO THIS AT RUN TIME USING VB6

 

[MichaelRed]

so, 'jro' is a Jet Replication Object?

is the db open elswhere within the code (and NOT previously closed)?

Why not use the (?simpler?) db engine?

What other issues exist in this op? e.g. is it a multiuser system?

When you say the error occurs on the second click, is this after the app has been shut down and restarted? or durinig the same session -if so how much time elapsese between the compress ops?

how much 'other' code is iin the app?



MichaelRed

 

[BPL062]

Hi Michael
Yes JET is the Jet replication object.
I closed any connection to the object after this code
Basically this is to dump data into a Temporary database so everytime i need to dump data, to analyse and after this data is not needed anymore. So what i did is, i start by deleting all the records on this table and after i compact the database so the ID starts from 0 again, or else it would get to a point where the ID number wouls be too high and generate an error.
Can you please elaborate on the
Why not use the (?simpler?) db engine
I dont know this methode.
Thank you so much for your help Michael

 

[MichaelRed]

'dbEngine' is a basic entity refering to the Jet engine. Just type 'it' (dbengine into an immediate / debug window, highlight it (or just place the cursor in it) and hit F1. Explore the subject and associated references ('aee also and other 'links'. I believe one of the Methods of the dbengine is "Compact", which is what you want?



MichaelRed

 

[BPL062]

Hi Michael
Thank you for the tip
i will investigate that option