DB Password

[TeeVee]

I want to protect my db-be with a password but Access will ask the user for this password.
I want to keep that password secret and NOT to be supplied by any user nor from a /cmd line option which can be read by anyone.
Can i somehow supply that password from my front-end application, so the password is not known by the user.?
Of course i am aware that this is not full proof security and i need to do more.
I am using Access-2000.

 

[Guus2005]

You could refresh the connected tables using the password.
Examine the following piece of code

    Dim strConnectionString as string
    strConnectionString = "MS Access;PWD=YourPassword ;Database=H:\user\db_be.mdb"
    myTable.Connect = strConnectionString
    myTable.RefreshLink

HTH

 

[midmented]

In the past I've done this using a function. Mainly because I wanted to not have any links to the data active until the user passed login requirements. I made the front end an mde so the password stayed protected in code. Under the front end Project Properties (Protection), I locked the project from viewing with a password.

It goes something like this:
Call the password open function from the event:

Call PasswordOpen("c:\SomeDirectory\Somedb.mdb", "tblSomeTable")

The Password open function:

Function PasswordOpen(strTablePathName As String, strTableName2 As String) As Integer

         Dim db2 As DAO.Database
         Dim ws As DAO.Workspace
         Set ws = DBEngine.Workspaces(0)
         Set db2 = ws.OpenDatabase _
         (strTablePathName, _
         False, False, "MS Access;PWD=yourpassword")
         Call fExistTable(strTableName2)
         Set ws = Nothing
         Set db2 = Nothing
         
End Function

As you see, within this function, a call is made to fExistTable to make the link to the backend table. You just opened the database using the password function so now you can make the link to the data.

The fExistTable function:

Function fExistTable(strTableName As String) As Integer
'Public call - Does table exist? - Do events
Dim db As Database
Dim i As Integer
    Set db = DBEngine.Workspaces(0).Databases(0)
    fExistTable = False
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.count - 1
        If strTableName = db.TableDefs(i).Name Then
            'Table Exists
            fExistTable = True
            Exit For
        End If
    Next i

'Link to table if the table is not already linked
    If fExistTable = False And strTableName = "tblMyTableName" Then
                DoCmd.TransferDatabase acLink, "Microsoft Access", "c:\SomeDirectory\SomeDb.mdb", acTable, "tblMyTableName", "tblMyTableName"
                strTableName = ""
                Set db = Nothing
                Exit Function
'If the table is already linked, do nothing
    ElseIf fExistTable = True And strTableName = "tblMyTableName" Then
                strTableName = ""
                Set db = Nothing
                Exit Function
    End If

    strTableName = ""
    Set db = Nothing
End Function

Finally, when the application (Access Front end) exits, I ran a function from a module to delete all linked tables.

Public Function Delete_All_Linked_Tables() As Boolean
'deletes any linked tables within the current database
'Accepts : Nothing
'Returns : True/False
'Originator: Rod Hathaway
'Date : 18/08/1999
'Amended 12/01/2000

On Error GoTo err_del_lnks

Dim myDb As Database
Dim tdfLinked As TableDef
Dim i As Integer
Dim rtn_val As Integer

Delete_All_Linked_Tables = False

'set object var
Set myDb = CurrentDb()

'iterate and delete
For i = myDb.TableDefs.count - 1 To 0 Step -1
    Set tdfLinked = myDb.TableDefs(i)
    If tdfLinked.Attributes And dbAttachedTable Then
        'set status bar
        rtn_val = SysCmd(acSysCmdSetStatus, "Deleting link to table " & tdfLinked.Name)
        myDb.TableDefs.Delete tdfLinked.Name
    Else
    
    End If
Next i

myDb.TableDefs.Refresh

Delete_All_Linked_Tables = True

leave_dlt:
    Set tdfLinked = Nothing
    Set myDb = Nothing
    Exit Function
    
err_del_lnks:
    err_num = Err.NUMBER
    err_descr = Err.Description
    Delete_All_Linked_Tables = False
    Resume leave_dlt

End Function

The other thing I really liked about this method was being able to make a multipurpose database front end that would only link to the tables the user needed. It increased application startup time but decreased the amount of users in some of the backend data tables.

 

[TeeVee]

Hei Guys,

Thanks a lot, appreciate it very much.
I will soon start working on it and will post my experience with these solutions.
I am surprised that on the Office forum(access) in Holland nobody know this should be possible, if you agree i will refer the links to your solutions on that forum.

I assume that you start the front-end without any linked table to the DB-be at first.
And then start linking all needed tables form the db-be one by one or if i need all the tables in the db-be all at once?

Regrads
TV

 

[midmented]

It's been a long time since I've used it but it did work well. I had an issue with someone (unauthorized to have the data) creating a new mdb and importing the data from the linked tables into the new database. My solution was to password protect the backend, connect to the tables after the user logged in, and make the front mde.

Yes, there are no linked tables while the db is not open. The tables are linked up one by one as the app opens.

At the time, I wrote my own users security levels code and assigned users different security levels. I used a module to capture the current user logged into the computer (Windows 2000 server) and made a users table of network usernames and assigned them security numbers. This told me who was starting the application, gave me their security level, and I used a case select to link up to the tables that the user was authorized to use.
I thought it was pretty slick because since the user already logged on to the network server, there was no need to write any logon code. If your network username wasn't in the Access users table, you weren't allowed in.
Downside: When you are writing code, queries, reports, etc. The table isn't there to work with the data. I normally made a macro to link to all the tables while developing and deleted the macro before distributing.

 

[TeeVee]

Hei Midmented,
Thanks again for your solution.
I have put it in my application and it works great.
Now i can start working on the rest of the security.
Regrads
TV