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.

My First Access Services Project

Access Services is Microsoft's answer to running MS Access applications on the web, in Sharepoint. The obvious advantage is that a user with login credentials can access their data from any web browser. I have completed and published my first MS Access Services project. It has been tested on an iphone/ipad and it runs just like on a PC (not quite as much fun on an iphone because of the screen size but great to have the option in a pinch). I don't know about you but I can think of lots of business needs that this might fill. Microsoft Access is the most widely used desktop database system in the world. Sharepoint is the perfect intranet environment and adding Access Services extends that usefulness.

Challenges that had to be overcome:

1) Office 2010 was the first release of Access Services so finding a lot of good examples and forum support is a challenge. A Google search on any MS Access features or questions returns beaucoup of resources - such is not yet the case with Access Services. Microsoft recommends http://www.utteraccess.com/forum/Access-Services-Web-Data-f74.html and I have found others like http://accessexperts.net/blog/category/access-web-database, http://channel9.msdn.com and http://dmoffat.wordpress.com. Though there are many that try to offer support, most prefice their comments with "I haven't tried this yet, but ..." It's a start, we are all learning and any effort these non-paid folks make to help should definately be appreciated. I've found posts by Albert Kallal are very comprehensive and informative.

2) I heard a lot of buzz about the fact that you must either have Sharepoint hosted in house of pay for the hosting service. Some complained that it was too expensive. In my opinion, this is not an issue. Any serious business application on the web must be securely hosted somewhere and Sharepoint offers that security and flexibility at a reasonable price for businesses. For $49/month accesshosting.com offers 5 users and handles our needs. That is not concurrent users, concurrent users aren't limited. That 5 users is user logins. So I configured administrator, contributor and viewer users with 3 user logins to spare. Accesshosting includes a daily backup in the hosting price but will only restore back one day. This is not an adequate backup/restore flexibility for most businesses but you can upgrade backup/restore options or plan your own backup.

2) Optimizing the design for speed. As we are running this in a web browser with multiple and varied internet speeds, making it as efficient as possible is important to it being a useable tool. My project contained about 14,000 records in the largest table and initially was too slow to load. The solution was to open the main page with a subset of the data (A-C for example) and offer the user a box to search for the record(s) they want and then query the next small subset of data. Doing this in all forms made a huge different in speed.

3) Offering multiple report formats and filtering options. I have 10 reports including formatted labels, each report/label has 3 or more filtering options. I host with Accesshosting.com and they include SQL Reporting Services in the hosting. In this way, my users are able to generate their reports and print or export in multiple formats including PDF, Excel, CSV and more. Does the trick.

I'm excited about the results and looking for my next project!

MS Access 2010 - Sharepoint Hosting

I have taken the giant step of upgradng to Microsoft Office 2010 and I love it. I did not upgrade to 2007 because I chose to wait for the second iteration of this major upgrade. For MS Access 2010, the major improvement and, in my opinion, move in the right direction, is the new Access Services that makes it possible to develop an application totally for the web and hosted on a MS Sharepoint server. Data is moving to the web, we know it and if we are smart, we will get ready to support it.

Here's how it works: In Access the developer must select "Web Database" option when starting the project. All forms, reports, Queries and events are developed with web compatibility. New macro functionality has been added to extend the capabilities. Access 2010 allows forms and reports to be published to web sites using what is called "access web services" that runs on Sharepoint software. These web based forms and reports run in any standard browser. Before publishing your project to the Sharepoint server, it is checked for compatibility and errors reported.

Many have voiced the concern that having to run in Sharepoint creates an expense and complication that will impede this usefulness. Honestly, I had this concern at first but here's what I decided: Any URL will have to be hosted somewhere and security, user-access, multi-user conflicts in tables, etc., will need to be handled somehow. Sharepoint does it effortlessly to the developer and there are many reasonably-priced hosting services out there. You can find 30-day free trials to host and demo sites to customers if needed as well. I have used www.accesshosting.com for such a demo and been happy.

Go Where the Work Takes You

I have been awarded several MS Access database development projects of late that I would like to share:

I recently completed an application for a wonderful European style bakery in Atlanta that is growing daily. The chef needed a way to control the process of receiving and tracking daily customer's orders, generating the baker's daily production reports with breakdown of dough types, dough volume required, and invoicing of customers. I worked closely with the client and we have developed a great tool that will make the daily process more efficient, easier to manage and less time consuming. Really enjoyed this project.

I am working on modifications to an application used by a local micro brewery to manage their process. This is a challenging project as I am wading through some very different, and totally without comments, code. To further complicate matters, the original programmer and I seem to approach the process differently. An occasional challenge can be good and keep you on your toes.

The third project is for an association that promotes a food product. Though I am modifying and adding to an existing application, I and the original programmer seem to approach the process similarly. I can more quickly understand where he is going.

The moral of the story is: Go where the work takes you. Be flexible enough to see a need and accept the challenge.

We Are a Google Favorite Place!

It's been almost a year since I posted (shame on me). Still here after 15+ years. We have been staying busy with a large project collecting police accident reports and validating the VINs. Been promised more to come and glad of it.

We we're excited to receive a Google "FAVORITE PLACE" decal this week. Shows that the hard work I've been doing to promote our services on the web have had benefit. Google sent out 100,000 of the decals to those businesses that had enough clicks from their Local Business listings.

Thanks Google!

Lost a Bid

We were not selected to continue in the bid process this week. A large telecommunications company located in our area gave us the honor of being included in their RFP list. We gave it our best shot but one of the things they looked at was company growth and receivables. Unfortunately our growth is flat and our receivables have gone down. We were also in the upper-middle tier on our pricing. There were some unknown variables inherent in the project that caused us to bid a bit higher. They gave about a week in a half to prepare the bid.

It's a tough economic market and this company had previously started this project with a subsidiary company of a very large corporation. They went bust, leaving them with a failed project to start over. The moral of the story is: size may matter but it doesn't guarantee success.

We have a strong history of successfully starting, continuing and completing projects. We have never let a customer down and had hoped that this would be considered. We are also proud of the people that have worked with us over the years. Some in our office, some from their homes. Local mothers for the most part that want to help with the family budget.

C'est la vie! If at first you don't succeed, try try again.