"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
|
|
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
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
|
|