424,294 Members | 1,898 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Working with Front and Back-Ends

twinnyfo
Expert Mod 2.5K+
P: 2,894
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:

Expand|Select|Wrap|Line Numbers
  1. @ECHO OFF
  2. IF NOT EXIST %userprofile%\Documents\DBUser\[DatabaseName].accde MKDIR %userprofile%\Documents\DBUser
  3. COPY \\NetworkLocation\NetworkFolder\DATABASE\[DatabaseName].accde %userprofile%\Documents\DBUser\[DatabaseName].accde /Y
  4. 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:

Expand|Select|Wrap|Line Numbers
  1.  Option Compare Database
  2. Option Explicit
  3.  
  4. '******************************
  5. '* ESTABLISH PUBLIC CONSTANTS *
  6. '* AND GLOBAL VARIABLES       *
  7. '******************************
  8.  
  9. 'Database Path constants and variables
  10. Public Const gstrSERVER As String = _
  11.     "\\NetworkServer\NetworkFolder\"
  12. Public Const gstrDBASEPATH As String = _
  13.     gstrSERVER & "DATABASE\"
  14. Public Const gstrBACKUPPATH As String = _
  15.     gstrDBASEPATH & "BACKUP\"
  16. Public Const gstrDBASEDATA As String = _
  17.     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.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. 'Set some Constants and Public Variables
  5. Public dbACC As Database
  6. Public tdfLink As TableDef
I have a Command Button that calls the RelinkTables procedure in the OnClick Event of that command button.

Expand|Select|Wrap|Line Numbers
  1. Private Sub RelinkTables()
  2. On Error GoTo EH
  3.     Dim tdf As TableDef
  4.     Dim strDBName As String
  5.     Dim strLink As String
  6.  
  7.     ChangeProperty "StartUpShowDBWindow", dbBoolean, False
  8.     ChangeProperty "AllowBuiltInToolbars", dbBoolean, False
  9.     ChangeProperty "StartupForm", dbText, "frmSplash"
  10.     ChangeProperty "StartUpShowStatusBar", dbBoolean, False
  11.     ChangeProperty "AllowSpecialKeys", dbBoolean, False
  12.     ChangeProperty "AllowDatasheetSchema", dbBoolean, False
  13.     ChangeProperty "AllowFullMenus", dbBoolean, False
  14.     ChangeProperty "AllowShortcutMenus", dbBoolean, False
  15.     LinkTableStatus "Startup Defaults Set"
  16.  
  17.     strLink = ";DATABASE=" & gstrDBASEDATA
  18.     For Each tdf In dbACC.TableDefs
  19.         'Filter out system "~",
  20.         'non-Access (Excel Spreadsheets="$")
  21.         'and non-linked tables ("")
  22.         If Not ((Left(tdf.Name, 1) = "~") Or _
  23.             (Right(tdf.SourceTableName, 1) = "$") Or _
  24.             tdf.Connect = "") Then
  25.             strDBName = GetDBName(tdf.Connect)
  26.             RelinkTable dbACC, tdf.Name, strLink & strDBName
  27.         End If
  28.     Next tdf
  29.     LinkTableStatus ""
  30.     MsgBox "All tables were successfully linked!  You are now free to " & _
  31.         "move about the Database!", vbOKOnly, "Action Complete"
  32.     Exit Sub
  33. EH:
  34.     MsgBox "There was an error Relinking the Tables!  " & _
  35.         "Please contact your Database Administrator.", vbCritical, "Error!"
  36.     Resume Next
  37. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Function ChangeProperty(strPropertyName As String, _
  2.     varPropertyType As Variant, varPropertyValue As Variant) As Integer
  3. On Error GoTo Err_ChangeProperty
  4.     Dim prpProperty As Property
  5.  
  6.     'Property exists, so set its Value
  7.     dbACC.Properties(strPropertyName) = varPropertyValue
  8.     ChangeProperty = True
  9.  
  10. Exit_ChangeProperty:
  11.     Exit Function
  12.  
  13. Err_ChangeProperty:
  14.     If Err.Number = 3270 Then   'Property not found
  15.         'Since the Property isn't found, create it!
  16.         Set prpProperty = _
  17.             dbACC.CreateProperty(strPropertyName, _
  18.                 varPropertyType, varPropertyValue)
  19.         dbACC.Properties.Append prpProperty
  20.         Resume Next
  21.     Else
  22.         'Unknown Error
  23.         ChangeProperty = False
  24.         Resume Exit_ChangeProperty
  25.     End If
  26. End Function 
Next, I created a function (GetDBName) to find out the name of the Database to which the table is linked:

Expand|Select|Wrap|Line Numbers
  1. Private Function GetDBName(FullPathName As String) As String
  2. On Error GoTo EH
  3.     Dim intCharCount As Integer
  4.     For intCharCount = Len(FullPathName) To 1 Step -1
  5.         If InStr(intCharCount, FullPathName, "\") <> 0 Then
  6.             GetDBName = Right(FullPathName, Len(FullPathName) - intCharCount)
  7.             intCharCount = -1
  8.         End If
  9.     Next intCharCount
  10.     Exit Function
  11. EH:
  12.     MsgBox "There was an error getting the DB Name!  " & _
  13.         "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
  14.     Exit Function
  15. End Function
Finally, I need a function that will relink the tables, based on the values I have determined from the current table identified.

Expand|Select|Wrap|Line Numbers
  1. Private Function RelinkTable(dbLink As Database, _
  2.     strTable As String, strConnect As String)
  3. On Error GoTo EH
  4.     LinkTableStatus "Relinking " & strTable
  5.     Set tdfLink = dbLink.TableDefs(strTable)
  6.     tdfLink.Connect = strConnect
  7.     tdfLink.RefreshLink
  8.     Exit Function
  9. EH:
  10.     MsgBox "There was an error Relinking the Table!  " & _
  11.         "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
  12.     Exit Function
  13. 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!
Jul 9 '14 #1
Share this Article
Share on Google+
1 Comment


twinnyfo
Expert Mod 2.5K+
P: 2,894
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)
Oct 22 '14 #2