472,961 Members | 2,056 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,961 developers and data experts.

Working with Front and Back-Ends

3,653 Expert Mod 2GB
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
  4. '******************************
  6. '* AND GLOBAL VARIABLES       *
  7. '******************************
  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
  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
  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"
  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
  6.     'Property exists, so set its Value
  7.     dbACC.Properties(strPropertyName) = varPropertyValue
  8.     ChangeProperty = True
  10. Exit_ChangeProperty:
  11.     Exit Function
  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
1 8580
3,653 Expert Mod 2GB
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

Sign in to post your reply or Sign up for a free account.

Similar topics

by: w | last post by:
Hello all!!! For a year and a half now, we have been developing different Access 2000 applications. Most of them are access frontend and access backends. Some of them have SQL server 2000 as a...
by: Jenny - | last post by:
when i start the postmaster i have multiple backends running even though i made sure to kill all the backends lingering from the previous run. is there a reason for this? while using gdb does it...
by: Therese A. Sorna | last post by:
Hello all... I am trying to develop a database that could be duplicated many times over with different data (separated by project) in each database, but the same front end information. I was...
by: Lumpierbritches | last post by:
Thank you for any and all assistance. I'm trying to build a small application. On my computer the front and backend work great, until I change the name of the original files to something else, or...
by: Lumpierbritches | last post by:
I'm developing a small program that I would like to have a front and backend, so when I change the forms, all I have to do is upload the file on the frontend and if I change the tables, I can...
by: aaj | last post by:
Hi all I have Aceess 2000 front end linking to SQLServer 2000 BE. While developing the backend I work on an offline database. When happy I update the live one. Any changes I've made to the...
by: robert d via AccessMonster.com | last post by:
My application is beginning to interest prospective clients. This may be wishful thinking, but if I have a lot of clients how will I update a production backend when I need to add tables (for...
by: rdemyan via AccessMonster.com | last post by:
I just got Access 2003 and have been playing around with it. This whole macro security thing can be a bit annoying. Initially, I was able to link to my backends located on a second computer....
by: John | last post by:
Hi I have a single front end to which I would like to select one of two backbends at start-up. How can I accomplish this via code? Also is it possible to leave the "active" backend to last used...
by: bowtie | last post by:
i want to share my database on a workgroup,and curently i have both the front end and the backend on the workgroup server.The problem is when i share the frontend to the other clients ,the front end...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.