Adding Code to a Data Access Page for Offline Redirection

See Also

For the Web pages in your team solution to function correctly when taken offline, they must have script making it possible for them to connect to the offline data source when offline and then reconnect to the online data source. This is referred to as a redirection script, because it redirects the data source depending on whether the page is being used online or offline.

As with any script used in a series of pages, the best approach to offline redirection is to create a resource text file containing the functions and then call that text file from your Web page script using the 

src = command
. For example, the following script calls the modConStr.vbs file that contains additional functions: 

<SCRIPT id=connectionStringVBS language=vbscript src="./modConStr.vbs"></SCRIPT>

For information about Web scripting, visit the Microsoft Developer Network (MSDN™) Online Web Workshop at http://msdn.Microsoft.com/workshop/, or refer to the Microsoft Script Technologies Web site at http://msdn.Microsoft.com/scripting/.

To create the offline redirection script

  1. Create your data access page. For details, see Creating Data Access Pages.

  2. Add script to the data access page. For details, see Adding script to a data access page.

  3. Create a resource text file containing functions. For details, see Creating a resource file for HTML scripting.

Adding Script to a Data Access Page

Once you have created your data access page, you can modify it using any text or HTML editor, such as Access, Microsoft Visual InterDev®, or even Microsoft Notepad. This example uses the Microsoft Script Editor available in Design view in Access.

To add script to a data access page

  1. After creating your data access page in Access 2000, open it in Design view.

  2. Right-click any part of the data access page that is not a control or label, and from the shortcut menu, select Microsoft Script Editor.

  3. In the main window of the Microsoft Script Editor, locate the <HEAD> and <BODY> tags.

  4. Place the cursor between the <HEAD> and <BODY> tags, and add a few blank lines. Then, copy the following code, and paste it between those two tags.

    Note   The following script requires the modConStr.vbs file. For details about creating this file, see Creating a Resource File for HTML Scripting later in this document.

    <!----------------Create Global Connection------------>
<SCRIPT id=connectionStringVBS language=vbscript src="./modConStr.vbs"></SCRIPT>
<SCRIPT id=CreateConnectionScript language=vbscript>
<!--
Dim oConnection
CreateConnection
Sub CreateConnection
   Dim strConnectionString
   strConnectionString = GetConnectionString()
   set oConnection = CreateObject("ADODB.Connection")
   oConnection.ConnectionString = strConnectionString
   On Error Resume Next
   oConnection.Open
   If Err.number <> 0 Then
      msgbox "The following error has occurred:  " & Err.description
      window.navigate ("about:Blank")
      ' window.close
   End If
   On Error GoTo 0
   document.all("msodsc").ConnectionString = strConnectionString
End Sub
-->
</SCRIPT>
<!--------------END Create Global Connection END------------>
  5. Save the script by clicking Save or selecting Save from the File menu.

Creating a Resource File for HTML Scripting

A resource file is a text file that contains only script. This file can be referred to using the using the 

src =
command. For example, you can place the following code in your Web page or workflow script, and the functions in the modConStr.vbs file will be executed.

<SCRIPT id=connectionStringVBS language=vbscript src="./modConStr.vbs"></SCRIPT>

For more information about scripting, refer to the Microsoft Script Technologies Web site at http://msdn.Microsoft.com/scripting/.

To create a resource text file

  1. In a text editor, such as Notepad, type or copy the following code.

  2. Save the file as modconstr.vbs, and copy it to the root directory for the Web site associated with your team solution.

    Code for the Resource File 

    ' Change the sqlServerDatabase and sqlServerName variables to reflect 
' how your system is set up. This needs to be changed for every 
' time you want to take a different data access page offline.
dim sqlServerDatabase as string
dim sqlServerName as string
sqlServerDatabase = "Set database name here" 'Change this
sqlServerName = "Set SQL Server name here" 'Change this

Function GetConnectionString
   If window.navigator.onLine = True Then
      GetConnectionString = modGetConnectionStringEx("onLine","Application")
   Else
      GetConnectionString = modGetConnectionStringEx("offline","Application")
   End If
End Function

Function GetSystemDBConnectionString
   If window.navigator.onLine = True Then
      GetSystemDBConnectionString = modGetConnectionStringEx("onLine","System")
   Else
      GetSystemDBConnectionString = modGetConnectionStringEx("offline","System")
   End If
End Function

Function modGetConnectionStringEx(strType,strDBType)
   Dim strServer, strLogon, strDB
   strLogon = "Integrated Security=SSPI;"
   Select Case UCase(strType)
      Case "ONLINE"
         strServer = modGetServer()
      Case "OFFLINE"
         strServer = modGetLocal()
         strLogon = ""
      Case Else
         strServer = ""
   End Select
   Select Case UCase(strDBType)
      Case "APPLICATION"
         strDB = modGetApplicationDB()
      Case "SYSTEM"
         strDB = modGetSystemDB()
      Case Else
         strDB = ""
   End Select

   modGetConnectionStringEx = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=" + strDB + ";Data Source=" + strServer + ";" + strLogon
End Function

Function modGetSystemDB()
   modGetSystemDB = "modSystem"
End Function

Function modGetApplicationDB()
   Dim oLocalCon, rsLocal, strConnectionString, strPassword, strLogon
   ' Localization Resource Identifer
   Const L_UnableToConnect_Message = "Unable to connect to the offline system database. Make sure you have valid login permissions and that the server is running. Contact your server administrator for assistance."
   Const L_EnterPassword_Message = "Enter your password: "
   Const L_InvalidPassword_Message = "Unable to connect to the offline system database. Your password may have been entered incorrectly. "
   Const L_CantGetOfflineDBName_Message = "Unable to retrieve the name of the offline database from the offline modSystem database."
   If window.navigator.onLine = True Then
      modGetApplicationDB = sqlServerDatabase
   Else
      On Error Resume Next
      Set oLocalCon = CreateObject("ADODB.Connection")
      If Err.Number = 429 Then
         ' Unable to create ActiveX object, not safe
         ' User receives message from browser, so exit the page
         gAbort = True
         Exit Function
      End If
      If modGetOS() <> "Windows NT" Then
         strLogon = ""
      Else
         strLogon = "Integrated Security=SSPI;"
      End If
      strConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=" + modGetSystemDB() + ";Data Source=(local);" + strLogon
      strPassword = GetValue(document.cookie,"Password")
      intTries = 1
      Do
         oLocalCon.ConnectionString = strConnectionString
         If strPassword <> "" Then
            oLocalCon.Properties("Password") = strPassword
         End If
         oLocalCon.Open
         If oLocalCon.State <> 1 Then
            If InStr(strConnectionString,"Integrated Security") <> 0 Then
               msgbox L_UnableToConnect_Message & Chr(13) & Chr(13) & Err.description,vbCritical,gDialogTitle
               gAbort = True
               window.navigate "about:blank"
               Exit Function
            Else
               If intTries > 1 Then
                  strPrompt = L_InvalidPassword_Message & chr(13) & chr(13) & L_EnterPassword_Message
               Else
                  strPrompt = Chr(13) & L_EnterPassword_Message
               End If
               If strRetValue = "" Then
                  gAbort = True
                  window.navigate "about:blank"
                  Exit Function
               End If
               strPassword = strRetValue
               intTries = intTries + 1
               oLocalCon.Properties("Password").Value = strPassword
            End If
         End If
      Loop Until oLocalCon.State = 1

      If InStr(document.cookie,"Password=") = 0 And strPassword <> "" Then   
         document.cookie = ("Password=" & strPassword & ";")
      End If
      On Error Resume Next
      Set rsLocal = CreateObject("ADODB.Recordset")
      If Err.Number = 429 Then
         ' Unable to create ActiveX object, not safe
         ' User receives message from browser, so exit the page
         gAbort = True
         Exit Function
      End If
      rsLocal.Open "SELECT OfflineDatabase FROM modSystem..modApplications WHERE [Server]='" & modGetServer() & "' AND [Database]='" & sqlServerDatabase & "'", oLocalCon, 3
      If Err.number <> 0 Then
         HandleDBErrors L_CantGetOfflineDBName_Message
         gAbort = True
         Exit Function
      End If
      modGetApplicationDB = rsLocal.Fields("OfflineDatabase").Value
      If Err.number <> 0 Then
         HandleDBErrors L_CantGetOfflineDBName_Message
         gAbort = True
         Exit Function
      End If
   End If
End Function

Function modGetServer()
   modGetServer = sqlServerName
End Function

Function modGetLocal()
   modGetLocal = "(local)"
End Function

Function modGetOS()
   If InStr(window.navigator.userAgent,"Windows NT") <> 0 Then
      modGetOS = "Windows NT"
   Else
      modGetOS = "Windows"
   End If
End Function