Schedule the time to compact the secured database file

accessman2

Registered User.
Local time
Today, 07:30
Joined
Sep 15, 2005
Messages
335
Hi,

I have a db1.mdb with user-level security. The Secured.mdw is security file. Everytime we need to refer Secured.mdw to open the db1.mdb.

I created another compact.mdb database for compact and repair the db1.mdb at night time.

In the compact.mdb:
I create the form and setup the timer to compact the db1.mdb at the night time.

I used

Public Function CompactAndRepairDB(sSource As String, _
sDestination As String, _
Optional sSecurity As String, _
Optional sUser As String = "Admins", _
Optional sPassword As String, _
Optional lDestinationVersion As Long) As Boolean

Dim sCompactPart1 As String
Dim sCompactPart2 As String
Dim oJet As JRO.JetEngine

On Error GoTo Err_compact

' Put together the provider string for the source database
sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & sSource & _
";User Id=" & sUser & _
";Password=" & sPassword


' If the database has a user-level security file, add the
' details
If sSecurity <> "" Then
sCompactPart1 = sCompactPart1 & _
";Jet OLEDBystem database=" & sSecurity & ";"
End If

' Put together the provider string for the destination
' database
sCompactPart2 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & sDestination

' The destination database will end up in the latest version
' of jet, unless a specific version has been requested;
' 1 = Jet 1.0, 2 = Jet 1.1, 3 = Jet 2.x, 4 = Jet 3.x,
' 5 = Jet 4.x etc
If lDestinationVersion <> 0 Then
sCompactPart2 = sCompactPart2 & _
";Jet OLEDB:Engine Type=" & lDestinationVersion
End If

' Compact and repair the database
Set oJet = New JRO.JetEngine
oJet.CompactDatabase sCompactPart1, sCompactPart2
Set oJet = Nothing

CompactAndRepairDB = True

Exit_compact:
Exit Function
Err_compact:
MsgBox Err.Description
Resume Exit_compact
End Function



Function CompactWithSecurity()
Call CompactAndRepairDB("c:\db1.mdb", "c:\tmp.mdb", "c:\Secured.mdw", "jim")
End Function

It works, because jim is Adminstrator.

But, if Jim is not Adminstrator, then it doesn't work.
if jim is assigned "Backup Operators" and "Read-Only Users" permission by Adminstrator. When Jim logged in the Account, Jim can compact the database from clicking option menu (Database Ulities->Compact and Repair Database...).

If he used compact.mdb database to compact the db1.mdb
with this function
Function CompactWithSecurity()
Call CompactAndRepairDB("c:\db1.mdb", "c:\tmp.mdb", "c:\Secured.mdw", "jim")
End Function

Then it doesn't work.

How can I fix the function?
I want jim to compact the db1.mdb using compact.mdb. But, he is NOT adminstrator. I want to assign limit permission to him to compact the database using compact.mdb file to compact the db1.mdb at the night time.

Thanks.
 
Searching the forum is a great way to discover and learn the answers to your Access programming questions.

Oh I forgot... AccessBoy refuses to use the forum's Search function. :eek:

The user must have the correct user permissions to compact a database.

HAL - Handy Access Launcher

Password / Username in shortcut to mdb file

Compacting A Database

Set command-line options for starting Access

This is how you call another secured database and compact it using the Shell() function...
Code:
Dim sAccess
Dim sWorkgroup
Dim sDatabase
Dim sSwitches

sAccess = (SysCmd(acSysCmdAccessDir)) & "MSACCESS.EXE"
sWorkgroup = "/wrkgrp" & Chr(32) & Chr(34) & "\\Server\Partition\Directory\WorkgroupFile.mdw"
sDatabase = "\\Server\Partition\Directory\YourDatabase.mdb"
sSwitches = "/user UserName /pwd YourPassword /excl /compact"

MsgBox Chr(34) & sAccess & Chr(34) & Chr(32) & sWorkgroup & Chr(34) & Chr(32) & Chr(34) & sDatabase & Chr(34) & Chr(32) & sSwitches
Call Shell(Chr(34) & sAccess & Chr(34) & Chr(32) & sWorkgroup & Chr(34) & Chr(32) & Chr(34) & sDatabase & Chr(34) & Chr(32) & sSwitches)
 

Users who are viewing this thread

Back
Top Bottom