Total Visual SourceBook

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office 2007 and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

Module: SharePointDatabases in Category Microsoft SharePoint : Databases from Total Visual SourceBook

Routines for working with Access databases on SharePoint from Access VBA.

Procedure List

Procedure Name

Type

Description

(Declarations) Declarations Declarations and private variables for the modSharePointDatabases module
CreateDatabaseLinkedToSharePoint Procedure In Access 2007 or later, NewCurrentDatabase can be used to create a new database linked to SharePoint.
ImportSharePointData Procedure Use the TransferSharePointList method to import or link data from a Microsoft Windows SharePoint Services site.
OpenSPSite Procedure Open a SharePoint site in your browser Pass the full path for a database on SharePoint to open the database in Access, as an alternative for OpenDatabaseFromSP
OpenDatabaseFromSP Procedure Open a database from SharePoint in your browser.
IsDatabaseOnline Procedure See if the database is in Online mode. When a database is linked to SharePoint, you can work offline to make changes locally and synchronize it with the server when you reconnect. This option caches the list data locally for anyone who has access to the database (even if they don't have permission to view the data on the SharePoint site).
ToggleDBOnOrOffLine Procedure Toggle the table lists online or offline. When a database is linked to SharePoint, you can work offline to make changes locally and synchronize it with the server when you reconnect. This option caches the list data locally for anyone who has access to the database (even if they don't have permission to view the data on the SharePoint site).
IsDatabaseCached Procedure See if the database is in Cached mode. When a database is linked to SharePoint, you can work offline to make changes locally and synchronize it with the server when you reconnect. This option caches the list data locally for anyone who has access to the database (even if they don't have permission to view the data on the SharePoint site).
ToggleCacheListData Procedure Toggle cached mode on or off. When a database is linked to SharePoint, you can work offline to make changes locally and synchronize it with the server when you reconnect. This option caches the list data locally for anyone who has access to the database (even if they don't have permission to view the data on the SharePoint site).
GetPublishPath Procedure When a SharePoint database is opened in Access, Access allows you to publish the database to the SharePoint site (or save changes to the server). This function returns the full URL of the database on the SharePoint site.
DisallowPublish Procedure When a SharePoint database is opened in Access, Access allows you to publish the database to the SharePoint site (or save changes to the server). In some cases, you may not want database users to republish to the original site. This function deletes the PublishURL property, so that the user cannot publish to the SharePoint site.
DisplayOnSP Procedure Control the availability of a particular form or report on SharePoint. Available settings are "Follow Table Settings" and "Do Not Display". See "Control which forms and reports are available on a SharePoint site" (http://office.microsoft.com/en-us/access/HA102093071033.aspx) for details.
DisplayViewsOnSP Procedure Control the availability of all forms and reports in the database that use the table. Forms or reports that are based on other tables will not be affected by this change. See "Control which forms and reports are available on a SharePoint site" (http://office.microsoft.com/en-us/access/HA102093071033.aspx) for details
DisplayAllViewsOnSP Procedure Create a user defined property named 'DisplayAllViewsOnSharePointSite', which controls the availability of all forms and reports in the database. See "Control which forms and reports are available on a SharePoint site" (http://office.microsoft.com/en-us/access/HA102093071033.aspx) for details.
DiscardChanges Procedure Discard changes that are made in an offline copy of a database connected to SharePoint. When working in an offline database connected to SharePoint, there are two options available for discarding changes. "Discard All Changes" and "Discard All Changes and Refresh".
SynchronizeWithSP Procedure Synchronize the current database with SharePoint.

Example Code for Using Module: SharePointDatabases

' Example of the modSharePointDatabases module
'
' To use this example:
' 1. Create a new form.
' 2. Create the following command buttons:
'       cmdCreate
'       cmdImport
'       cmdOpenSite
'       cmdOpenDB
'       cmdToggleDB
'       cmdToggleCache
'       cmdDisplay
'       cmdSynch
'       cmdDisallowPublish
' 3. Create the following text boxes:
'       txtSPSite
'       txtDB
' 4. Paste the entire contents of this module into the new form's module.

Private Const mcstrExample As String = "C:\Total Visual SourceBook 2013\Samples\SPTest.accdb"

Private Sub cmdCreate_Click()
  Dim strMsg As String

  If (Me.txtSPSite = "http://YourSPSite") Or (Me.txtSPSite = "") Then
    MsgBox "First, specify your SharePoint site."
    Me.txtSPSite.SetFocus
  Else
    strMsg = "Are you sure you want to create database: " & vbCrLf & _
             Me.txtDB & vbCrLf & _
             "on SharePoint site " & Me.txtSPSite & "?"
    If MsgBox(strMsg, vbYesNo) = vbYes Then
      If CreateDatabaseLinkedToSharePoint(Me.txtDB, , acNewDatabaseFormatAccess2007, , Me.txtSPSite) Then
        MsgBox "Database created"
      End If
    End If
  End If
End Sub

Private Sub cmdDisallowPublish_Click()
  Dim strMsg As String

  strMsg = "Are you sure you want to disallow publishing in this database?" & vbCrLf & vbCrLf & _
           "This will make a permanent change to the database."
  If MsgBox(strMsg, vbYesNo) = vbYes Then
    DisallowPublish
    MsgBox "Publishing is no longer allowed"
  End If
End Sub

Private Sub cmdDisplay_Click()
  Dim strMsg As String
  Dim strTable As String
  Dim strObj As String

  If MsgBox("Are you sure you want to display objects on SP site?", vbYesNo) = vbYes Then
    strMsg = "Do you want to display all tables, or a single table?" & vbCrLf & _
             "Click Yes to display all tables, or No to choose a single table."
    If MsgBox(strMsg, vbYesNo) = vbYes Then
      DisplayAllViewsOnSP True
    Else
      strTable = InputBox("Enter the table name:")
      DisplayViewsOnSP strTable, True
    End If

    strObj = InputBox("Enter the name of the form to display:")
    DisplayOnSP Forms(strObj), True

  End If
End Sub

Private Sub cmdImport_Click()
  Dim strList As String

  If (Me.txtSPSite = "http://YourSPSite") Or (Me.txtSPSite = "") Then
    MsgBox "First, specify your SharePoint site."
    Me.txtSPSite.SetFocus
  Else

    If MsgBox("Are you sure you want to import a SharePoint list from " & Me.txtSPSite & "?", vbYesNo) = vbYes Then
      strList = InputBox("Name or GUID of the list to import (e.g. Events, Document Library, etc.):")
      ImportSharePointData acImportSharePointList, Me.txtSPSite, strList
    End If
  End If
End Sub

Private Sub cmdOpenDB_Click()
  Dim strPubPath As String
  strPubPath = GetPublishPath
  OpenDatabaseFromSP strPubPath
End Sub

Private Sub cmdOpenSite_Click()
  OpenSPSite Me.txtSPSite
End Sub

Private Sub cmdSynch_Click()
  If IsDatabaseOnline Then
    MsgBox "This command is only available when the database is offline."
  Else
    DoCmd.Close
    SynchronizeWithSP
  End If
End Sub

Private Sub cmdToggleCache_Click()
  If IsDatabaseOnline Then
    DoCmd.Close
    If IsDatabaseCached Then
      ToggleCacheListData (False)
    Else
      ToggleCacheListData (True)
    End If
  Else
    MsgBox "This command is only available when the database is online."
  End If
End Sub

Private Sub cmdToggleDB_Click()
  DoCmd.Close
  If IsDatabaseOnline Then
    ToggleDBOnOrOffLine False
  Else
    If MsgBox("Keep changes?" & vbCrLf & vbCrLf & "Click Yes to keep changes, or No to discard changes.", vbYesNo) = vbNo Then
      DiscardChanges (True)
    End If
    ToggleDBOnOrOffLine True
  End If
End Sub

Private Sub Form_Load()
  Me.txtDB = mcstrExample
  Me.txtSPSite = "http://YourSPSite"
  Me.cmdCreate.Caption = "Create New Db"
  Me.cmdImport.Caption = "Import SP List"
  Me.cmdOpenSite.Caption = "Open SP Site in Browser"
  Me.cmdOpenDB.Caption = "Open SP Database in Browser"
  Me.cmdDisplay.Caption = "Display Objects on SP Site"
  Me.cmdSynch.Caption = "Synchronize with SP"
  Me.cmdDisallowPublish.Caption = "Disallow Publishing DB"
  SetToggleBtnCaptions
End Sub

Private Sub SetToggleBtnCaptions()

  If IsDatabaseOnline Then
    Me.cmdToggleDB.Caption = "Work Offline"
  Else
    Me.cmdToggleDB.Caption = "Work Online"
  End If

  If IsDatabaseCached Then
    Me.cmdToggleCache.Caption = "Do not Cache List Data"
  Else
    Me.cmdToggleCache.Caption = "Cache List Data"
  End If

End Sub

Microsoft Access Module LibraryOverview of Total Visual SourceBook

The source code in Total Visual SourceBook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Additional Resources