Opening one secured db via vba in another secured db

[Tarnish]

Hi all,

I'm trying to figure out how I can open one secured db from another secured db (same .mdw file) to perform one change.

Is that possible?

Thanks,
T

 

[Remou]

How about ADO?

http://www.connectionstrings.com

 

[Remou]

Oops. You will need to use the same mdw for both databases.

 

[Tarnish]

Thanks Remou,

I'm trying to change the allowbypasskey property via the code. I'm playing around with ADO but no luck so far. I'm not very experienced with ADO or DAO, but I'm starting to feel a bit DOA so maybe I'm getting closer!

Thanks again,
T

 

[Remou]

Ok. How about:

Dim db As Database

strDB = "C:\Data\Tek-Tips.mdb"
Set db = OpenDatabase(strDB, True)

On Error Resume Next

db.Properties("AllowBypassKey") = True

If Err.Number > 0 Then
    ' Create the new property.
    Set prp = dbs.CreateProperty("AllowBypassKey", dbBoolean, True)
    dbs.Properties.Append prp
End If

 

[Tarnish]

Hi Remou,

I put the following code in the on_click event of a cmd button:

Private Sub cmdFEINI_Click()
Dim strDB As String
Dim db As Database
Dim prp As Property

strDB = "H:\myDirectory\myFolder\myDatabase.mde"
Set db = OpenDatabase(strDB, True)

On Error Resume Next

db.Properties("AllowBypassKey") = True
    
    If Err.Number > 0 Then
    ' Create the new property.
    Set prp = db.CreateProperty("AllowBypassKey", dbBoolean, True)
    db.Properties.Append prp
    End If

End Sub

Of course, that's not the actual mapped drive in question, but I had the appropriate drive path in place.

The code doesn't break, but it doesn't result in the property being changed either. If I set a breakpoint at the top of the on_click event, it just runs right through (skipping the middle of the 'if' statement code.....meaning no error). However, if I click down into the db variable in the locals window while stepping through the code, I think it's telling me that no actual connection is being made to the mde file in question.

I've mostly used breakpoints in the past so I can follow basic locals windows values and see how the program runs through the code. I'm not sure if my interpretation of the expanded values of the db object is accurate....

Anyway, I have a meeting pretty soon but I'll be back on this after lunch. I do appreciate your help.

T

 

[Remou]

That is an mde, I do not think you can make such changes to an mde. I had assumed a secured mdb.

 

[lameid]

Hmm.. something seems amiss in Remou's code. I think Opendatabase uses the default workgroup when used off the application object.

Private Sub cmdFEINI_Click()
Dim WS as Workspace
Dim strDB As String
Dim db As Database
Dim prp As Property

Set WS = Workspaces(0) ' Current workspace
strDB = "H:\myDirectory\myFolder\myDatabase.mde"
Set db = WS.OpenDatabase(strDB, True) 'Hopefully retains security

On Error Resume Next

db.Properties("AllowBypassKey") = True
    
    If Err.Number > 0 Then
    ' Create the new property.
    Set prp = db.CreateProperty("AllowBypassKey", dbBoolean, True)
    db.Properties.Append prp
    End If

End Sub

On the otherhand, Essentially the same thing I used to use the following code back with Access 97...

I would just keep a macro in the source MDB file with a runcode for NoByPass function. Then after converting to MDE, I would run the macro in the MDE while logged in as the database administrator (recquired security).

I have not done that in so long... I don't know if the error for the property not being found is still the same.

Function NoByPass()
    Dim varResult As Variant
    varResult = AllowBypassKeyFalse
    If varResult = True Then
        MsgBox "Bypass startup key will now be disregarded."
    Else
        MsgBox "Unable to disable bypass startup key."
    End If
End Function

Function AllowBypassKeyFalse() As Integer
    Dim dbs As Database, prp As Property
    Dim strPropName As String
    Const conPropNotFoundError = 3270
    
    Set dbs = CurrentDb
    strPropName = "AllowBypassKey"
    On Error GoTo AllowBypassKeyFalse_Err
    dbs.Properties(strPropName) = False
    AllowBypassKeyFalse = True

AllowBypassKeyFalse_Exit:
    Exit Function

AllowBypassKeyFalse_Err:
    If Err = conPropNotFoundError Then  ' Property not found.
        Set prp = dbs.CreateProperty(strPropName, dbBoolean, False)
        dbs.Properties.Append prp
        Resume Next
    Else
        ' Unknown error.
        AllowBypassKeyFalse = False
        Resume AllowBypassKeyFalse_Exit
    End If
End Function

I hope one of these works out for you.

 

[Remou]

lameid, I mentioned that it was necessary to use the same mdw for both databases. Will your code work with an mde, which is what Tarnish has?

 

[lameid]

Remou,

The only difference an MDE has vs an MDB is that the code is compiled so the modules can't be edited. The second method does work as you do log in with the secured workgroup via some normal means.

My point about your code was that I am pretty sure that opendatabase as you used it opens a database in the default security context which is not necessarily the workgroup currently logged in (Access started with /wrkgrp command line switch). I am hoping that using the same workspace would keep the security context but it has been so long since I looked at those object models that I really don't remember which is why I posted my tried and true method.

I suspect your code would work if Tarnish joined the workgroup in question. And if my revision doesn't work, then the same would be true for it.

 

[Tarnish]

Hi guys,

Lameid, I tried that first one, and I can't get it to work. On the second one, I don't see any reference to the second database. I've copied that code into a separate module, and I assume I'm suppose to call one of those functions in the on_click event of my button, but without any reference to the second db I know it can't work.

I also tried using a file path in place of the 'CurrentDB' in the code, but that didn't work. I may have done it wrong, though.

Sorry for being dense, but can you throw a line or two explanation in terms of where I put my database path into your code and which function i call from the click event of the button.

thanks again,
T

PS: I think I"ve tried connecting to the workgroup file and running rem's code....connecting to the workgroup file as the default.

The situation is that I want to be able to go into my development copy of the file, make an .mde copy of it and put it on the server where I've setup a frontend updater solution someone provided (granite?), but then I want to immediately lock the bypass as soon as I put the mde file there. Since my development copy will open back up after I create that mde, I'm only a couple of clicks from the button I'm trying to code with this stuff, so I should be able to complete the entire process in a matter of seconds. I could, of course, made the mde file and save it somewhere else, then lock it, then move it, but I'd prefer to skip the other steps at the risk of the unlikely possibility that someone could get the update after I drop it on the server but before I lock the bypass.

Hope that makes sense....

 

[Remou]

I tested the suggestion I made (having corrected db/dbs error) with very vague security set up, and it worked for me (Access 2000). How about commenting out the On Error Resume Next line to see what happens?

 

[Tarnish]

Remou,

I copied that code from my earlier post, which was the code you gave me (with the db/dbs correction) and using my path to the mde file.

Then I commented out the 'on error resume next' line. I tried hitting the button and opening the mde thereafter. It still isn't prevented from shift key usage. I then put a breakpoint at the top of the sub and clicked teh button. When the code highlights this line: Set db = OpenDatabase(strDB, True), the db variable in the local variables window doesn't have a value, and there's no '+' sign beside it indicating it can be expanded to reveal additional properties.

When the code highlights the next line: db.Properties("AllowBypassKey") = True

the db in the locals window gains the '+' sign beside it. When I expand the db to show the properties under it, I find that beside 'connection' it says 'operation is not supported for this type of object'.

I'm not sure what the problem is. I'm using access 2003 and my db is a 2002-3 file.

Thanks again. I may just give up on this because I can work around it....I was just hoping I wouldn't have to....

T

 

[lameid]

You essentially nailed how the second method I posted is intended to work...

I could, of course, made the mde file and save it somewhere else, then lock it, then move it, but I'd prefer to skip the other steps at the risk of the unlikely possibility that someone could get the update after I drop it on the server but before I lock the bypass.

I have a macro that uses runcode to run the NoByPass function. Then you open the database and click the macro.

 

[lameid]

I did some checking... Opendatabase uses the Default workspace by default. So scratch my first code. I thought since it did not specify an object it would use the application object. But ultimately I think I had it confused with automation code and opencurrentdatabase method.

Moving on. The code is DAO code so you will have to add a reference to the DAO object model...

Private Sub cmdFEINI_Click()

'Requires DAO library reference; 
'In module select References from tools menu 
'and check Microsoft DAO 3.6 Object Library

Dim strDB As String
Dim db As DAO.Database 'Note DAO library reference
Dim prp As Property

strDB = "H:\myDirectory\myFolder\myDatabase.mde"
Set db = OpenDatabase(strDB, True)

'On Error Resume Next

db.Properties("AllowBypassKey") = True
    
    If Err.Number > 0 Then
    ' Create the new property.
    Set prp = db.CreateProperty("AllowBypassKey", dbBoolean, True)
    db.Properties.Append prp
    End If

End Sub

 

[Tarnish]

Thanks for the reply, Lameid,

No luck I have found a solution, though. Albert D. Kallal has some code in an mdb in a 'temporary' area that I've found and been able to incorporate this morning.

It can, at least temporarily, be found here:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

I've been able to import the form and module, make some changes to accomodate the fact that I already have some of this code in my db (the windows api call...using the same function names), and get it to work.

I think think I'm going to see if I can consolidate anything from the module in this solution with the modules I already had, but as it is it seems to work.

Thanks again for all the help. I still haven't put everything together in terms of knowing how to approach issues like this, but hopefully I'll get there...

t

 

[lameid]

Hmm.... Looking through it is just a more developed method of basically the same code I had... I probably modified the same help file myself.

So it is easy to modify NoByPass to take a target database filename and path...

Function NoByPass(strDbPathFilename as String)
    Dim varResult As Variant
    varResult = AllowBypassKeyFalse (strDbPathFilename)
    If varResult = True Then
        MsgBox "Bypass startup key will now be disregarded."
    Else
        MsgBox "Unable to disable bypass startup key."
    End If
End Function

Function AllowBypassKeyFalse(strDbPathFilename as String) As Integer
    Dim dbs As Database, prp As Property
    Dim strPropName As String
    Const conPropNotFoundError = 3270
    
    Set dbs = Opendatabase(strDbPathFilename) 'Replaced Currentdb
    strPropName = "AllowBypassKey"
    On Error GoTo AllowBypassKeyFalse_Err
    dbs.Properties(strPropName) = False
    AllowBypassKeyFalse = True

AllowBypassKeyFalse_Exit:
    Exit Function

AllowBypassKeyFalse_Err:
    If Err = conPropNotFoundError Then  ' Property not found.
        Set prp = dbs.CreateProperty(strPropName, dbBoolean, False)
        dbs.Properties.Append prp
        Resume Next
    Else
        ' Unknown error.
        AllowBypassKeyFalse = False
        Resume AllowBypassKeyFalse_Exit
    End If
End Function

That said, putting it altogether is slick too.