MS Access Designing and Deploying for Efficiency

This week a coworker and I were discussing Access design practices that can be used to improve performance and security.  I got to thinking about the things he suggested and that I've picked up over time (from Microsoft and other developers) and realized that I could benefit from a review and, in some cases an update of those practices. So for anyone that might be helped by it, here's what I sifted out:

 

Split your application into front-end/back-end architecture:

A split architecture has long been suggested and I have used it and found it improves performance, minimizes collisions and corruption, and makes support and program enhancements so much easier.
Because the front-end, with forms, reports, queries and VBA, is on the users local desktop network traffic and load time is reduced.  Support is easier and more efficient because the tables are stored in a separate database.  When you make any form, report, query and/or vba modifications you simply replace the users front-end file without affecting their data.  Of course in the, hopefully, rare event that you find it necessary to adjust a table struture, you can do it with users offline as before.

How to split an application:

There are a couple of ways to accomplish a split application but the easiest is to use the wizard Microsoft has included.  From the "Database Tools" menu option, select "Access Database" in the "Move Data" section:

Your tables will be exported to the location you select (using UNC rather than mapped paths is recommended) with links included from the front end.  If you  move your back-end database to a different location, you will need to use the "Linked Table Manager" (under the "External Data" menu option) to re-link your tables.  Now if you give users on the network a copy of your front-end and they have access to the back end location, they will be set to go.

Establish a Persistent Connection:

In addition to the above, by keeping your back-end linked tables open with a persistent connection you improve speed. This avoids creating a new lock file (ldb) on the database each time a tables is used.  

Here's a quote from Microsoft with simple instructions:

"You can greatly enhance performance, when opening the main database and opening tables and forms, by forcing the linked database to remain open. To do this, create an empty table in the linked database, and link the table in the main database. Then, use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and from creating and deleting the associated .ldb file."

Save Database as an accde:

An Access 2010 database saved as an ACCDE makes it an executable file, gives a smaller file size and locks your database (first disable shift key startup and make sure you have a back up). To save the Access 2010 database in ACCDE format, first save a back up for development, open the database and on "File" menu, click "Save & Publish," select ACCDE.

Password Protect VBA Code:

To keep zealous, curious users from accessing (and possibly changing) your VBA code, password protect it.  Here's how to do it:  While in the VBA editor, click on "Tools" on the Menu Bar,  select your database name properties, select the "Protection" tab, select "Lock project for viewing," fill in the "Password" and "Confirm Password" fields.  Please BE SURE YOU HAVE SAVED YOUR PASSWORD SOMEWHERE!!

MS Access 2010 Runtime:

Microsoft has kindly provided us with a free runtime (no it does not expire) that we can distribute to users that do not have Access 2010 installed on their desktops. Imagine, they don't have to buy Office! The application should be tested in runtime mode before deploying to verify that all needed features are working since the runtime disables some features. To test in runtime mode, add the /runtime switch to the run line of your shortcut, or rename the file extension from accdb to accdr (thank you Antoine). If you find any lack you can program in the needed functionality before deploying.

To choose the correct runtime, right click COMPUTER to see if your operating system is 32 or 64 bit. System requirements for MS Access 2010 Runtime are:
Windows 7, Windows Server 2003 R2 (32-Bit x86), Windows Server 2003 R2 x64 editions, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 1, Windows XP Service Pack 3

Only the 32-bit Access 2010 Runtime is supported on Windows XP Service Pack 3 (ask me how I know!).

http://www.microsoft.com/en-us/download/details.aspx?id=10910

Package and Deploy Solution Wizard:

Depending on your environment and needs, there are times when the Package and Deploy Solution Wizard comes in handy.  It provides a simple way to bundle and deploy Microsoft Access database applications. The wizard guides you through the steps necessary to bundle stand-alone Access applications into Setup packages. You can also include the Access 2010 Runtime and create a shortcut in your package if helpful.

http://office.microsoft.com/en-us/access-help/package-solution-wizard-overview-HA010294985.aspx

Don't forget to add error handling to your VBA code:

You can get quite extensive with error handling but at the least, it's good practice to let the user know something happened and move forward without offering the user the opportunity to "END" or "DEBUG."

At the least include something simple like this:

On Error GoTo Err_Traping

YOUR EVENT CODE

Err_Trapping:

MsgBox err.Description

Exit Sub '(or resume next)

Additional Resources:

http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx

http://www.opengatesw.net/ms-access-tutorials/Access-Articles/MSAccess-Deployment-Best-Practices.htm
http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/6d2da932-05bc-4dac-b3f5-7ce10fb6cabc
http://www.fmsinc.com/microsoftaccess/performance/linkeddatabase.html
http://msdn.microsoft.com/en-us/library/office/ee358847(v=office.12).aspx

Have I missed anything?  Leave a comment, let me know if you disagree with any of my conclusions or add some additional suggestions!

ActiveX Controls to Enhance MS Access Functionality

ActiveX is a Microsoft technology that offers components to insert into programs or web pages, similar to JAVA applets.  Here's Microsoft's description of ActiveX Controls (http://support.microsoft.com/kb/154544): 

ActiveX controls, formerly known as OLE controls or OCX controls, are components (or objects) that you can insert into a Web page or other program so that you can reuse packaged functionality that someone else programmed. For example, the ActiveX controls that are included with Internet Explorer enable you to enhance your Web pages with sophisticated formatting features and animation. 

ActiveX is an open integration platform that provides developers, users, and Web producers a fast and easy way to create integrated programs and content for the Internet and Intranets. Using ActiveX, you can easily insert multimedia effects, interactive objects, and sophisticated programs into a Web page, creating a user experience comparable to that of high quality multimedia CD-ROM titles.

In other words, ActiveX controls are mini-programs that can be inserted into, say an MS Access form, to add functionality.  File extensions of controls are dll (direct link library) or OCX (object linking and embedding).  There are many pre-designed ActiveX controls available for free or little cost.  Here are a few examples:

Adobe PDF Viewer Control
This control allows you to embed and control the view of PDF's directly in your form. 

Barcode Control
This type of ActiveX control can be inserted into a report or form and bound to data field(s).

File Download Controls
You can download files directly from the internet to the hard drive, with a progress notification with this type of control.

Document Management Controls
Microsoft Office Document Imaging 2003 (MODI) adds programmability features to the document scanning and viewing tools.  This is a great tool but no longer included with MS Access 2010 (http://support.microsoft.com/kb/982760).

Before using a custom control, it must be registered in your install of MS Access.  To do this, open the visual basic editor (Ctrl-G), select "Tools," "References" and "Browse" to your dll or ocx file, and select it.  If  you have registered the control correctly, in a form or report design mode, select "Design" and "ActiveX Controls" you will see and be able add the control. 

ActiveX Controls can be controlled by modifying the controls "Properties," or writing event handlers in VBA depending on the control.  

One example of how I have used a custom control is in a data entry application to view embedded PDF's.  I added hot-key enabled command buttons on my form that have onclick event procedures to zoom or page through the PDF.  I thus give the keyer the ability to quickly, (without using the mouse) control the navigation and viewability of the PDF and thus speed up data entry.  Of course we could open the PDF in a separate viewer but we would then have to use the mouse to control the zooming and paging.  When you have repetitive data entry, this little option saves time and ultimately, money. 

If you have the need to add functionality to your application, an internet search will expose many available controls.

MS Access - A Cost Effective, Powerful Business Tool

MS Access is a powerful low-cost business tool that offers excellent ROI and fills many and varied business needs. 

As the most popular database program in the world, MS Access allows the creation of very sophisticated multi-user applications with a shared back-end data source, simple desktop data manipulation, or adhoc reporting solutions from a variety of data sources.  

I have not found a data source that I could not link MS Access to including SQL Server, Oracle, MySQL, MS Excel, Word, dBase, etc.  Because of this, an Access developer has the ability to develop and deploy real-time front-end solutions that report critical business intelligence from one or more of these sources.  These solutions can either be one-offs or custom menu-driven applications that offer the selection of multiple criteria, grouping, sorting and visual representation. 

With the Access Query Designer, complex multi-table queries can be created. Queries can also use VBA or user defined functions directly in queries for sophisticated analysis and updates.

The reporting ability in MS Access ranges from simple data querying to complex grouped, sorted and summarized reports with charts, graphs and custom VBA functions.  Include sub-reports to further show multi-table relationships if needed.  Here's a few examples:

MS Access 2010 also includes the ability to design and deploy database applications to web hosted Sharepoint sites.  These can be totally browser based or desktop front-ends linked to Sharepoint lists.  Though there are size limitations, this allows the flexibility to have remote users share, add or update all or some business data.  Note the example of Sharepoint hosted reporting design:

Bottom Line Cost Advantage

MS Access offers a low-cost solution for handling data and automating business processes.  Microsoft even makes available a free runtime that alleviates the cost of buying Access for every end user. 
For many business needs, MS Access is a cost-effective and powerful tool!

MS Access Key From Image

I wanted to share an additional option to my earlier blog regarding Sharepoint Attachments.  This design shows the flexibility that can be built into an MS Access Web Database.

Here it is:

In my web database (as described in my blog "MS Access SharePoint Attachment Field):

Add CLIENT forms that have an unbound Image Control, place TIF images in a consistent folder on the users computer (I would automate the daily image distribution).

Run my custom PopulateImages() function that adds the image names to a field in the table.

In the OnCurrent event of the form, set the unbound image control's "Picture" to the image.

As my tables are still Sharepoint lists, I still have the administrative advantages of a web database. This is more reasonable for higher daily volumes (remember it took and hour to add 150 images to the attachment field). It also gives the advantage of distributing any updates to users automatically whenever they login. Users can use the free MS Access 2010 runtime. Either browser based or client based can be available from the same application.

Private Sub Form_Current()
Dim MImage
MImage = "C:\folder\NewImages\" & Me.ImageName
Me.ImageNew.Picture = MImage
End Sub

What do you think?

MS Access to Oracle ODBC Connection

I recently had an adhoc reporting project using MS Access querying Oracle tables via ODBC. Thought I would share the process for anyone that might need it.

Though I could make the connection read/write, for safety and because I didn't need to make any changes to the Oracle data, a read-only connection was made. Here's the basic overview:

Oracle provides a free local client (Instant Client) and ODBC driver that is used for the connection. It can be downloaded from:

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

It's important to select the correct driver - either 32 or 64 bit. The Oracle documentation doesn't clearly explain but keep in mind that you want the driver version to match your Oracle server version. I used the 32-bit version. After downloading and installing the Oracle "Instant Client" (make sure to download the separate ODBC driver that is part of the Instant Client), you must setup your system PATH and TNS_ADMIN variable as follows:

• In Windows explorer LEFT pane, right click on “My Computer” icon and click “Properties”

• Click “Advanced system settings” tab

• Click Environment variables button:

• In the “System Variables” panel select “Path” variable and click “Edit” button:

• Now find a variable called TNS_ADMIN — it could be in either System or User panel (I chose System so that all users on this PC would have access), if you have no such variable, create it in the User panel by clicking “New”:

• Variable Name: TNS_ADMIN

• Variable Value: C:\InstantClient (or whatever folder you designated in the install above)

• Click Ok

• Now click “Ok” on the Environment Variables screen and click “Ok” on the System Properties window to close it

• Run C:\InstantClient\odbc_install to install the Oracle ODBC driver.

• Run the ODBC Data Source Administrator to configure your connection. Note: The default ODBC
Data Source Administrator is 64-bit so if you are running the 32-bit ODBC driver, you will have to specifically run the above file (on my machine it was found at (C:\Windows\SysWOW64\odbcad32.exe).

• Select “System DSN” and configure as shown:

• Click “Test Connection” to confirm set-up. You will be asked for a TNS Service Name, User ID and password which your Oracle administrator can give you. If all is correct you should see a successful connection message.

Once connected, I was able to design and run all my reports without issue.

MS Access SharePoint Attachment Field

I recently tested a project that is a good candidate for MS Access on Sharepoint. It is an ongoing project keying records daily from scanned images and would require at least 3 keyers daily. The advantages are obvious: ability to work remotely, centralized administration, and flexibility to distribute or redistribute work flow.

I developed forms with needed validation rules for each unique image that we are keying from. Within the table design (and then forms) I included an attachment field. When an attachment field is added to a form, the first image will show. As I will only add one image per record, this works perfectly for our purpose.

For management tools, I designed a separate, client application that links to the Sharepoint lists (tables). I run daily processes and updates from this "management" application. I needed a way to add all new images in a batch process daily. My images arrive as tifs since this is the default and best format on scanning images. However, this format is not "web enabled" and needed to be converted to jpg's to render in the browser. After batch converting the tif's to jpg's, I batch add them to the table with the following two-step vba functions: First, I populate a field in the table (list) with the name of the image (having all new images in a folder):

Public Function PopulateImages()
Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:\Folder\NewImages\"
strFile = Dir$(strFolder & "*.jpg")

Do While Len(strFile) > 0
strSQL = "INSERT INTO TicketsTbl (ImageName) " & "VALUES(" & Chr$(34) & strFile & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir$()
Loop
End Function

Then I add the attachments:

Sub AddAttachments()
Dim mfile
Set db = CurrentDb
Dim rsTickets As DAO.Recordset

Set rsTickets = db.OpenRecordset("TicketsTbl")

If Not (rsTickets.EOF And rsTickets.BOF) Then
rsTickets.MoveFirst
Do Until rsTickets.EOF
rsTickets.Edit
Set rsPictures = rsTickets.Fields("Field1").Value
mfile = rsTickets.Fields("ImageName")
mfile = "C:\Folder\1F\" & mfile

rsPictures.AddNew
rsPictures.Fields("FileData").LoadFromFile mfile
rsPictures.Update

rsTickets.Update
rsTickets.MoveNext

Loop
End If

Set rsTickets = Nothing
Set db = Nothing

End Sub

NOTE: I set the process running and checked back later and found that adding 150 jpg attachments took one hour. Not real speedy and an obvious consideration that will make this process unreasonable for some projects.

I have the ability to run counts, do quality control and give feedback to keyers based on the login information that Sharepoint always captures. The built in fields are "SharePointCreatedDate", "Author", "SharePointModifiedDate" and "SharePointEditor." I always add similar fields to my desktop applications but these are included in any SharePoint list.

I found no fully-developed instructions to this process on the web (though I started with a basic outline of the add attachment function from Microsoft) so I'm hopeful that this information will be helpful to some of you. Would love to hear your feedback.

Sharepoint Access Services Reporting Flexibility

I have been struggling with offering my users a flexible and powerful reporting environment in their Access Services application. In a desktop MS Access database, to give users the ability to select more than one item to apply to a single filtering criteria you can either create a custom interface/fuction with VBA or use a multi-select combobox (or listbox) with VBA. Unfortunately, VBA is not available in Access Services. Microsoft has replaced VBA with expanded Macro capabilities that, while pretty powerful, is not as flexible or powerful as VBA. Nor is a multi-select combobox available in the same way in a web database. I had to think outside the box.

Here's what I came up with:

In our database we have a table with contacts and a related table with lists that the contacts belong to. I created a list lookup table with the values the users can select to filter their records with the fields: SelectList(s) (Y/N), Lists, ListID. There are 55 possible lists in the lookup table. Then I designed a report selection form, placed a subform on it with checkboxes for each list selection. After selecting the list(s) the user desires and clicking "Select Lists" a macro is run that populates TempVars with the for selected list(s) ListID number.

I designed a Query that links the Contacts table to an interim ContactLists table that then links to the SelectMultiLists table. I run a datamacro that sets the selected records to YES for the TempVars ListID. I included the SelectList field in the query and set it's criteria to "True" so that only the related records that the user has checked in the subform appear in the results.

Back on the Report Selection form, after the user clicks the yes/no option to select their criteria, they select the report (I have 4 different report formats including labels and a spreadsheet layout appropriate for export to Excel) they want to run and a macro is run with additional If/Else/Endif structure that additionally filters the report selected.

In this way I was able to offer reports on multiple lists and have included 3 additional criteria (state, district, active).

I reviewed the templates Microsoft offers for Web Databases, and the Northwinds Web template and I believe what I have designed has more flexibility/options for reporting all in Sharepoint (don't have to move to the client).

Would love to hear your feedback if you have worked with Access Services and can offer better or additional reporting suggestions.