CompactRepair on a secured Access db

  • Thread starter Thread starter Jonathan
  • Start date Start date
J

Jonathan

Hello all,
Is there a way to run the CompactRepair method on a user secured database?
This code works fine on an unsecured db but not on a secured one.

VB6.0 code...

Dim strSource As String
Dim strTarget As String
Dim blnLogfile As Boolean
Dim blnRet As Boolean
Dim app As New Access.Application

strSource = "C:\database\Secured_db.mdb"
strTarget = "C:\database\Repaired_db.mdb"
blnRet = app.CompactRepair(strSource, strTarget, blnLogfile)

Jonathan
 
Jonathan said:
Hello all,
Is there a way to run the CompactRepair method on a user secured database?
This code works fine on an unsecured db but not on a secured one.

VB6.0 code...

Dim strSource As String
Dim strTarget As String
Dim blnLogfile As Boolean
Dim blnRet As Boolean
Dim app As New Access.Application

strSource = "C:\database\Secured_db.mdb"
strTarget = "C:\database\Repaired_db.mdb"
blnRet = app.CompactRepair(strSource, strTarget, blnLogfile)

Jonathan
Click to expand...

Describe "secured". Database password, User-Level Security, on a disk in his
rucksack, ... ?

-ralph
 
User-level security... sorry for the ommision

Ralph said:
Describe "secured". Database password, User-Level Security, on a disk in his
rucksack, ... ?

-ralph
Click to expand...
 
Do you get an error message when you attempt this?

Do users have Open Exclusive permission on the Database Object?
 
Hi Joan,
I'm trying the solution referenced by Paul, above. Here is the code:
================================================
Private Sub main()
Dim jro As jro.JetEngine
Dim strSource As String
Dim strTarget As String
Dim strProvider As String
Dim strWGrp As String

strProvider = "Microsoft.Jet.OLEDB.4.0;"
strWGrp = "Jet OLEDB:System database=C:\DB\\DB.mdw;"
strSource = "Data Source=C:\DB\db1.mdb;User ID=DBAdmin"
strTarget = "Data Source=C:\DB\repaired.mdb;User ID=DBAdmin"

Set jro = New jro.JetEngine
jro.CompactDatabase strProvider & strWGrp & strSource, strProvider & strWGrp
& strTarget
End
End Sub
=====================================================
I get this error on the CompactDatabase
"Cannot start your application. The workgroup information file is missing or
opened exclusively by another user"


See anything in this?

Jonathan
 
Jonathan said:
Hi Joan,
I'm trying the solution referenced by Paul, above. Here is the code:
================================================
Private Sub main()
Dim jro As jro.JetEngine
Dim strSource As String
Dim strTarget As String
Dim strProvider As String
Dim strWGrp As String

strProvider = "Microsoft.Jet.OLEDB.4.0;"
strWGrp = "Jet OLEDB:System database=C:\DB\\DB.mdw;"
strSource = "Data Source=C:\DB\db1.mdb;User ID=DBAdmin"
strTarget = "Data Source=C:\DB\repaired.mdb;User ID=DBAdmin"

Set jro = New jro.JetEngine
jro.CompactDatabase strProvider & strWGrp & strSource, strProvider & strWGrp
& strTarget
End
End Sub
=====================================================
I get this error on the CompactDatabase
"Cannot start your application. The workgroup information file is missing or
opened exclusively by another user"
Click to expand...

If any other user or any object in the current database has a connection to the .mdb file
in question then you won't be able to get exclusive access to the .mdb file. That is a
requirement before compacting. You certainly wouldn't want to be compacting if another
user is designing a form or entering data.

Given that, you can never compact from within the currently open database file without
resorting to either the Compact On Close option or using SendKeys to select the menu
option to do this (I believe, unless perhaps you opened the .mdb file exclusively initially).
 
¤ Hi Joan,
¤ I'm trying the solution referenced by Paul, above. Here is the code:
¤ ================================================
¤ Private Sub main()
¤ Dim jro As jro.JetEngine
¤ Dim strSource As String
¤ Dim strTarget As String
¤ Dim strProvider As String
¤ Dim strWGrp As String
¤
¤ strProvider = "Microsoft.Jet.OLEDB.4.0;"
¤ strWGrp = "Jet OLEDB:System database=C:\DB\\DB.mdw;"
¤ strSource = "Data Source=C:\DB\db1.mdb;User ID=DBAdmin"
¤ strTarget = "Data Source=C:\DB\repaired.mdb;User ID=DBAdmin"
¤
¤ Set jro = New jro.JetEngine
¤ jro.CompactDatabase strProvider & strWGrp & strSource, strProvider & strWGrp
¤ & strTarget
¤ End
¤ End Sub
¤ =====================================================
¤ I get this error on the CompactDatabase
¤ "Cannot start your application. The workgroup information file is missing or
¤ opened exclusively by another user"
¤

I don't believe that the security information would be specified for the destination database. See
if the following example works for you:

http://www.freevbcode.com/ShowCode.asp?ID=2879


Paul
~~~~
Microsoft MVP (Visual Basic)
 
How to compress an open access DB

I have tried to use the code specified and I get an error that the DB is not open exclusively.

I have split my DB into a front and backend. I want to compress my backend DB using VB code as it grows from 50 MB to 1.6 GB. If I compress it manually during the process it stays near 50-100 MB.

My VB code adds and delete records in multiple tables and I suspect access is not cleaning itself up, until I do a compress.

The way my application works is:

1) user enters data
2) when ready they press a button to run some code
3) analysis is done on the data and several reports are created and tables are updated.

While the code is running the user will not be entering any data. The data is used by one user only and I no one else can be updating it. I have tried different ways of closing and opening the backend DB with no luck.
Any suggestions
 
Back
Top