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
- @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
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:
Here are the basics of what I do:
- Create the .accde file
- Open the Administrator Form and run the Database set-up code
- Close the Database and copy it to the network location
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
- 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\"
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
- Option Explicit
- Option Compare Database
- 'Set some Constants and Public Variables
- Public dbACC As Database
- Public tdfLink As TableDef
Expand|Select|Wrap|Line Numbers
- 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
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
- 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
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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
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!