Microsoft Access, Office/VBA, VB6 Modules royalty-free source code library

Microsoft Access Modules royalty-free source code library

Microsoft 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 Build and publish an Access Database to SharePoint for more information.
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 Build and publish an Access Database to SharePoint for more information.
DisplayAllViewsOnSP	Procedure	Create a user defined property named 'DisplayAllViewsOnSharePointSite', which controls the availability of all forms and reports in the database. See Build and publish an Access Database to SharePoint for more information.
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

Overview 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.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!