SharePoint and Access: How do they fit together?

Access and SharePoint

If you’ve been working with Access for a while, it’s hard to miss the push Microsoft has been putting behind SharePoint integration with Access. The integration started as early as Access 2003, continued through 2007 and has blossomed with 2010. Without question the number one feature in the latest release is publishing your database to the web with SharePoint. In this first post I’m going to discuss what is SharePoint and why was it used to publish Access web databases, replace the Jet Replication and other integration features.

So what is SharePoint?

If you were to go over to Microsoft SharePoint page, you may see a long list of features and success stories described in nebulous terms. Indeed, there is no one simple definition to tack SharePoint to. Instead of discussing its capabilities, I believe we’re better off discussing the intention behind its capabilities, and I’m going to use Access as an analogy.

SharePoint is to Web Apps as Access is to Visual Studio

Why do we use Access instead of Visual Studio? Isn’t Visual Studio more powerful and lets us do all those cool things in code? Or better yet, why not just hire a professional to set up everything for us? The fact is, Access is accessible (excuse the pun) to *non*-programmers; people who need to track their data as part of their job and this is the crucial difference. To use Access, you’re not required to be a professional to get started, and because you can do it yourself. You don’t have to go and talk with your IT department who may be overworked with other, more important projects. Precisely because of this ability to create something on a worker’s desktop, Access is has become the most popular database in the world. Some of you may be thinking, “but there are complex Access applications that requires specialized consultants out there!” – Well, yes, but that usually comes later in the Access databases’ life and not all Access databases out there end the same way and to me that’s the beauty of Access – it enables companies to develop a line-of-business applications at far less risk, whether monetary or time & effort than if we opted to use Visual Studio.

SharePoint solves a different set of problems exactly the same way as Access does: it enables *non*-web-designers to build web pages quickly and without any specialized knowledge because it’s merely a part of their workflow and not their livelihood. Likewise, it enables workers to manage files without having to think about the organization of the files, backing up the files and how they would find the files again. SharePoint helps the company saves money by reducing the overall IT administration in contrast to traditional file sharing/networked hard drives, management of documents and so forth. Some people has suggested that SharePoint be thought of as a platform providing easy-to-use building blocks, and suddenly the analogy from Access is much more accurate; we get to build web applications or develop a certain document management strategy at far less risk and expenses than if we built one from scratch.

In fact, when you examine the difference between Jet Replication and the Offline mode with SharePoint, it’s impressive in how simple it is. All you have to do is just use SharePoint as your data source and everything else about working offline and synchronizing is automatic. No manual configuration. No coding or additional installation. This truly demonstrate the raison d’être behind Access and SharePoint; solving IT problems for common people. And that’s just one of many integration points between Access and SharePoint.

SharePoint is not a relational database

Access and SharePoint are very similar when we look at how they can solve business problems. Given the similarity in the audience both programs addresses, it seems conceivable that they’d be peas in pod, right? Well, here’s the other thing. Many professional Access consultants are actutely aware that SharePoint is anything but a true relational database system and they’re right. Even though SharePoint is powered by SQL Server, it introduces additional abstractions in the form of “lists” which are not similar to a SQL table and has some ramifications on how we can use lists in Access. That’ll be addressed in a future post.

SharePoint 2010 + Access 2010 = Instant Web Database!

The latest version of Access will allow you to publish your work to SharePoint 2010 with just one URL and a click. Gone are the days were you needed to upload files, configure security, configure the web server and countless other details. Granted, not all of the functionality of Access made it into this version of SharePoint’s Access Services, but we expect improved functionality in the years to come.

Sounds great! So why isn’t SharePoint used in small businesses?

Price is another point where SharePoint and Access diverge, SharePoint’s cost are much higher, both in terms of licensing fees and man hours required to support and maintain it. Microsoft has made some headway in this area by offering Office365which essentially promises to brings SharePoint functionality to small businesses. Of course, there’s also AccessHosting which specializes in hosting Access web databases and we provide consultation for developmental work on web database that get hosted. This inevitable reality of moving services off the desktop and into “cloud” will also be examined in a future post.

My second post will discuss SharePoint lists in more detail.

Criteria for the current month in a query

The other day I needed to create a query where it returns all records with dates for the current month. I did not want to hard code the beginning and ending dates in the query, so I came up with the following line of code you can paste into your query:

Between CDate(Month(Date()) & “/1/” & Year(Date())) And DateAdd(“m”,1,CDate(Month(Date()) & “/1/” & Year(Date())))-1

The criteria line above uses the “Between And” operators to calculate the first of the month and the end of the month of the current date. Enjoy!

How to create Outlook message from Access

Note: If you need to send out a massive amount of emails this code is not for you, instead we recommend FMS’s Total Access Emailer.

Note 2: You must add a reference to Outlook in your Access project in order for this code to work, if you need an alternate version that does not require it then look at the next section of this post below:

Dim objOutlook As Outlook.Application

Dim objOutLookMsg As Outlook.MailItem

Dim strBody as String

strBody = “Insert you body message here.”

Set objOutlook = New Outlook.Application

Set objOutLookMsg = objOutlook.CreateItem(0)

With objOutLookMsg

.To = “w@what_Ever.com”

.CC = “any@what_ever.com”

.Subject = “Enter Your Subject Here”

.Body = strBody

.Send

End With

Code without a reference to Outlook:

Dim objOutlook As Object

Dim objOutLookMsg As Object

Dim strBody as String

strBody = “Insert you body message here.”

Set objOutlook = CreateObject(“Outlook.Application”)

Set objOutLookMsg = objOutlook.CreateItem(0)

With objOutLookMsg

.To = “w@what_Ever.com”

.CC = “any@what_ever.com”

.Subject = “Enter Your Subject Here”

.Body = strBody

.Send

End With

Power Tip: Use the Form property when referencing subforms in your code

Form variables can make your code sizzle

I love using form variables. They make my code faster and easier to read, and they also provide a great way to remote control another form without being on that form or having it be the focus. Another advantage is that reading record values off a form is far quicker than using a recordset using a table off the disk. If you know the data you’re looking for is already loaded on a form, reading it by using a form variable can speed up your application.
For example, let’s assume I’m coding in a form called frmOrders and I need to read controls on an open form called frmCustomers (you can only associate a form variable to an open form). I would use the following code to instantiate my variables:
Dim frm as Form_frmCustomers Dim lngCustomerID as Long
If CurrentProject.AllForms("frmCustomers").IsLoaded then
    Set frm = Forms!frmCustomers
    'Use the frm variable to read fields off the form
    lngCustomerID = frm.CustomerID Else
    'Use another technique to find CustomerID End  If
One of the advantages of declaring frm as Form_frmCustomers instead of as the generic Form type is that IntelliSense will pop up to help you refer to controls on the form. You can do this with any form that's already loaded; just make sure you use "Form_" followed by the form name when declaring variables of this type.

Subforms work too, but you need to use the Form Property

Subforms are considered part of the main form and lose their own identity once they are loaded. In order to reference a subform, you need to use the Form property to associate the form variable with the subform. Let's assume frmOrders is the parent form and frmOrderItems_sub is the name of the subform inserted into frmOrders. If you need to reference the subform, you could do so in the following matter:
Dim frm as Form_frmOrderItems_sub
Set frm = Forms!frmCustomers.frmOrderItems_sub.Form
Notice the reference to the .Form property at the end of  the second statement, which is needed in order to associate the subform with the variable frm.
Note 1: You can only use form variables on forms that have modules; otherwise, the compiler will error out.
Note 2: Make sure you reference the correct name of the subform on the main form or the code will not work. For example, if the subform control has a SourceObject set to a form named "frmOrderItems_sub" but has a control name of "OrderItems" on the main form, then your code should look like this:

Dim frm as Form_frmOrderItems_sub
Set frm = Forms!frmCustomers.OrderItems.Form
I encourage you to start using form variables in your code when you need to reference one form from another. It's another great tool in Access that will make sharing information easier across your application and can even speed it up.

Custom OpenForm code - Close and open your form

One of the major issues with DoCmd.OpenForm is it doesn’t close the form before opening it again. This can lead to unexpected behavior of your application, so of course I created my own open form code:

Public Function OpenMyForm(strForm As String, Optional DataMode As AcFormOpenDataMode, Optional WindowMode As AcWindowMode, Optional strWhere As String, Optional strOpenArgs As String) As Boolean
   DoCmd.Close acForm, strForm
   DoCmd.OpenForm strForm, , strWhere, strWhere, DataMode, WindowMode, strOpenArgs
End Function

You will notice there are some missing arguments from my code, I just don’t use all of the arguments available to me when opening a form, (for example: the filter argument).

When to use a form’s recordset and when to use recordsetclone

Recordsets are an essential tool of any Access programmer, there are many types and are used in a wide range of situations, today we’re going to talk about form recordsets and provide some examples of their use.

What are Form Recordsets?
If you’re form has a data source, either a table or query, it has a recordset property you can use in your code to get direct access to the data. When you open a recordset with the form it gains access to all of the data the form has. You can then use the recordset to find records, modify data or other uses. The only way to use them is through code and a DAO or ADODB recordset object.

Why would you use a Form Recordset?
When the form opens and loads the data it will make a round trip to disk, why not take advantage of that trip and read it off the form using a recordset? A regular recordset makes the round trip but a form recordset reads it off the form. Any filter or sort order applied to the form would also be applied to the recordset.

Recordset Clone Example
This kind of recordset is used when you don’t want the data on the form to change. as illustrated below:

Let’s assume you have a field called txtFindCustomer on a form called frmCustomers that allows users to type in any characters they wish to find a customer record. On the AfterUpdate event you can place the following code:

Private Sub txtFindCustomer_AfterUpdate()

1     Dim rst AS DAO.Recordset

2     Set rst = Me.RecordSetClone
3     rst.MoveFirst
4     rst.FindFirst “CustomerName Like ‘*” & txtFindCustomer & “*’”
5     If Not rst.EOF Then
6           Me.BookMark = rst.BookMark
7     Else
8           MsgBox “Could not find Customer with a name that includes ” & _                     9                    txtFindCustomer
10    End If

11    Set rst = Nothing

End Sub

Notice on line 2 I use the Me equivalent of the form, I’m a big fan of Me, it allows me to copy my code to other projects with ease. As you can see, it only takes one line to open the recordset, then I move it to the first record and start my search. I finally use the bookmark property to sync the form with my search so that the user can see the record located. You will need a reference to DAO in your project in order to use the code above.

If you used a regular form recordset then the user would have seen the first record and then any record matching the criteria, if there were no hits they would have ended up at the end of the table. Instead, the recordsetclone will only move the form’s focus if there is a match, or leave them on their existing record if there is not.

SQL Server IssueSQL Server tables can hold millions of records, so searching for a partial name as shown above using a recordset can take a long time or not work at all. When searching a large volume of records consider using DoCmd.FindRecord instead or another alternative.