INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

COMPACT AND REPAIR ACCESS DATABASE

COMPACT AND REPAIR ACCESS DATABASE

(OP)
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

RE: COMPACT AND REPAIR ACCESS DATABASE

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


 

RE: COMPACT AND REPAIR ACCESS DATABASE

(OP)
I HAVE TO DO THIS AT RUN TIME USING VB6

RE: COMPACT AND REPAIR ACCESS DATABASE

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


 

RE: COMPACT AND REPAIR ACCESS DATABASE

(OP)
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

RE: COMPACT AND REPAIR ACCESS DATABASE

'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


 

RE: COMPACT AND REPAIR ACCESS DATABASE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close