CompactRepair on a secured Access db

9 Aug 2006 1:19 PM

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

9 Aug 2006 3:21 PM

Paul Clement

On Wed, 9 Aug 2006 09:19:18 -0400, "Jonathan" <KingsKnight1@Nospam.Hotmail.com> wrote:

¤ Hello all,
¤ Is there a way to run the CompactRepair method on a user secured database?

How To Compact Microsoft Access Database Through ADO
http://support.microsoft.com/kb/230501/EN-US/

Paul
~~~~
Microsoft MVP (Visual Basic)

9 Aug 2006 4:24 PM

Ralph

Show quote Hide quote

"Jonathan" <KingsKnight1@Nospam.Hotmail.com> wrote in message
news:efCNsZ7uGHA.4888@TK2MSFTNGP05.phx.gbl...
> 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
>

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

-ralph

9 Aug 2006 4:32 PM

Jonathan

User-level security... sorry for the ommision

Show quoteHide quote

"Ralph" <nt_consultin***@yahoo.com> wrote in message
news:P82dnebHh7VTlkfZnZ2dnUVZ_v2dnZ2d@arkansas.net...
>
> "Jonathan" <KingsKnight1@Nospam.Hotmail.com> wrote in message
> news:efCNsZ7uGHA.4888@TK2MSFTNGP05.phx.gbl...
> > 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
> >
>
> Describe "secured". Database password, User-Level Security, on a disk in
his
> rucksack, ... ?
>
> -ralph
>
>
>

9 Aug 2006 5:48 PM

Joan Wild

Do you get an error message when you attempt this?

Do users have Open Exclusive permission on the Database Object?

--
Joan Wild
Microsoft Access MVP

Jonathan wrote:

Show quoteHide quote

> User-level security... sorry for the ommision
>
> "Ralph" <nt_consultin***@yahoo.com> wrote in message
> news:P82dnebHh7VTlkfZnZ2dnUVZ_v2dnZ2d@arkansas.net...
>>
>> "Jonathan" <KingsKnight1@Nospam.Hotmail.com> wrote in message
>> news:efCNsZ7uGHA.4888@TK2MSFTNGP05.phx.gbl...
>>> 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
>>>
>>
>> Describe "secured". Database password, User-Level Security, on a
>> disk in his rucksack, ... ?
>>
>> -ralph

9 Aug 2006 6:42 PM

Jonathan

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

Show quoteHide quote

"Joan Wild" <jwild@nospamtyenet.com> wrote in message
news:ed8cV29uGHA.324@TK2MSFTNGP06.phx.gbl...
> Do you get an error message when you attempt this?
>
> Do users have Open Exclusive permission on the Database Object?
>
>
> --
> Joan Wild
> Microsoft Access MVP
>
> Jonathan wrote:
> > User-level security... sorry for the ommision
> >
> > "Ralph" <nt_consultin***@yahoo.com> wrote in message
> > news:P82dnebHh7VTlkfZnZ2dnUVZ_v2dnZ2d@arkansas.net...
> >>
> >> "Jonathan" <KingsKnight1@Nospam.Hotmail.com> wrote in message
> >> news:efCNsZ7uGHA.4888@TK2MSFTNGP05.phx.gbl...
> >>> 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
> >>>
> >>
> >> Describe "secured". Database password, User-Level Security, on a
> >> disk in his rucksack, ... ?
> >>
> >> -ralph
>
>

10 Aug 2006 3:55 AM

John Mishefske

Jonathan wrote:
Show quoteHide quote

> 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"

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).

--
'---------------
'John Mishefske
'---------------

10 Aug 2006 1:32 PM

Paul Clement

On Wed, 9 Aug 2006 14:42:35 -0400, "Jonathan" <KingsKnight1@Nospam.Hotmail.com> wrote:

¤ 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)