A many-to-many relationship is one of Access’ more complicated concepts. An example of such a relationship is where you have a group of people who may attend a group of meetings. Thus Paul might attend meetings on 4th March, 11th March and 18th March. Fred might attend meetings only on 4th March and 18th March. Thus any one person might attend multiple meetings and any one meeting may have multiple attendees.
In order to handle this correctly in a relational database system like Access (or SQL Server or Oracle or….), you would create a Persons table (holding details of the people who might attend meetings, e.g. their surname, first name and contact details), and a Meeting table (holding details of the meeting date and meeting location). How you create the crossover table depends on some more details in the Persons and Meetings table. Over the years I have come to use a “non-speaking” identifier in my tables, e.g. an Autonumber. I’ll post later about why this, but for the purposes of this example, let’s assume that the Persons table has a PersonID field (an Autonumber field) and the Meetings table has a MeetingID field (also an Autonumber field). The crossover table will then contain the following fields:
PersonMeetingID (an Autonumber field)PersonIDMeetingID
Thus the design of our new database, with some other fields also added in, will look like this:
For our 3 meetings, let’s say that the meeting on 4th March has a MeetingID value of 6, that of 11th March a MeetingID value of 7 and that of 18th March a MeetingID value of 8. For Persons table, let’s say that the PersonID for Paul is 10 and that for Fred is 11. Thus our crossover table would have the following records:
All the above is bog-standard table design for Access developers and there is nothing too exciting about it. Records in our crossover table can be added using a standard Append query, so why am I bothering to post anything on this subject?
The reason is that Access 2010 introduces a new type of database – the Web database. The idea of this type of database is to allow only that functionality which can be published to Sharepoint 2010 with the aim of making Access 2010 into a tool for creating databases usable over the internet. This is exciting new functionality for Access 2010 and the designers at Microsoft have done a great job. It is going to mean that the ease of design of Access databases (at least compared to other database design systems) can now be ported to databases which can be used with only a web browser.
However, there are some limitations. One which is a bit scarey is that you cannot create Append queries in Access 2010 web databases for use through a web browser. Instead, in the public beta of Access 2010 you were allowed to use a loop in a macro (For Each Record In) coupled with a Create Record action to create the records in the crossover table, but this was dropped in the Release Candidate of Access 2010, so what are we meant to do? You can still use an Access 2010 file (an Access 2010 “front end”) with the database and use Append queries in that, but what about if you want all your users to use the database only via a web browser?
I posed this question on a forum and was kindly answered by Krunal Sheth of Microsoft. My blog post fleshes out that answer to make it a bit clearer as it is an important point for Access 2010 developers. The idea is to split the action into two separate parts. The creation of our meeting (the addition of a new record in the meetings table) initiates a data macro making a note of the new MeetingID in the persons table. This in turn causes a second data macro (in the persons table) to create the new records in the crossover table. In more detail, it goes like this.
1) In the persons table (mine is called tblPersons), add a field called, “NeedToAttendMeetingID”.
2) In the meetings table (mine is called tblMeetings), add an After Insert data macro thus:
When you add a record to the meetings table (tblMeetings), the data macro fires and loops through all the records in your Persons table (tblPersons), setting the value of the NeedToAttendMeetingID field to the MeetingID automatically created in the record for our new meeting (since MeetingID is an Autonumber field and thus its value is automatically created). Thus all the persons in our persons table will now have details of this new meeting each time we add a new meeting to our meetings table.
3) In the persons table (tblPersons), create an After Update data macro thus:
This data macro is initiated by our having had the first data macro (the After Insert macro in the meetings table) update our persons table. The first line in this second data macro checks each NeedToAttendMeetingID field and, if a value has been entered into it (by the first data macro), this second data macro now goes on to create a new record in our crossover table (tblPersonMeetingX), setting both the MeetingID and the PersonID. Finally this second data macros tidies up the NeedToAttendMeetingID field.
This is a very different method to that used in standard Access databases, but it is logical, even though not particularly obvious.
Many thanks to Krunal Sheth for letting me know the basics.
Some time ago I was told about a utility for handling VBA errors centrally. Like just about every other Access developer on this planet I already had a system of handling my errors centrally, but was intrigued when I tried EverythingAccess.com’s “SimplyVBA Global Error Handler” http://www.everythingaccess.com/simplyvba-global-error-handler.htm. Wayne Phillips, who developed it, has provided lots of examples and information to help you as you start using it. As I’ve used it more and more I’ve done things my own way a bit and things have been settled for some months now so I am publishing my main piece of code below in case it is of help to anyone else.
The code below is from a standard module (mine is called “basErrorHandler” (though the name is not important) and can be copied directly into your own standard module. The main advantage of the error handler is that you don’t need to put code like the following into each of your procedures.
On Error GoTo Handler
….
ExitHere:
On Error Resume Next
Exit Sub
Handler:
MsgBox “Error No. “ & Err.Number & “. Error Description: “ & Err.Description ‘Or go to your central error handler . Resume ExitHere
MsgBox “Error No. “ & Err.Number & “. Error Description: “ & Err.Description ‘Or go to your central error handler .
Resume ExitHere
Instead, as long as you have run EnableErrorHandler in your startup code, e.g. from an Autoexec macro, you can leave all that out and the global error handler will catch any errors automatically. Cool or what?
If you want to run tidying up code in your procedure, you can add
ErrEx.Bookmark = erFinally
rst.Close
Set rst = Nothing
and the like. The point here is that if your code does hit an error, it will be returned to the line ErrEx.Bookmark = erFinally automatically and carry out any tidying up that you specify.
You can do lots of other things. For example, the error number 2501 can mean very different things in different contexts. Thus if you are about to send an e-mail, you can add
ErrEx.UserData = ErrorContext2501.MailCancelled
which will inform the error handler that the context is e-mail sending (and not a report with no data or cancellation of printing which are two other areas where error no. 2501 often comes up). The code in GlobalErrorHandler uses this information to then provide the user with a sensible error message.
You will probably notice that I have set up an Enum for ease of handling the different error numbers that Access/Jet/ACE use. Thus error number 94 signifies an invalid use of Null, but if you are like me you are not that likely to remember this unless you look it up. Use of an Enum simplifies things somewhat.
er94_InvalidUseOfNull
I’m aware that I have only scratched the surface of this very useful utility, but will come back to it in later posts.
Please note that I use 3 functions below:
==========================================================================================================
Option Compare DatabaseOption Explicit
Rem ##########################Rem Make sure EnableErrorHandler has run at startup. If you don’t this code wont’ work.
Rem ##########################
Rem Some error messages signify substantially different errors, e.g. 2501 can be returned in a function which callsRem a report with no data where that report’s NoData event has returned Cancel. Alternatively, it can signify thatRem an e-mail created via Automation was cancelled by the user. Thus, in order to make the error messages we give theRem user as clear as possible give GlobalErrorHandler a bit more info.Rem Use 1, 2, 4, 8, 16, etc. to allow use of bitwise comparisons.
Rem Error 2501.Rem ReportNoData is treated by GlobalErrorHander as default, but allow user to specify ErrorContext2501.MailCancelledRem and ErrorContext2501.PrintingCancelledRem at the start of their procedure if handling e-mail or even to explicitly set context to ErrorContext2501.ReportNoDataRem when opening a report.
Public Enum ErrorContext2501 MailCancelled = 1 ReportNoData = 2 PrintingCancelled = 4End Enum
Rem For this implementation we are using error markers with the same marker ids as error numbers for convenience
Public Enum ErrorBookmarks
Rem -1 to avoid conflicts with normal error numbers. erFinally = -1
Rem Use these bookmark when needing to run cleanup code. Rem Add to them as an when you find it useful.
er5_InvalidCallOrProcedure = 5 er11_DivisionByZero = 11 er13_TypeMismatch = 13 er53_FileNotFound = 53 er70_PermissionDenied = 70 er94_InvalidUseOfNull = 94 er429_UnableToCreateActiveX = 429 er2105_CantGoToSpecifiedRecord = 2105 er2110_CantSetFocus = 2110 er2176_PropertyTooLong = 2176 er2221_TextTooLong = 2221 er2465_FieldNotFound = 2465 er2467_ObjectClosedOrNotExist = 2467 er2474_ObjectNeedsToBeActiveControl = 2474 er2501_NoData = 2501 er3265_ItemNotFoundInThisCollection = 3265 er3021_NoCurrentRecord = 3021 er3022_DuplicateRecord = 3022 er3163_FieldTooSmall = 3163 er3200_RelatedRecords = 3200 er3270_PropertyNotFound = 3270 er3246_OperationNotSupportedInTransactions = 3246 er3314_RequiredDataMissing = 3314
End Enum
Public Function EnableErrorHandler()
Rem Note that this application needs a function called SimplyVBADLLUpdate which returns True or False which determines Rem whether it attempts to automatically update the local copy of the dll used by this application. Rem Note that it also needs a function called SimplyVBASourceFolder which returns a string Rem identifying the location of the location from which the dll is downloaded. If SimplyVBADLLUpdate returns False Rem SimplyVBASourceFolder can return any string you like, e.g. "Dummy Value". Rem An alternative way of handling this would be to use conditional compilation.
Dim strDLLSource As String Dim strDLLTarget As String Dim fso As Scripting.FileSystemObject Dim flDLLSource As Scripting.File Dim flDLLTarget As Scripting.File Dim blnUpdateDLL As Boolean Const cstrFailuremessage As String = "EnableErrorHandler() failed. Failed to activate the global error handler." Dim strMsg As String
blnUpdateDLL = SimplyVBADLLUpdate
If blnUpdateDLL = True Then Rem Check to see whether the version of the dll we are using is the most up to date one. strDLLSource = SimplyVBASourceFolder & SimplyVBADLLName End If
strDLLTarget = CurrentProject.path & "\" & SimplyVBADLLName
Set fso = New Scripting.FileSystemObject
If blnUpdateDLL = True Then If fso.FileExists(strDLLSource) = True Then Rem Found master copy of dll. If fso.FileExists(strDLLTarget) = True Then Rem Application already has a copy of the dll in same folder as application so need to check Rem to see whether it is up to date. Compare file sizes as using fso.GetFileVersion is very Rem slow across a WAN/VPN.
Set flDLLSource = fso.GetFile(strDLLSource) Set flDLLTarget = fso.GetFile(strDLLTarget) If flDLLSource.Size <> flDLLTarget.Size Then FileCopy strDLLSource, strDLLTarget End If Else Rem Application hasn’t yet got a copy of the dll in the same folder as application so take a copy Rem from the master location. FileCopy strDLLSource, strDLLTarget End If Else Rem Unable to file master copy of dll. strMsg = "The application has been unable to check whether there is an update to its error-handling system " _ & "due to a file missing from its expected location (" & strDLLSource & ")" MsgBox strMsg, vbCritical, "File missing" End If Else If fso.FileExists(strDLLTarget) = False Then MsgBox cstrFailuremessage, vbCritical, "Error-handler failure" ErrEx.GoToBookmark erFinally End If End If
Rem For use in Access (as opposed to, say, Excel, tell SimplyVBA that we are running this in an Access application. Rem Also tell it that our global error-handling procedure is called "GlobalErrorHandler". If ErrEx.EnableGlobalErrorHandler(Access.Application, "GlobalErrorHandler") = False Then MsgBox cstrFailuremessage, vbCritical, "Error-handler failure" End If
ErrEx.Bookmark = erFinally Set flDLLSource = Nothing Set flDLLTarget = Nothing Set fso = Nothing
End Function
Public Function DisableErrorHandler()
ErrEx.DisableGlobalErrorHandler
Public Sub GlobalErrorHandler()
Rem # IF SOMETHING IS NOT HAPPENING AS EXPECTED, MAKE SURE YOU HAVE ENABLED THE GLOBAL ERROR HANDLER!!!!! Rem If you have not run your usual start up code, e.g. via an Autoexec macro or a start up form, you Rem may not have enabled this global error handler.
If ErrEx.CurrentBookmark = erFinally Then Rem We got here from a Tidy Up section in the calling procedure so can ignore all errors, Rem e.g. we have just gone past a "ErrEx.Bookmark = erFinally" line Rem Such errors will be clean up code where it does not matter whether we get an error, e.g. we Rem may be attempting to close unopened recordsets. Rem In classic error handling there would be in an "On Error Resume Next" statement area so do the Rem equivalent here.
ErrEx.State = OnErrorResumeNext
Else Rem Not in a Tidy Up section so need to decide what to do with the error since they are errors Rem which require handling.
Select Case ErrEx.State
Case OnErrorResumeNext Rem We got here from an On Error Resume Next area so do nothing and allow the code Rem to continue on the next line in our procedure. Rem If we want to debug the code in our procedure, however, just rem ove Rem the On Error Resume Next statement.
Case OnErrorGotoLabel Rem We got here from an old style local error handler and this has caught the error. Rem Do nothing. Let local handler handle error. Rem No need to check for cleanup bookmarks as cleanup will be handled by local error handler. Rem If we want to record the error details, we need to call RecordErrorDetails function Rem from the local error-handler.
Case OnErrorGoto0, CalledByLocalHandler, OnErrorDebug, OnErrorEnd Rem No On Error Resume Next or old style local handler so we need to handle the error in this Rem GlobalErrorHandler procedure.
Rem Check specific cases first where we now our reaction to the error will always be the same. In Rem such cases we can handle the error totally witin this GlobalErrorHandler procedure. An example Rem might be a No Data error when opening reports where we always want to repond with the same Rem error message to the user. Rem Rem Our ErrorBookmarks enum assigns error numbers to bookmarks which reflect the error no. generated. Rem Thus a bookmark of bkmdNoData has been given a value of 2501 in the declaration area of this module Rem i.e. the error obtained when opening a report which contains no data. If (ErrEx.SetBookmarkAsOnErrorGoto(er2501_NoData) = True) _ And (ErrEx.Number = er2501_NoData) Then
If (ErrEx.UserData And ErrorContext2501.MailCancelled) = ErrorContext2501.MailCancelled Then
Rem We got here from an area covered by Rem Rem ErrEx.Bookmark = er2501_NoData Rem Rem i.e. where the error no. generated by the procedure is 2501 (= er2501_NoData) Rem and where the procedure has ErrEx.UserData = includes ErrorContext2501.MailCancelled Rem in order to specify that the 2501 Rem error is caused by the cancellation of an e-mail which the procedure created.
MsgBox "The e-mail was not sent.", , "E-mail cancelled"
Else
Rem We got here from an area covered by Rem Rem ErrEx.Bookmark = er2501_NoData Rem Rem i.e. where the error no. generated by the procedure is 2501 (= er2501_NoData) Rem and where there is NO ErrEx.UserData = "Mailing" added into the procedure which would have Rem specified that any 2501 error would come from the cancellation of an e-mail. Thus the 2501 Rem error must have come from a report being opened which has no data, so we need to handle Rem this specific error and then allow the procedure to carry on at its next line.
MsgBox "There is no data to show.", , "No data"
End If
Rem Now check for more general errors.
ElseIf ErrEx.SetBookmarkAsOnErrorGoto(ErrEx.Number) = True Then Rem Our ErrorBookmarks enum assigns error numbers to bookmarks which reflect the error no. generated. Rem Unlike the No Data error above where we can always handle it totally within this GlobalErrorHandler Rem procedure, errors handled from this point on may require handling in different manners in different Rem procedures so we push the actual error-handling back to the procedure where the error occurred. Rem Thus er429_UnableToCreateActiveX has a value of 429, i.e. the error no. assigned when, say, Rem we have attempted to set a Word application object when Word is not yet running with code such as Rem Rem Set wrdApp = GetObject(, "Word.Application") Rem Rem Thus if we have a line of code Rem Rem ErrEx.Bookmark = er429_UnableToCreateActiveX Rem Rem in our code, the flow of the program will now move to that bookmark, allowing us to carry out any actions Rem appropriate to that error, e.g. Rem Rem ErrEx.Bookmark = er429_UnableToCreateActiveX Rem Set wrdApp = CreateObject("Word.Application") Rem Resume Next
ElseIf ErrEx.SetBookmarkAsOnErrorGoto(erFinally) = True Then Rem The error has not been caught by any specifically set bookmark, but we do have a bookmark called erFinally Rem so we handle it in a general fashion by recording the error details before returning the flow of the Rem program to that bookmark Rem Rem ErrEx.Bookmark = erFinally
RecordErrorDetails
Else Rem The error has not been caught by any specifically set bookmark for catching specific errors, we have Rem no bookmark named erFinally, we are not in an On Error Resume Next area and we have no local Rem error-handling procedure so we record the error details and then exit the procedure where the Rem error occurred.
ErrEx.State = OnErrorExitProcedure
Case OnErrorPropagate ' Added in 1.3.1 (27th Feb 2009) so may be a better way of handling this. Need to think through.
End Select End If
End Sub
Public Sub RecordErrorDetails() Rem Assumes a reference to Microsoft Scripting Runtime (scrrun.dll). If you don’t want to set this reference, Rem you will need to use late binding.
Dim strMsg As String Dim fso As FileSystemObject Dim ts As TextStream Dim strErrorLogLocation As String Dim intError As Integer Dim strPadding As String Dim errDAO As DAO.Error
Rem Location of error text file will be in the same folder as the front end. strErrorLogLocation = CurrentProject.path & "\ErrorLog.txt"
Rem Create basic error message for user to see. We shall record more details later to Rem put into the error log though. strMsg = "Error No. " & Err.Number & ". Error Description: " & Err.Description If ERL = 0 Then strMsg = strMsg & "." & vbCrLf & vbCrLf & "See " _ & strErrorLogLocation & " for more information." Else strMsg = strMsg & " in line no. " & ERL & "." & vbCrLf & vbCrLf & " See " _ & strErrorLogLocation & " for more information." End If
MsgBox strMsg, vbInformation, pcstrAppName
Rem Now we shall get as much information as we can and store it in the error log.
With ErrEx
strMsg = UnderlinePlainText("GENERAL INFO:") & vbCrLf _ & "Date/Time: " & Now & vbCrLf _ & "Error No: " & .Number & vbCrLf _ & "Error Description: " & .Description & vbCrLf If InStr(.DLLVersion, "DISTRIBUTABLE") > 0 Then Rem Using the distributable version of the dll so can check the VariablesInspector. strMsg = strMsg & vbCrLf & UnderlinePlainText("VARIABLES INFO:") & vbCrLf _ & "Variables:" & vbCrLf & .VariablesInspector.DumpAll ' DumpAll includes a vbCrLf on the end End If End With
Rem Determine which procedure will handle the error in case, say, error is in a procedure with no local handler but Rem has been called by a procedure which does have one. With ErrEx.CallStack If ErrEx.State = OnErrorPropagate Then .FirstLevel Do If .HasActiveErrorHandler = True Then strMsg = strMsg & vbCrLf & UnderlinePlainText("PROPAGATION INFO:") & vbCrLf _ & "Error will propagate to " & .ProcedureName & " in " & .ModuleName & " in " & .ProjectName & vbCrLf Exit Do End If Loop While .NextLevel End If
Rem Record the above error data as well as any more we can pick up by working through the call stack. Rem Indent more for each procedure in call stack. .FirstLevel
strMsg = strMsg & vbCrLf & UnderlinePlainText("CALL STACK INFO:") & vbCrLf Do If strPadding > "" Then strMsg = strMsg & vbCrLf & strPadding & "Called by:" End If strMsg = strMsg & strPadding & "Project Name: " & .ProjectName & vbCrLf _ & strPadding & "Module Name: " & .ModuleName & vbCrLf _ & strPadding & "Procedure Name: " & .ProcedureName & vbCrLf _ & strPadding & "Line No. " & .LineNumber & vbCrLf _ & strPadding & "Has Active Error Handler: " & .HasActiveErrorHandler & vbCrLf
If ErrEx.IsDebugable Then Rem Not using an MDE/ACCDE so can retrieve the line of actual code. strMsg = strMsg & strPadding & "Line Code: " & .LineCode & vbCrLf End If strPadding = String(4, " ") & strPadding
Loop While .NextLevel
End With
Rem Note that VBA.Err object is not the same as DBEngine.Errors collection. Rem VBA.Err holds info on the last VBA error. Rem DBEngine.Errors collection holds information on the last DAO problem. Note that one DAO operation Rem can generate more than one error object. Note too that it stays in the Errors collection until Rem the next DAO operation fails and does not automatically clear itself. Therefore, we need to ensure Rem we donrem t start using out-of-date information from the Errors collection. To do this we need to Rem see whether the VBA.Err info is the same as the relevant info in the DAO.Errors collection. Rem The VBA.Erro info should be the same as the last item in the DAO.Errors collection. Make that Rem our first check.
Rem There should always be at least on item in the Errors collection since we are in an error-handling situation, Rem but check just in case we have got here in some weird manner. If Errors.Count > 0 Then If VBA.Err.Number <> Errors(Errors.Count - 1).Number Then Rem Shouldnrem t need to run next line, but reports in internet user groups suggest that sometimes Rem it is needed. A refresh is included in the code example in Access 2007 VBA book by Wrox. Rem Better safe than sorry. Errors.Refresh End If If VBA.Err.Number <> Errors(Errors.Count - 1).Number Then Rem Despite refresh, error numbers do not match so Errors collection must contain Rem data from an old DAO problem. Note that Errors collection does not get Rem emptied automatically. Else Rem New DAO error so record and display the DAO information. For Each errDAO In Errors strMsg = strMsg & vbCrLf & "Errors(" & intError & "):" & vbCrLf _ & "Error No. " & errDAO.Number & vbCrLf _ & "Error Description: " & errDAO.Description & vbCrLf _ & "Error Source: " & errDAO.Source & vbCrLf & vbCrLf intError = intError + 1 Next errDAO End If End If
strMsg = strMsg & "=========================================================" & vbCrLf & vbCrLf
Rem Now store the error information in the error log text file. Set fso = New FileSystemObject If Debugging = True Then Rem May be using it a lot so donrem t want to create a long file. Only keep one entry. Set ts = fso.OpenTextFile(strErrorLogLocation, ForWriting, True) Else Set ts = fso.OpenTextFile(strErrorLogLocation, ForAppending, True) End If
ts.WriteLine strMsg
Rem Tidy up. ts.Close Set ts = Nothing Set fso = Nothing
If Debugging Then FollowHyperlink strErrorLogLocation End If
Public Function UnderlinePlainText(TextToUnderline As String) UnderlinePlainText = TextToUnderline & vbCrLf & String(Len(TextToUnderline), "=")End Function
Access made tentative steps towards being usable across the internet in Access 2003 and then was given more oomph in Access 2007. Microsoft have been integrating Access more and more with Sharepoint, which is what allows Access to be used across the Internet. However, there were still problems with using Access 2007 and Sharepoint together, largely due to Sharepoint not being a “relational database”. However, the upcoming releases of Access 2010 and Sharepoint 2010, when used together, will enable Access to be used “properly” across the Internet for the first time.
There are a number of reasons for this. Firstly, Sharepoint can now properly be described as including a “relational database”. Sharepoints lists can now be used in much the same way as in “proper” databases like Access itself, SQL Server and Oracle. Secondly,there is the much heralded new ability of Access to create proper web pages in conjunction with Sharepoint 2010 (when a new piece of functionality called “Access Services” is installed). This will allow Access 2010 to create web pages which only require a web browser like Internet Explorer, Safari or Firefox to access those pages. However, unless you pay out a lot of money for the correct licence to allow absolutely everybody to access your Sharepoint site, Sharepoint is going to continue to be used to provide secure access to data to a limited number of users, e.g. on an intranet or extranet. That this will be a winner I have very little doubt, but I’ll leave Microsoft and others to go into detail about how this works.
However, what excites me more (as a developer of database-related solutions for small to medium-sized organisations) is the ability to use Access very much in its normal manner with its usual forms, reports and programming code, yet hold the data in a location where, with the right security, any authorised person can access it. Let me explain….
“Normal” Access databases, whether used with Access’ own database engine or with a server database, give the user very good functionality, much better in fact than your average web page. The Access user interface is an example of that provided by a “rich client”, i.e. “rich” in functionality and running on the “client” which is techno-speak for the user’s PC. It is much harder, if not impossible, to give a web page seen in a web browser the same sort of functionality. Where a web page seen in a web browser scores an advantage is that it is not necessary to install an extra program (Access) in order to access a web page (since all PC’s have a web browser) and, more importantly, a web page can be accessed easily over the Internet. The exciting thing about using Access 2010 with Sharepoint 2010 is the ease of creating web pages or, if you want the functionality of a rich client, the ability to use an Access 2010 rich client with data held in Sharepoint 2010, both over the Internet. It is this last scenario which most excites me. Though it requires the installation of at least the freebie Access 2010 runtime version on each user’s PC, it can let the user take the data offline, e.g. onto a laptop and then disconnect it from the Internet yet allow continued use of the data, and then later reconnect and synchronise the data with the main database.
Scenario
Web Browser + Server Database
Web Browser + Hosted Server Database
Access-only solution (or with Terminal Services & VPN)
Access + Server Database
Access 2010-generated web pages with Sharepoint 2010 database running Access services
Access 2010 runtime with Sharepoint 2010 database (Access services not needed).
Small number of users on a local area network (LAN).
Poor value for money as web browser is not easy to program and need a server database.
Good value. Access’ main area of strength up until now.
Reasonable value for money. Need to install server database.
If you already have a suitable Sharepoint server available, e.g. a hosted system like those at Accesshosting.com or, in the future, Microsoft themselves, this is good value for money. If you don’t have such a server, don’t go this route as it will be expensive.
Good value for money. Gives more functionality than web pages. Could use a hosted Sharepoint database.
Large number of users on a LAN.
Good value for money.
Access will struggle to perform fast enough. Not a good solution.
Good value for money, but need to install Access runtime on each user’s PC. Need to install server database.
Good value for money, inc. if using a hosted Sharepoint database. Less functionality than with a rich client, but no need to install Access runtime.
Good value for money. Gives more functionality than web pages. Could use a hosted Sharepoint database. Need to install Access runtime.
Small number of users working across the internet.
Poor value for money, though security may be complicated. Need to install server database and VPN though.
Reasonable value for money.
Not suitable technically unless used with Terminal Services and a VPN.
Good value for money. Need to install server database and VPN though.
Good value for money, inc. if using a hosted Sharepoint database.
Large number of users working across the internet.
Good value for money. Need to install server database and VPN or use hosted database.
Not suitable technically unless used with Terminal Services and a VPN. Will still struggle.
Needs a virtual private network. Would then be a good solution.
Reasonable value for money (or can use a hosted Sharepoint database). Gives more functionality than web pages do, but need to install Access runtime program.
Security
Good
Poor
Reasonable.
Reasonable
There is an excellent article by Russell Sinclair on the Access Team Blog entitled “Access 2010: How to write expressions for Web queries, forms, and reports” at http://blogs.msdn.com/access/archive/2010/01/26/access-2010-how-to-write-expressions-for-web-queries-forms-and-reports.aspx. I’ve made a note of it here for my own easy reference. If you intend to create web databases in Access 2010, you’ll need to know about the stuff in Russell Sinclair’s article.
Maybe I am getting old, but during my time of trying out the Access 2010 public beta and publishing web databases to Sharepoint, I find I keep forgetting what the exact URL is that I published to. However, there are a couple of easy ways of finding this out. In response to a question on this subject on the Access Team blog at http://blogs.msdn.access/access, Albert Kallal suggested two methods:1) Press Ctrl/g to open the Immediate window in the Access application you have published and then type in Debug.Print CurrentProject.Website followed by pressing Enter. The correct URL will be printed to the Immediate Window. 2) Open up the Navigation Pane in the Access application you have published and hover the cursor over one of the web tables. The screentip will then give you the URL. However, unlike option 1) above, you can’t copy and paste this value.