PDTL

Making technology work for you.

Home
Solutions Development
About Us
Contact Us
Site Map
Resources
Information Security Policy Statement
Where to find us
SMOG
Blog
08 March

Creating records in a many-to-many table in an Access 2010 Web Database

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)
PersonID
MeetingID

Thus the design of our new database, with some other fields also added in, will look like this:

image

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:

PersonMeetingID PersonID MeetingID
1 10 6
2 11 6
3 10 7
4 10 8
5 10 8

 

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:

image

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:

image

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.



06:31 GMT  |  Read comments(0)

02 March

Error Handling using EverythingAccess.com’s SimplyVBA Global Error Handler

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

 

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:

  • SimplyVBADLLUpdate is a boolean function which specifies whether the application should try and download the latest version of the the dll from a central location
  • SimplyVBADLLName is just the name of the dll file.
  • SimplyVBASourceFolder is the name of folder where the dll file is held ready for download

==========================================================================================================

Option Compare Database
Option 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 calls
Rem  a report with no data where that report’s NoData event has returned Cancel. Alternatively, it can signify that
Rem  an e-mail created via Automation was cancelled by the user. Thus, in order to make the error messages we give the
Rem  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.MailCancelled
Rem  and ErrorContext2501.PrintingCancelled
Rem  at the start of their procedure if handling e-mail or even to explicitly set context to ErrorContext2501.ReportNoData
Rem  when opening a report.

Public Enum ErrorContext2501
   MailCancelled = 1
   ReportNoData = 2
   PrintingCancelled = 4
End 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

End Function

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.

               RecordErrorDetails

               ErrEx.State = OnErrorExitProcedure

            End If

         Case OnErrorPropagate   ' Added in 1.3.1  (27th Feb 2009) so may be a better way of handling this. Need to think through.

            RecordErrorDetails

      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

End Sub

Public Function UnderlinePlainText(TextToUnderline As String)
   UnderlinePlainText = TextToUnderline & vbCrLf & String(Len(TextToUnderline), "=")
End Function



10:27 GMT  |  Read comments(0)

16 February

Microsoft Office Access and the Internet

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.

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.

Reasonable value for money. Need to install server database.

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.

Good value for money. Gives more functionality than web pages. Could use 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.

Good value for money.

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.

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.

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

Good

Poor

Reasonable.

Good

Reasonable



12:37 GMT  |  Read comments(0)

27 January

How to write expressions for Web queries, forms, and reports in Access 2010

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.



04:28 GMT  |  Read comments(0)

21 January

Remembering where you published your Access 2010 database to

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.




06:10 GMT  |  Read comments(0)

Add your content here