Split Database

[goaksmith]

I just split my database and it put the tables in the back end and everything else is in the front end. But I do not want anyone but me to be able to change the design of the forms, queries, reports, etc should those be in the back end as well. I am not entirely sure how the split database works, but any suggestions would be helpful.

 

[DBL]

What you've done is right. The queries, forms, reports and modules all stay in the frontend. It depends on how far down the line you are with your database as to what to do next. If the database is finished you can make it an MDE (there's a wizard) which means you can't change the design of the forms or reports and the code's all closed off too - but you really need to be sure you database is finished before you want to think about that. Another thing you could do is, under Startup, hide the database window and uncheck the shortcut menus etc, this stops the user getting into the gubbins too. The trick to get in to a database where you've disabled the startup options is to hold down the shift key as the database is opening - if the user's know that then they can get in too of course....

 

[ElsVanMiert]

Eventually you need to activate the security system. Be sure to read the MS white paper as well as the security related articles in the

ACCESS ONLINE ENCYCLOPEDIA (www.unsoftwareag.com)

Here is what I found about the FE/BE architecture:

Article Code: G9

How do I split my application in frontend and backend?
PROBLEM


If all database objects (tables, queries, forms, reports, macros and modules) are located in one single (MDB) file then you have (at least) two problems:
1. a direct update of your non-table-objects will overwrite the table data
2. You can not use your application in a multi-user environment.


Solution

Simply follow these steps:
Copy your MDB
Open the copy
Delete all database objects EXECPT the tables
Close and compact the copy
Rename the copy "Backend.MDB" br> Open the original
Close all tables
Link all tables from the Backend.MDB into this MDB
Close and compact this file
Rename this file "frontend.mdb"

Notes

From now on you can update the non-table-data in just replacing the frontend MDB with a new version. The data of the backend will remain unchanged.
Modifications of the table structure of the backend require SQL-DLL or ADO/DAO programming.
If you move or rename the backend file then you need to relink the linked tables.
If you move the backend on a network server multiple frontend files on the clients can access the same data.

 

[Randomblink]

Front End -> Back End experience I have had...

I have split my databases. I keep a Master Template Front End on the server. I also maintain the backend on the same server, different location. I have created another Front End for the sole purpose of capturing the users NT Logon and their Name and Title.

I e-mail the NTLogonCatcher front end to a user as they have need for the database. The catcher requests from them to enter their first and last name and their title, then as they hit done it closes the database and copies the Master Template Front End to their Desktop as Project Status...

Then every time they open the front end from their desktop, it checks to see if it is the newest version based on a custom database property I call version. If I have updated the Master Template front end, it grabs a copy and dumps it on the users desktop overwriting the older version.

I am including some code I wrote that works for me. I am sure if you have experience you could beef it up, make it better...but I am still somewhat of a newbie. All I ask, if this code helps AND you change it, send me the fix...? Thanks...

************************************************
The following function checks the Users Department based on a Department table linked to Employee table design.
************************************************
Public Function GetUserDepartment() As String
On Error GoTo Err_GetUserDepartment

GetUserDepartment = DLookup("[DEPT_NAME]", "tbl_Department", "[DEPT_ID]=" & UsrDtl("DepartmentID") & "")

Exit_GetUserDepartment:
Exit Function

Err_GetUserDepartment:
MsgBox Err.Description
Resume Exit_GetUserDepartment

End Function
************************************************
The following function checks the users details based on the NT Logon and the Employee table.
************************************************
Public Function UsrDtl(optn As String) As Variant
' DLookup(expr, domain[, criteria])
Select Case optn
Case "EmployeeID"
optn = "Empl_ID"
Case "DepartmentID"
optn = "Dept_ID"
Case "FirstName"
optn = "Empl_FirstName"
Case "LastName"
optn = "Empl_LastName"
Case "Title"
optn = "Empl_Title"
Case "Phone"
optn = "Empl_Phone"
Case "Password"
optn = "Empl_Password"
Case "NTLogon"
optn = "Empl_NTLogon"
End Select
UsrDtl = DLookup(optn, "tbl_Employee", "[Empl_NTLogon]='" & GetNTUser & "'")
End Function
************************************************
The following function checks two separate tables.
The NTLogonCreation table and the Employee table.
If a user has requested a logon but has not yet been authorized, that user will get told to wait. If they have not yet requested a logon, this opens the alternate front end that captures ONLY the NTLogon and their First and Last Name and position title.
************************************************
Public Function cde_CreateLogon()
Dim TrgtLoc As String
On Error GoTo Err_cde_CreateLogon

TrgtLoc = "\\PWC\Engineering\wpdata\reports\Databases\Front_End_Databases\NTLogonCatcher.mdb"
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase TrgtLoc, False
DoCmd.Quit acQuitSaveNone

Exit_cde_CreateLogon:
Exit Function

Err_cde_CreateLogon:
MsgBox Err.Description
Resume Exit_cde_CreateLogon

End Function
************************************************
The following function checks the Server location to get a custom database property I called Version from a Master Template mdb file.
************************************************
Public Function GetMasterVersion() As String
Dim wrkJet As Workspace
Dim dbs As DAO.Database, cnt As Container
Dim doc As Document, prp As Property

' Property not found error.
Const conPropertyNotFound = 3270
On Error GoTo GetSummary_Err

Set wrkJet = CreateWorkspace("", "Admin", "")
Set dbs = wrkJet.OpenDatabase("\\PWC\Engineering\wpdata\reports\Databases\Front_End_Databases\Tmpl_ProjectStatus.mdb")
Set doc = dbs.Containers("Databases")!UserDefined
GetMasterVersion = doc.Properties("ReplicaVersion")
dbs.Close
wrkJet.Close
Set doc = Nothing
Set dbs = Nothing

GetSummary_Bye:
Exit Function

GetSummary_Err:
If Err = conPropertyNotFound Then
MsgBox "There is no Replica Version number assigned."
Resume
Else
' Unknown error.
MsgBox Err.Description
Resume GetSummary_Bye
End If
End Function
************************************************
The following function checks the users desktop for the current version of their Project Status mdb which is just the Master Template after it was copied (via code) to their Desktop.
************************************************
Public Function GetTemplateVersion() As String
Dim dbs As DAO.Database, cnt As Container, GetTemplateLocation As String
Dim doc1 As Document, doc2 As Document, prp As Property

' Property not found error.
Const conPropertyNotFound = 3270
On Error GoTo GetSummary_Err

Set dbs = CurrentDb
Set cnt = dbs.Containers!Databases
Set doc1 = cnt.Documents!UserDefined
' Set doc2 = cnt.Documents!GeneralInfo
doc1.Properties.Refresh
' doc2.Properties.Refresh
GetTemplateVersion = doc1.Properties("ReplicaVersion")
' GetTemplateLocation = doc2.Properties("Location")
' MsgBox GetTemplateLocation

GetSummary_Bye:
Exit Function

GetSummary_Err:
If Err = conPropertyNotFound Then
MsgBox "There is no Replica Version number assigned."
Resume
Else
' Unknown error.
Resume GetSummary_Bye
End If
End Function
************************************************
The following code, compares the version of the Server located Master Template to the Desktop copy that the user currently has.
************************************************
Public Function CheckMasterToTemplate()

Select Case GetMasterVersion
Case GetTemplateVersion
Exit Function
Case Else
CopySwap
End Select

End Function
************************************************
The following function opens a form for the user based on their title as listed in the Employee table. Some titles have no forms yet, so they have no where to go...I plan on adding them as needed.
************************************************
Public Function ApprFrm()
On Error GoTo Err_ApprFrm
' The following Cases are for what title the database user holds.
' Create a form or entry level for each Title.
Select Case UsrDtl("Title")
Case "Office Assistant"
DoCmd.OpenForm "frm_OA_FORM"
Case "Engineer", "Senior Engineer", "Architect"
DoCmd.OpenForm "frm_Engineer_FORM"
Case "Lead Engineer"
DoCmd.OpenForm "frm_LeadEngineer_FORM"
Case "Engineering Manager"
Case "Deputy Director"
End Select

Exit_ApprFrm:
Exit Function

Err_ApprFrm:
MsgBox Err.Description
Resume Exit_ApprFrm

End Function
************************************************
This takes the Master version (if the versions don't match) and it puts a copy of it on their desktop and re-names that copy to Project Status...
************************************************
Public Function CopySwap()
Dim fs
On Error GoTo Err_CopySwap

' Create a target that points to where the new version should go.
' TrgtLoc = "C:\WINNT\Profiles\" & GetNTUser & "\Desktop\ProjectStatus.mdb"
' Start up the COPY FUNCTION.
Set fs = CreateObject("Scripting.FileSystemObject")
' Get the original Template and copy it to where new versions go. (True means to overwrite if a copy exists.)
fs.CopyFile "\\PWC\Engineering\wpdata\reports\Databases\Front_End_Databases\Tmpl_ProjectStatus.mdb", TNmLoc, True
' Start up the OPEN DATABASE FUNCTION.
Set appAccess = CreateObject("Access.Application")
' Open the new database that was just copied from the Template.
appAccess.OpenCurrentDatabase TNmLoc, False
' Close the old version of the database.
DoCmd.Quit acQuitSaveNone

Exit_CopySwap:
Exit Function

Err_CopySwap:
MsgBox Err.Description
Resume Exit_CopySwap

End Function
************************************************
This function checks the usr and if it is me...it opens up my form, otherwise, it goes to the ApprFrm function and runs it.
************************************************
Public Function ChckUsr()
Dim NTLogonCaught, AuthEmpl, NTUsr As String
On Error GoTo Err_ChckUsr

NTUsr = GetNTUser
If NTUsr = "bokeefe" Then
DoCmd.OpenForm "frm_DatabaseAdministrator"
GoTo Exit_ChckUsr
End If

AuthEmpl = UsrDtl("EmployeeID")
NTLogonCaught = DLookup("[UserNTLogon]", "tbl_NTLogonCatcher", "[UserNTLogon]='" & NTUsr & "'")

If IsNull(AuthEmpl) Then
If NTLogonCaught <> "" Then
MsgBox "Your account has not yet been authorized. Please check with your Office Assistant."
cdeCmd reboObject(Null, , , , , , "optShutDown")
GoTo Exit_ChckUsr
End If
MsgBox "You have not yet been approved for activity on this database. Please fill out the following form to request authorization.", vbInformation, "Secure Database Application"
cde_CreateLogon
GoTo Exit_ChckUsr
End If

Call ApprFrm

Exit_ChckUsr:
Exit Function

Err_ChckUsr:
MsgBox Err.Description
Resume Exit_ChckUsr

End Function
************************************************

 

[Randomblink]

One Last Note: Function

This following function was NOT written by me, but I use it to work form the users NT Logon.
************************************************
Public Function GetNTUser() As String
On Error GoTo Err_GetNTUser
'Returns the network login name
Dim strUserName As String
'Create a buffer
strUserName = String(100, Chr$(0))
'Get user name
GetUserName strUserName, 100
'Strip the rest of the buffer
strUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)
GetNTUser = LCase(strUserName)

Exit_GetNtUser:
Exit Function

Err_GetNTUser:
MsgBox Err.Description
Resume Exit_GetNtUser

' SAMPLE USAGE:
' Me!<insert field name here> = GetNTUser

End Function
************************************************
And that is all I have...