Access Site Creation
The Problem
There is a need to allow many users to use the contents of an Access database concurrently and/or maintain an Access database across many users while maintaining data integrity.
The Approach
Create a SharePoint site / Sub-site which integrates the Access data in tables to allow some users to use the Access Database file to manipulate and view data and others to use a SharePoint interface.
The Result
Create a SharePoint Sub-Site
From the main collection site (not the admin console) click
the wheel and select Site Contents
In the Site Contents Page, under Subsites heading click on
“new subsite” Link.
In the settings section give the site a Title (this is
displayed in the subsite), a description (optional), and a url name (access, or
the name of the database the site will house WITHOUT spaces). In the Template
select ‘Team Site’. Leave the rest of the settings as the default.
Remove
the default ‘Get Started’ bar
Edit Site settings:
Edit the Site Permissions
Click Stop Inheriting Permissions
In the Set Up Groups … page click on create new groups for
the three permission levels. The administrator account will be included in the
Members and Owners groups – DO NOT REMOVE THIS. Add members to these groups as
is appropriate (can be done after as well). Once the members are set click OK
to save.
What this step does is create groups to enable access
control to the specific database(s) that will be housed in this site. You can
add/remove members of these groups at any time by editing the group memberships
(Site Settings -> Groups, Select the one to edit and edit)
Go into the URL bar of the browser and select the root of
the new sub-site you created (not the trailing slash or the _layouts… bits).
Copy this (CTRL+C)
Open the desired Access Database
Click the Database Tools tab and click SharePoint from the
menu
In the pop-up, paste the url you just copied and click Next.
Go back to the browser and refresh (you may need to log out
and log back in at this point to refresh the session cookies).
Click on the Wheel and select Site Contents. You should see
new tables in the list of items that correspond to the tables in the access
database.
Close the Access Database and exit Access, saving when
prompted.
Back to the browser window – click ‘Documents’. In the
Documents List Click ‘New Document’ and browse to the database you just paired
with SharePoint. Upload and add relevant information.
This allows users to download the database (from the
Documents Library visible on the front page of this site) and when opened
locally they can edit information in the database which is then paired with the
site.
Optionally and more readily accessible you can give users
links to the lists in SharePoint and they can edit information there which is
linked to the Access database.
Security is managed at the site level. This will prevent
people without membership in at least the members group from editing table data
and anyone not in any of the groups cannot access the data at all.
Logging into Access as a SharePoint user (different from the desktop account)
To use SharePoint login you can login to Access as a different user.
-
Open the Access Database and click the File Ribbon Menu item.
-
Click the Switch User link and enter
your SharePoint credentials and open the shared database.
Going Forward
Next steps would include building reports and other BI type of stuff using this data.
Notes / Bugs
In the event the upload / migration fails:
I've noticed a trend here which is conveniently not documented anywhere in
Microsoft’s documentation – generally I love finding this kind of gem but it is
getting old fast lol.
Access
Date/Time fields which use just Time (i.e. 5:00 PM va 1/1/2000 5:00 PM) do not
allow migration to SharePoint via the export vehicle. Thus these fields need to
be changed to type Short Text before migration (they can be changed back to
Date Time in the SharePoint ui after if this is required – gotta love data
types!)
Access
Memo fields and apparently Long Text allow really long strings. Unfortunately
these are not properly transformed to the SharePoint Memo or Blob types but are
instead transformed to Multi-line text rich text types which have a hard cap
~8k characters – including the ‘rich text’ characters which is seems to wrap
around every letter …. So these types need to have the filed contents trimmed
(potential for data loss here so I back up to Excel before migrating and
compared after)
[EDIT Further]
After working on another recent project with a lot of user data and a database which has been around for several iterations (It started as an Access 2k database) I've discovered a slightly different method.
1 - create a copy of the database called 'data'
2 - open all tables in the source copy, delete all data, save as 'linked'. Run the link to SharePoint command as per the above. Some fields may need to be renamed - the error issues reported on an attempt are much more meaningful now (rather than the extremely vague 'read only' when the wizard runs into data type issues)
3 - Once linked test the data types by adding a sample record using direct SharePoint forms. Certain fields in certain tables will be duplicated (contacts, for instance, seems to duplicate all of the default fields). The trick is to find out which fields SharePoint requires and uses by default and which the Access database reports and forms are mapped to. Similarly, create a new record using the Access forms. Note any differences in which data is placed in which fields between the input methods.
4 - modify the forms queries to use the fields used by SharePoint.
5 - open the 'data' copy. Export each table to Access (use a new name - I simply prefix the table names with 'x' to indicate the transferred data sources)
6- arrange the 'x' table layout and the linked table layout so they show the correct fields in the same order in both tables (ensure all fields are shown in the linked table) - replace the destination ID field (whichever is the primary key) with the '_oldID' field in the linked database such that the 'x' table ID field is being pasted to the '_oldID' field in the linked table.
7 - Based on relationships and/or queries in the database, find the table which contains the core record (the table which does not rely on a record in another table)
8 - copy the entire table (in datasheet view) from the 'x' table and paste into the linked table. Any errors will be reported in a new table 'Paste Errors' and more to the point any acceptable records will be copied over.
9 - create an update query in Access to lookpu the -OldID' value from the linked table with data and return the ID value in that table and update the 'x' tables which consume that relationship. Once an 'x' table has the new ID's mapped to the linked table properly, repeat step 6, 7, 8, 9 until all tables are uploaded.
10 - review the 'paste errors' for issues. Typically this will be data issues where the record is an orphan (so the record should be removed or retired), a type cast issue (the record field needs to match the data format) or a lookup. I've discovered that fields with a choice or lookup that allow user typed entries will cause errors if the value is not in the field values list so modify the values list to include the distinct values (running a simple select distinct from <insert choice column name from x table> will create said value list). Add all possible values in the column definition and paste into the table all values again until there are no errors.
11 - copy the update queries and the column values to a text file as you go through this process.
12 - blow out the site*. Open a new copy of the database, apply the choice \ lookup values to the source columns.
13 - repeat all of the above steps.
14 - once there are no errors in copy / paste, test the data for proper display and the forms and reports to ensure they are using the correct fields throughout. Some reports and forms may need to be tweaked to use the proper fields in their queries.
15 - remove the 'x' tables from the Access database. Save and distribute the Access front end.
* Note that if a given table exceeds the 5k list limit the site will not be removable via the UI. In this case use PowerShell to blow out the site.
Given this discovery, moving forward I would start with ensuring all lookup / choice fields have all possible values in use in their definition prior to step 2. Also check to ensure there is a default value 'none' or similar and that all records have a value (or 'none' if they are blank in the source data). Additionally any required fields should have a value in the source data. I used a '-' character for any required but blank text fields and now() in any date type fields.
Hope this helps!
[EDIT Further]
After working on another recent project with a lot of user data and a database which has been around for several iterations (It started as an Access 2k database) I've discovered a slightly different method.
1 - create a copy of the database called 'data'
2 - open all tables in the source copy, delete all data, save as 'linked'. Run the link to SharePoint command as per the above. Some fields may need to be renamed - the error issues reported on an attempt are much more meaningful now (rather than the extremely vague 'read only' when the wizard runs into data type issues)
3 - Once linked test the data types by adding a sample record using direct SharePoint forms. Certain fields in certain tables will be duplicated (contacts, for instance, seems to duplicate all of the default fields). The trick is to find out which fields SharePoint requires and uses by default and which the Access database reports and forms are mapped to. Similarly, create a new record using the Access forms. Note any differences in which data is placed in which fields between the input methods.
4 - modify the forms queries to use the fields used by SharePoint.
5 - open the 'data' copy. Export each table to Access (use a new name - I simply prefix the table names with 'x' to indicate the transferred data sources)
6- arrange the 'x' table layout and the linked table layout so they show the correct fields in the same order in both tables (ensure all fields are shown in the linked table) - replace the destination ID field (whichever is the primary key) with the '_oldID' field in the linked database such that the 'x' table ID field is being pasted to the '_oldID' field in the linked table.
7 - Based on relationships and/or queries in the database, find the table which contains the core record (the table which does not rely on a record in another table)
8 - copy the entire table (in datasheet view) from the 'x' table and paste into the linked table. Any errors will be reported in a new table 'Paste Errors' and more to the point any acceptable records will be copied over.
9 - create an update query in Access to lookpu the -OldID' value from the linked table with data and return the ID value in that table and update the 'x' tables which consume that relationship. Once an 'x' table has the new ID's mapped to the linked table properly, repeat step 6, 7, 8, 9 until all tables are uploaded.
10 - review the 'paste errors' for issues. Typically this will be data issues where the record is an orphan (so the record should be removed or retired), a type cast issue (the record field needs to match the data format) or a lookup. I've discovered that fields with a choice or lookup that allow user typed entries will cause errors if the value is not in the field values list so modify the values list to include the distinct values (running a simple select distinct from <insert choice column name from x table> will create said value list). Add all possible values in the column definition and paste into the table all values again until there are no errors.
11 - copy the update queries and the column values to a text file as you go through this process.
12 - blow out the site*. Open a new copy of the database, apply the choice \ lookup values to the source columns.
13 - repeat all of the above steps.
14 - once there are no errors in copy / paste, test the data for proper display and the forms and reports to ensure they are using the correct fields throughout. Some reports and forms may need to be tweaked to use the proper fields in their queries.
15 - remove the 'x' tables from the Access database. Save and distribute the Access front end.
* Note that if a given table exceeds the 5k list limit the site will not be removable via the UI. In this case use PowerShell to blow out the site.
Given this discovery, moving forward I would start with ensuring all lookup / choice fields have all possible values in use in their definition prior to step 2. Also check to ensure there is a default value 'none' or similar and that all records have a value (or 'none' if they are blank in the source data). Additionally any required fields should have a value in the source data. I used a '-' character for any required but blank text fields and now() in any date type fields.
Hope this helps!
