AP: Access + SharePoint, NOT REALLY about SharePoint, by Albert Kallal
HTML-код
- Опубликовано: 6 июн 2021
- Albert Kallal, former Microsoft MVP, will share his experience using Access Front End accdbs with SharePoint Lists. Albert will tell us "what is great, what to watch out for, and how this solution is easy, and great option for Access users." Albert’s knowledge of SharePoint is unmatched in our community. This is good.
Access + SharePoint, NOT REALLY about SharePoint
by Albert Kallal ( kallal.ca )
3 June 2021
0:54 About Albert Kallal
2:25 history, SharePoint with Access started 18 years ago.
4:43 repackaged SharePoint, put out as Office 365, made it cheap.
4:56 What's the catch?
6:33 really improved Access 2007 because SharePoint was getting popular -- use Access anywhere
7:23 In 2010, there was a quantum leap. Added relational database capabilities to the SharePoint system. Performance increased about 10x.
8:19 Data size limits.
9:03 When you start the application, tables should have less than 5,000 rows, using low-cost cloud edition. Can go up, but it will be painful to get it working.
10:21 In most cases, there are no changes to your application, but data structures have to conform to what the relational limitations are.
10:38 Relationships need a standard PK (primary key) AutoNumber with long integer FK (Foreign Key).
11:08 What's different with relationship between Customers and Invoices?
12:22 Suggest Left Join for documentation, and also matters when moving tables up.
12:45 Table setup and limits. Put all data in tables. Use AutoNumber PK. Put all tables on relationships diagram.
14:21 Go over to a typical Access application.
15:02 Microsoft 365 account types
15:39 20 multiple users in the cloud for CAD 11.00, or about 8.50 USD/month.
16:01 typical Access application with front-end and back-end. Using Access 2010 -- non-365 version -- show that you don't have to use subscription versions of desktop Access. And you can use Access Runtime for laptops on the road.
17:54 open back-end database
18:12 Relationships window in Access. Customers, Invoices, InvoiceDetail, tables for drop-downs.
20:07 SharePoint makes new AutoNumber values when you push the tables up.
21:48 Users are invited to SharePoint using any email address. Guest users don't need a 365 account.
22:10 All for 8.50/month, you have multi-user, on-the-road, robust, shared in the cloud, for next to nothing.
23:01 Go to 365 Account, and your team site.
23:52 Create a New site for this application. Make a subsite. Remember the name. This example is "aupug5"
24:50 Done in SharePoint. The rest occurs in Access.
25:01 login to the 365 account, don't need to buy a subscription.
25:20 We have our tables in Access -- going to push up to SharePoint, then change linked tables in front-end. So let's do that.
27:30 This setup will run circles around SQL Server in terms of performance with less than 10,000 records in each table.
27:57 uh-oh, skipped a step. Still going.
28:17 Reason why Access with SharePoint works so good is because data access stores the data local, and synchronizes with the cloud, and may get better performance than on your local network. But for larger datasets, there are problems.
29:36 When you update a row, it has to synchronize with the cloud.
29:58 Applications that update a lot of rows run slow.
30:58 Once tables are pushed up and you hover over a table, you see its location on SharePoint. They're quick to open.
32:07 When it upsizes, automatically makes a copy of the original back-end.
32:20 Go into front-end and hook up to SharePoint.
38:47 Here we are with our SharePoint tables in the Access application. The VBA code and everything else we have should work.
39:33 The biggest challenge is to ensure you follow the relationship rules.
39:50 Questions
40:51 Use single table for different lookups?
45:59 Get indexes right in Access before you push them up. You can add field indexes after tables are in SharePoint, and even more fields. Can't use Table Design view, but you can use Layout.
50:00 You can work disconnected. Behind the scenes, it's web-service based, which means its like email. You can be using the application, unplug the network, and keep on working.
51:06 When you get re-connected to network, it will start synching again.
52:59 In Access, right-click a table and choose Convert to Local.
56:02 SharePoint works with any version from 2010 up.
57:11 Limit is about 65 columns for low-cost accounts.
57:36 If business has onsite SharePoint, can get row limits turned off.
1:00:41 next month, July 1, David Nealey will present on how he uses graphics. Join us!
AccessUserGroups.org
Access Pacific hosted by George Hepworth
accessusergroups.org/pacific/...
First Thursday at 6:30 pm Pacific time PT -- join us!
SUBSCRIBE for MORE videos! ► bit.ly/AUGRUclips
✓ Join our online Access User Groups with free membership.
► Facebook ◄
/ accessusergroups
► Twitter ◄
/ accessusergroup 
Наука
This is excellent. When our office moved to SharePoint & Teams, Access became obsolete because I could not figure out how to setup the backend in SharePoint. THANK YOU!!!!
Thanks for the detailed session, nicely explained - Could you pls advice if it is possible to edit design view of table after linking the DB to share point. If yes pls advice how to do it.
Yes, you can edit the design of your SharePoint lists both in SharePoint and in Access, albeit differently from local Access tables.
Obviously, you can navigate to the SharePoint site, find the lists, and modify them there.
To edit linked SharePoint tables in Access, you can open them in datasheet view and select "Table Fields" or "Table" from the ribbon. There are additional tools there to add, modify or delete fields, and so on. Note that there are restrictions on this capability. If you have aliased a list in the Access accdb (i.e. the list is called "Contacts" and you linked to it and aliased it as "MyContacts" locally in Access) then it is not editable in Access. You'll have to do it in SharePoint and then refresh the link in Access.
Thank you this has worked for me so far. However, we import a .CSV from another data source for one of backend tables quite regularly, how would we do this on Sharepoint?
What have you tried so far? What was the result? What problems did you encounter? If you are simply importing data from a .csv into a table in Access, whether it is a local Access table or a Linked SharePoint list, the process should be the same.
@@GroverParkGeorge Hi, so I used to have a saved import that would just look for the file in the designated folder and then replace the previous version of the table, let's call it 'App Data'. However, when I try to do this with the Sharepoint linked Table it won't allow me to.
@@benbennett354 Ah, that will not work, for sure. Sort of like trying to mix apples and oranges. I would link to the .csv and use and append or update query or queries to move the data into the linked SharePoint list, then delete the link to the csv.
@@GroverParkGeorge Thank you George, that's good to know. I'll try something along those lines. Do you think there may be a way of using Power Automate? by instead using a .xlsx export and then converting that to a Sharepoint List? I'm only thinking on my feet so far, but it seems like it could be possible.
Anyway, thanks for your reply and sorry for my initial vague (and rather panicked) post.
I managed to solve this using Power Automate, it's actually easier and faster than it was before (after a bit of a involved set up) I would recommend. Thanks!
So how do I loop very large recordset in SharePoint if it is very slow.
Looping recordsets is the same regardless of the backend (Access, SharePoint, SQL Server, etc.). Performance is dependent on a number of factors, including the size of the recordsource (1,000 records or 50,000 records) and the connection to that data (local or remote). Even the way the looping code is written matters.
So, the answer that question has to be, "It depends" on the specifics of the situation. Whether or not a particular looping procedure is, in fact, slow, has to be determined on a case by case basis. The approach then depends on what the situation is and what the requirement is.
If you can provide a more specific description of a particular problem, perhaps a more specific answer can be given.
@@AccessUserGroups Thanks for the quick reply. I think I might split my table into newer and older tables. For the older tables I might perform earlier looping and store the result which could be incorporated into the newer dataset. This is assuming the looping or data-analysis is the same. Just a thought.
Hey guys, i have a problem with ms access, this the detail of my problem .
1. I have accdb 2010 file, and then iam tried to make ehis database online, so i tried split database.
2. The file_be, iam tried connect to sharepoint, and its success
3. The file_fe, iam delete the existing table, and get external data from sharepoint, and its success too
4. Iam tried the application, its success, database on the sharepoint was update too, until this, i have no problem, but the problem is
5. I was copied File_fe to another computer, office 2010, and office 2013, and then when iam running the app, the application directly force close without information or warning, i have tried on 4 pc, and the problem was same, can you help me guys for my problem ?
It's not clear what the problem actually is. You copy the Front End accdb to a different computer. When you open that Front End accdb from that computer, Access immediately closes, with no error message?
There can be a few different causes.
First, permissions to use SharePoint. Obviously, if the users involved have correct permissions to the SharePoint site, then that should not be a problem. However, it may be the different computers don't sign on with the appropriate credentials, which can't connect.
Second, there may be a difference in the bitness of the versions of Access, 32 or 64. You need to verify that for each computer.
What you can do, to try to deal with the problem, is open the FE accdb on the other computers while pressing and holding down the Shift Key to bypass any start up code. Then, compile the code in the accdb to ensure that it compiles without problems. Then, relink the SharePoint lists in that FE accdb, selecting the proper SharePoint site and lists. If that can't be done, then you will know that there is a problem reaching the SharePoint site from that other computer.
Unfortunately, problems like this are inherent in the environment you have and can't be diagnosed without direct access to that environment, which leaves the ball in your court entirely.
Sharepoint re-index the tables ID's quite often, would that mean that Sharepoint in the background uses the _OID of the migrated ms access database to maintain referential integrity?
Re-indexing does not work like that. The values of the Primary Key fields are not changed by indexing them. Indexes are separate things and they maintained to assist searching and filtering and sorting.
So I can convert a back end to SharePoint and my defined primary keys and foreign keys will be preserved?
If i delete records, the primary keys will remain the same? What is the difference between the _oid and the list primary key?
@@anzus101 Yes, values are not changed in other records when a record is deleted. This is very much expected behavior in all databases.
Thank you. I know what is indexing in the DB world. Some GIS applications used the word indexing to refer to the recalculation of the shape file id. People drag that concept into the IT space