Compacting Dbases 2

[chell]

Hello Everyone;

I'm looking for input on how to compact databases using VBA. What approaches are there? What are the pitfalls of each? Third party solutions?

Please give me your input!

Chell

 

[Durkin]

Here's a routine I use for compacting a database. It uses the CompactDatabase method of the DBEngine object. It compacts the database into a temporary database. Then it changes the extension of the original one to .bak. Then it changes the compacted one to the original name. Keep in mind that you cannot compact a database when there is anyone in it - that includes yourself. So, this routine must be run from a separate DB.

Public Sub CompactDB()
Dim FilePath As String

    FilePath = InputBox("Enter Database Directory Path")
    If FilePath = "" Then Exit Sub
    
    On Error GoTo CompactDB_Err
    ' Compact the database to a temp file.
    DBEngine.CompactDatabase FilePath & "\YourDB.mdb", FilePath & "\YourDBTemp.mdb"

    ' Rename the current database as backup and rename the temp file to
    ' the original file name.
    Name FilePath & "\YourDB.mdb" As FilePath & "\YourDB.bak"
    Name FilePath & "\YourDBTemp.mdb" As FilePath & "\YourDB.mdb"
    MsgBox "Compacting is complete", vbInformation

Exit_CompactDB:
    Exit Sub

CompactDB_Err:
    MsgBox Err.Description, vbCritical
    Resume Exit_CompactDB
End Sub

Durkin
alandurkin@bigpond.com

 

[chell]

Hello Durkin;

Thankyou for the response but I still have a few questions. Suppose a user has a database open using run-time access, and suppose he then presses the "Compact Dbase" button. How can my code check to see if other users currently have the dbase open and how can my code transfer control to the other dbase for compacting?

Can I use workspaces to accomplish the transfer or must I use some variation of the shell command?

Thanks once again;
Chell

 

[DougP]

To see if a database is open or not would be to look in its folder for a corresponding .LDB file
In other words every time you open and Access database it creates and .LDB file. Then when the database is closed it deletes the file.

you can use the "Dir" command for this
Here is a simple DEMO to check
-----------------------------------

Dim MyFile, Status1, MyName
MyName = &quot;p:\db9.ldb&quot; ' < Note put your Drive, full path and file name here
MyFile = Dir(MyName)
If MyFile = &quot;&quot; Then
Status1 = &quot;File is closed&quot;
Else
Status1 = &quot;File is open&quot;
End If

MsgBox Status1, vbInformation, &quot;Status of Database&quot;



DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[Durkin]

I must apologise. There is an error in what I told you before. When I said you no one can be in the DB not even yourself, that was of course incorrect. You can be in there but no one else.
So, to make up for it, here is a routine that will tell you if persons other than yourself are in the Database. ;-)

Public Function DBOpened(strDbPath As String) As Boolean
Dim cnn    As ADODB.Connection
Dim rst    As ADODB.Recordset
Dim strConnect As String
Dim MyPC As String
   
   ' Format connection string to open database.
   strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath
   
   Set cnn = New ADODB.Connection
   cnn.Open strConnect
   
   ' Open user information schema query.
   Set rst = cnn.OpenSchema(Schema:=adSchemaProviderSpecific, SchemaID:="{947bb102-5d43-11d1-bdbf-00c04fb92675}")
   
   DBOpened = True
   
   'This gets the name of the local machine
   MyPC = Environ("COMPUTERNAME")
   
   With rst
      Do Until .EOF
          If Trim(!COMPUTER_NAME) <> MyPC Then
              'If you want the user to know which machine the other user is on then use this err.raise statement
              'Err.Raise 5001, &quot;YourDB&quot;, &quot;Database already opened on machine &quot; & !COMPUTER_NAME
              DBOpened = False
              Exit Function
          End If
          .MoveNext
      Loop
   End With

End Function

Durkin
alandurkin@bigpond.com

 

[chell]

Hello Durkin;

I've copied your code into access97 so as try it out but Access reports that ADODB is an unknown data type and there is not any info under this word in the help system. Can you show me what is wrong?

Sincerely;
Michelle

 

[Durkin]

Apologies again Michelle. ADO is the new replacement for DAO (and RDO and various other data access models). It doesn't ship with Access '97 so I don't think it will work in your case. Anyway, the original routine I posted for compacting the database should do almost as well. It will return an error message like

'You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'NT_ALAND'.  Try again when the database is available.'

if there is someone else in the database. There is one problem with this however. Sometimes it tells you who the other person is and sometimes it tells you that you are locking the database, even when it really is someone else. I think what happens is that when it discovers that there is more than one person in there it just uses the first name it comes across to make the error message. Anyway, I hope this will be enough. Sorry again.:~/ Durkin
alandurkin@bigpond.com