Unsecuring a database

[GoatieEddie]

Hi all,
Last week I was playing with the security features and secured my db with the user-level security wizard. I have since been changing lots of things in it so I can't just go back to the back-up.

However, I need to save an unsecured version to a different directory in order for a complex spreadsheet to interact with it. If I move the secured version, excel claims not to find the db.

So how can I unsecure it? I am the owner and have the only permissions and now I wish I had played with a different db rather than my live one!

Any help always appreciated,

Thanks,
GE

 

[lupins46]

Your safest approach is to make sure that admin user is included in the admins group and admins group has full permissions on all objects.
Then make sure you are joined to an empty workgroup file as Admin (system.mdw unless you also used this for your secure workgroup).
You should then create a new empty databaae and import everything from the current database.

 

[GoatieEddie]

Ok. How do you make sure you are joined to an empty workgroup file?

 

[lupins46]

Create a new one using the Workgroup Administrator.

 

[easyit]

You could make excel interact with the secured DB using ADO. This is an example:

Const db_name = "C:\MyDatabase.mdb"

Sub ADOImportFromAccessTable(ByVal DBFullName As String, TableName As String, TargetRange As Range)
Dim intColIndex  As Integer
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection

cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Properties("Data Source") = DBFullName
cnn.Properties("Jet OLEDB:System database") = mdwlocation
cnn.Open UserId:="excel_readonly", Password:=""

Set TargetRange = TargetRange.Cells(1, 1)

Set rst = New ADODB.Recordset
With rst

    .Open TableName, cnn, adOpenDynamic, adLockOptimistic
    If Not .EOF Then
        .MoveFirst

        For intColIndex = 0 To rst.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rst.Fields(intColIndex).Name
        Next
        TargetRange.Offset(1, 0).CopyFromRecordset rst ' the recordset data
   End If
   .Close
End With
Set rst = Nothing
cnn.Close
Set cnn = Nothing

End Sub

You could call this sub as follows:

strSQL = "SELECT * From MyTable " & _
"HAVING (((Format([Date],'ww',2,3))='" & Cells.Range("a2") & "'));"
Debug.Print strSQL

Call ADOImportFromAccessTable(db_name, strSQL, Worksheets("MyFirstWorksheetName").Range("MyDefinedName1"))
Call ADOImportFromAccessTable(db_name, "MyQueryNameAsItIsInMyDatabase", Worksheets("MySecondWorksheetName").Range("MyDefinedName12"))

I have named ranges defined to make sure the data goes into the right cell(s).

However, if you need a unsec. copy, do the following:

open your sec.DB as admin. Now press ctrl+n to create another (new) db. In this DB import all objects from the secured DB. This will work now because the new DB is created with the MDW file from the sec. DB. now close access and you have your unsecured copy.

Easyit