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

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

Procedure List

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.

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)