Working with Front and Back-Ends

[twinnyfo]

A long-understood, good practice for Database design is to “split” the database into a Front End (FE) and a Back End (BE). The FE contains all the Queries, Forms, Reports and Modules needed to make your Database work. The BE contains all the Tables that house the data for your Project. The FE is linked to the Tables in your BE, so that there appears to be a seamless integration between the two. The reasoning behind this (among other things) is that, most often, when an Access Database crashes, it is due to the Queries, Forms, Reports and Modules failing to function properly, which may cause the program to close unexpectedly or, more likely, hang endlessly and require the killing of the program through your Task Manager. If this happened with your BE, there are much higher chances that you would lose your data or even that your data tables would become corrupted, thus making your Database unusable.

NB: splitting your Database into an FE and a BE does not preclude your need for frequent backups of your data files. This is just a good practice, no matter how big or small your data files are. Let’s say you backed up your data every hour. Losing one hour’s worth of data may seem like a lot if you are making thousands of transactions. However, imagine, then, losing an entire day’s worth of data!

Please view Microsoft’s guidance on how to split a Database for details about the basics of this process.

Typically (or at least in many cases with Database architects), we will publish the final FE in a form that cannot be so easily hacked by the average user. This transforms the .accdb file into an .accde file. Yes, since MS Access is not a secure Database, any user with moderate skills can find a way around this, but they will require advanced skills to get much farther than being able to look at your tables. Since this Insight is not about security, but about FE and BE management, we will save that discussion for a different time and place.

Please view Microsoft’s guidance on how to create an .accde file.

Also, typically, in the modern work environment, Databases are not usually designed for one person to use and enter data. They are designed for a work group to access and update accordingly. This usually entails using network file structures to house your FE and BE. However, when working in a networked environment, MS Access can be rather finicky when multiple people open the same file and use it. Additionally, whenever anyone opens that particular file, it is locked, preventing the Database Administrator from updating it or replacing that file with a newer version—at least not until everyone has exited the Database.

For this reason, I like to post the .accde file to a shared network location, but copy the file to the user’s local machine and execute the file from the local computer (thanks much to zmbd for his insights in doing this). To do this, create a brief command script in Notepad, similar to this:

@ECHO OFF
IF NOT EXIST %userprofile%\Documents\DBUser\[DatabaseName].accde MKDIR %userprofile%\Documents\DBUser
COPY \\NetworkLocation\NetworkFolder\DATABASE\[DatabaseName].accde %userprofile%\Documents\DBUser\[DatabaseName].accde /Y
START /I "MSAccess.exe" %userprofile%\Documents\DBUser\[DatabaseName].accde

Then save this file as “Database.cmd” or something useful, and save that file in the same network location as your Database. Then, when you set up your users to use the Database, create a shortcut to the Command Script, not the Database. This will copy the Database to the local folder and run it from there.

Another thing I like to do is disable as much of the built-in MS Access user interface that I can. I make all my forms modal and pop-up and hide the Database window, I disable the menus and prevent shortcut menus. The user is highly restricted in what they can and cannot do in my Database, and this is by design. I figure that the less someone is able to explore on their own, the less likely they are to mess things up. I also have a designated form open upon Database execution, which sets the user permissions and directs the user to the appropriate parts of the Database. Go here to find my article on How To Create User Permissions and Customized Menus in MS Access.

Now, let’s talk about Database updates and changes.

Let’s say that your Database is running fine, but one of your users identifies a new capability they need added to the Database. You should still have a local copy of your .accdb file (or at least you better still have it!). However, your working copy of all the Queries, Forms, Reports and Modules may still be linked to your network Database. So, you go to the Linked Table Manager, and have the Database now point to your locally saved version of the tables (you should also have these readily available). Then, when you publish your changes, you go back to the Linked Table Manager and do it all over again.

But, what if you publish your Database and disable the menus—but forgot to link to the network tables? I guess you have to start all over again. This can become very tedious, and if you happen to have multiple BEs, this can take a lot of time. I don’t like to waste time, so I have come up with a solution that has served me very well for several years.

I have built into my Database an Administrator Form, which, among other things, sets up the Database for being published on the network. This is all done after the Database has been saved as an .accde file. The process is really as simple as this:

1. Create the .accde file
2. Open the Administrator Form and run the Database set-up code
3. Close the Database and copy it to the network location

Here are the basics of what I do:

In a stand-alone module, I have established certain Global Constants that indicate the locations of certain files and folders. Because we use our Database to reference other files and folders on our network, I have a separate constant for the Network server and for the Database path. I also have separate folders for my backups and my data:

 Option Compare Database
Option Explicit
 
'******************************
'* ESTABLISH PUBLIC CONSTANTS *
'* AND GLOBAL VARIABLES       *
'******************************
 
'Database Path constants and variables
Public Const gstrSERVER As String = _
    "\\NetworkServer\NetworkFolder\"
Public Const gstrDBASEPATH As String = _
    gstrSERVER & "DATABASE\"
Public Const gstrBACKUPPATH As String = _
    gstrDBASEPATH & "BACKUP\"
Public Const gstrDBASEDATA As String = _
    gstrDBASEPATH & "DATA\"

After the .accde file has been created, I open that file and then open the Administrator form that has my code.

The first thing I do is establish some Form specific variables that will help me run my code more quickly and easily.

Option Explicit
Option Compare Database
 
'Set some Constants and Public Variables
Public dbACC As Database
Public tdfLink As TableDef

I have a Command Button that calls the RelinkTables procedure in the OnClick Event of that command button.

Private Sub RelinkTables()
On Error GoTo EH
    Dim tdf As TableDef
    Dim strDBName As String
    Dim strLink As String
 
    ChangeProperty "StartUpShowDBWindow", dbBoolean, False
    ChangeProperty "AllowBuiltInToolbars", dbBoolean, False
    ChangeProperty "StartupForm", dbText, "frmSplash"
    ChangeProperty "StartUpShowStatusBar", dbBoolean, False
    ChangeProperty "AllowSpecialKeys", dbBoolean, False
    ChangeProperty "AllowDatasheetSchema", dbBoolean, False
    ChangeProperty "AllowFullMenus", dbBoolean, False
    ChangeProperty "AllowShortcutMenus", dbBoolean, False
    LinkTableStatus "Startup Defaults Set"
 
    strLink = ";DATABASE=" & gstrDBASEDATA
    For Each tdf In dbACC.TableDefs
        'Filter out system "~",
        'non-Access (Excel Spreadsheets="$")
        'and non-linked tables ("")
        If Not ((Left(tdf.Name, 1) = "~") Or _
            (Right(tdf.SourceTableName, 1) = "$") Or _
            tdf.Connect = "") Then
            strDBName = GetDBName(tdf.Connect)
            RelinkTable dbACC, tdf.Name, strLink & strDBName
        End If
    Next tdf
    LinkTableStatus ""
    MsgBox "All tables were successfully linked!  You are now free to " & _
        "move about the Database!", vbOKOnly, "Action Complete"
    Exit Sub
EH:
    MsgBox "There was an error Relinking the Tables!  " & _
        "Please contact your Database Administrator.", vbCritical, "Error!"
    Resume Next
End Sub

The first thing I do is establish some Database defaults (which would make it really difficult to work with your Database if they were currently in effect). Then it loops through all of the Tables (which are linked), and determines what the source file for those tables ought to be. Then, it relinks those tables to the Network version of those tables.

In order for this to work, I need to create several small functions that will help me do this. The first is a function to actually change the properties:

Private Function ChangeProperty(strPropertyName As String, _
    varPropertyType As Variant, varPropertyValue As Variant) As Integer
On Error GoTo Err_ChangeProperty
    Dim prpProperty As Property
 
    'Property exists, so set its Value
    dbACC.Properties(strPropertyName) = varPropertyValue
    ChangeProperty = True
 
Exit_ChangeProperty:
    Exit Function
 
Err_ChangeProperty:
    If Err.Number = 3270 Then   'Property not found
        'Since the Property isn't found, create it!
        Set prpProperty = _
            dbACC.CreateProperty(strPropertyName, _
                varPropertyType, varPropertyValue)
        dbACC.Properties.Append prpProperty
        Resume Next
    Else
        'Unknown Error
        ChangeProperty = False
        Resume Exit_ChangeProperty
    End If
End Function 

Next, I created a function (GetDBName) to find out the name of the Database to which the table is linked:

Private Function GetDBName(FullPathName As String) As String
On Error GoTo EH
    Dim intCharCount As Integer
    For intCharCount = Len(FullPathName) To 1 Step -1
        If InStr(intCharCount, FullPathName, "\") <> 0 Then
            GetDBName = Right(FullPathName, Len(FullPathName) - intCharCount)
            intCharCount = -1
        End If
    Next intCharCount
    Exit Function
EH:
    MsgBox "There was an error getting the DB Name!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Function
End Function

Finally, I need a function that will relink the tables, based on the values I have determined from the current table identified.

Private Function RelinkTable(dbLink As Database, _
    strTable As String, strConnect As String)
On Error GoTo EH
    LinkTableStatus "Relinking " & strTable
    Set tdfLink = dbLink.TableDefs(strTable)
    tdfLink.Connect = strConnect
    tdfLink.RefreshLink
    Exit Function
EH:
    MsgBox "There was an error Relinking the Table!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Function
End Function

I have yet to figure out how to relink a spreadsheet, but I am sure there is probably a way that I am unaware of.

That’s it! Then I close my Administrator Form, close the Database and copy it to the network location. Everything is all neat and tidy and I won’t forget anything.

Also, since I am not ever touching my original .accdb file, I can still open it and test it (using the local data resources) without fear of messing up anything on the network.

I am sure there are also some better ways to do all this, but for those of you who find FE and BE management quite tedious, this is a real time-saver. This code can run slow when others are connected to the Database, though, as it must re-establish those links (I’ve heard there are ways around that), but my Database has well over 100 tables, and when no one is connected, this whole procedure runs in much less than a minute!

Hope this will be able to help some of you out!

[twinnyfo]

I wanted to add an update. After some discussion with some other folks on this forum on another thread, some of the info here can be updated to include a better way of managing the transfer of fresh FEs.

Please refer particularly to Posts #5 and #8 from this thread: Feeding updates from a newer database version (1.1) to an older version (1.0)