Ben has completed the first phase of a two phase project using Access 2010, SQL Server in the cloud and FileStream; all coming together in one nice solution we would like to share with you.

Using FileStream with Access to deliver docs via The Cloud

Creating proposals using Access and Word
We were hired by an insurance company to create a solution that would use Access in creating sales proposals in Word. They were creating a large amount of complex word proposals using templates by hand and it would take the entire organization weeks to go through them all.

The Solution – multiple Word sections coming together in one Doc

Their proposal is a lengthy document with a wide range of different tables and verbage based on the type of proposal being created. Rather than use a handful of templates we decided to instead create sections that would come together in one final doc. We had one section for RX plans, another for dental, another for references, etc. Each section has it’s own set of tokens we would fill in from Access, as well as programming tokens to add tables, rows and other data needed for the proposal.

In Access we followed a similar methodology, with users putting together sections into a consolidated proposal using forms. We did it using a vertical column of values that would read from predefined field aggregates, therefore eliminating the need to add new columns as new tokens are added to the system.

For example, if a user added an Rx section they can pick from a wide range of predefined plans with values already negotiated with the supplier. Once the numbers are copied over from the standard plan users can customize the plan by changing allowable values.

SQL Server in the cloud

Our client has staff in the field that needed anywhere access and also wanted to avoid the hassles of backing up and maintaining SQL Server, so they decided to use our SQL Server in the cloud for Access solution.

SQL Server FileStream and Word Automation

Each proposal has a document center that allowed users to upload pdfs, spreadsheets and other related documentation regarding the proposal. Users can create multiple versions of the proposal and those also get stored in the document center. Since the app is used by users outside of the office via the web, we decided to take advantage of SQL Server’s great FileStream feature that allowed Microsoft Access to easily upload and download files from the cloud to the user’s PC. It was our first time using this feature with Access and we were thoroughly impressed with how easy and reliable it is.

FileStream to the rescue

Think of a FileStream column as just another varbinary(MAX) column. The difference is that FileStream documents are stored outside the database in a controlled folder on the server. Advantages of this approach:

  • Since documents are not stored in the database we avoid bloating, (great since SQL Express is limited to 11 Gigs)
  • We developed a more granularized backup strategy, backing the database separate from all the binaries that are generated & saved.
  • Because files are stored in server’s file system rather in the database, storage and retrievals can be more effective, in particular with files larged than 1 MB.

Versioning and Word Automation

Ben came up with a innovative solution to make sure Word documents changes were saved to the cloud with minimum user intervention. We wanted Access to remain in control of the Word doc at all times:

  • We have Access binding to Word’s Application and Document events so it knows when the document is closed by the user and whether the user chose to save or discard the edits.
  • We added a “version” property to each Word file created by the system, allowing us to compare the local version of the file with the version stored in the cloud.
  • When a user requests a document from the doc center, Access will check if there is already a local copy of the file,  get the version and compare it with the cloud version number. If the document on the server is newer, it gets downloaded and opened for the user, otherwise we just load the local copy.
  • When the user closes the file we check if changes have occurred and if so, update the version number and upload it to the cloud.
Users must open documents via Access and the document center, but once they open it, there is no change in how they use Word. Saving & closing the document is handled as they would with any normal Word document, behind the scenes the document is kept in synchronization with the server.

Early binding required & Macro security considerations

In order to intercept and use Word events, Ben had to use early binding, (we prefer late binding for all of our needs), there is no way to late bind the Word objects and also receive events raised from those objects. That can be a problem when users may not have the same version of Word. For our case, it was acceptable as the client agreed to have everyone using the same version of Office. To avoid macro security alerts from having VBA in Word objects, all code for handling Word objects’ events were stored inside the Access application. In order for all of this to work Access has to be running at all times when interacting with documents. We added a safeguard where when Access is closed, users are prompted to close all managed Word documents (or cancel & review the edits).

SQL Server, Access, FileStream and Word – Better then a browser solution

We developed a cloud solution that you would be hard pressed to deliver via a browser, since I’m not aware of any way you can incorporate Word automation with a SQL Server database without using Access or .Net. I’m continually amazed how we continue to push the envelope with our favorite tool.

Interested in using this technology at your company? Call us for a great quote today!