Yet another Front End Back End Issue

[dreamzdestiny]

Hi All
I've been going through lots of posts in this forums, but could not find the perfect solution for my problem. I've got a front end database with forms. And back end database with data tables. The FE database is stored in all the computers with the linked tables in BE database in shared drive. I now want to totally disable the BE database from even being opened. The Data has to be so secure that people should not be able to open it(when they find out where the files are stored in). But it should still retrieve results based on data in BE database. I tried applying password to the BE .mdb. But it did not help running the front end. Instead came up with an error. Is there a way to restrict access to BE .mdb totally (as it is only a copy of the database I have in my drive). Any help would be greatly regarded.
Thanks

 

[allan57]

When linking tables to the front-end database and the backend database is password protected, you will be asked for the database password when linking, from that point onwards the front-end will store the database password in itself so that you won't be asked to enter it again. If you apply the password after you have linked the tables then your front-end database will no longer work properly.

I use the following code in the front-end when changing/applying a password to a backend database; basically it drops the link and then reconnects the link with the password.

Private Sub Form_Load()

On Error Resume Next

ChangeTableDetails

DoCmd.Close acForm, Me.FormName

End Sub

Public Function ChangeTableDetails()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim tdfLinked As DAO.TableDef

Dim strGetCurrentLinkedPath As String
Dim strGetCurrentLinkedTableName As String

Dim intTableCounter As Integer
Dim intNumberOfTables As Integer

Const strBackendTargetDbPassword = ";PWD=plajsgbd"

On Error GoTo ErrorDetected

Set dbs = CurrentDb

dbs.TableDefs.Refresh
intNumberOfTables = dbs.TableDefs.Count

For Each tdf In dbs.TableDefs

If Nz(tdf.Connect, "") <> "" Then

'Store current linked table details
strGetCurrentLinkedTableName = tdf.Name
strGetCurrentLinkedPath = tdf.Connect
strGetCurrentLinkedPath = FindDatabasePathFr0mLinkedString(strGetCurrentLink edPath)

'Delete current linked table
DoCmd.DeleteObject acTable, tdf.Name

'Rebuild linked table from stored data
'Ensure the password is correct
Set tdfLinked = dbs.CreateTableDef(strGetCurrentLinkedTableName)
tdfLinked.Connect = strBackendTargetDbPassword & ";DATABASE=" & strGetCurrentLinkedPath
tdfLinked.SourceTableName = strGetCurrentLinkedTableName
dbs.TableDefs.Append tdfLinked

End If

intTableCounter = intTableCounter + 1

If intNumberOfTables = intTableCounter Then

GoTo ClearDownArrays

End If

Next

Exit Function

ErrorDetected:

If Err.Number = 3031 Then

MsgBox "The password supplied with the function (ChangeTableDetails) " & vbCrLf & "is incorrect for the Backend Db." & vbCrLf & vbCrLf & _
"Please provide a valid password for Const 'strTargetDbPassword'", vbExclamation, "Invalid Password Set"

Else

MsgBox Err.Number & " - " & Err.Description, vbCritical, "Unexpected error detected, update aborted"

End If

ClearDownArrays:

Set tdf = Nothing
dbs.Close
Set dbs = Nothing

End Function

Public Function FindDatabasePathFr0mLinkedString(GetLinkedString As String) As String

Dim intStartPosition As Integer
Dim intEndPosition As Integer

On Error Resume Next

intStartPosition = InStr(1, GetLinkedString, "Database=") + 9
intEndPosition = InStr(1, GetLinkedString, ".mdb") + 4
FindDatabasePathFr0mLinkedString = MID(GetLinkedString, intStartPosition, intEndPosition)

 

[Dennisk]

if the backend is workgroup protected and there is no shortcut, then just dbl-clicking on the db will result in in an 'you do not have the necessary permissions to use this database"

 

[dreamzdestiny]

Thank you allan57.
That was of great help. I relinked the tables after applying the password. It worked. You are a champion.
And Dennisk I am sorry for not thankin you to take time in helping me out. but I was stuck i creating workgroups and setting permissions. If possible and if time permits could you please explain me how to go about that as well please?
thank you guys!!!!!!!!!

 

[Dennisk]

You need to search the site for help on setting up workgroups as it is complex and cannot be explained in a few paragraphs.
I set up 3 blank databases (97,2000 2003) with basic workgroup protection. I disabled the admin user, set my own administrator and a readonly group and a readwrite group. So I do not re-invent the wheel, and once you have set up workgroup security do the same thing. It is a pain.