Creating and Linking to SharePoint Lists from MS Access | Access + SharePoint Development - Pt 1

[@marvinbrando722]

This guy is something else. Total quality and advanced

[@advantageapplications5712]

Thanks so much for the awesome positive feedback. Very much appreciated!

[@karldonaubauer6898]

Hi Rich, good video. Just a few additions: 1. Alternatively, exporting to and linking from Sharepoint can also be done in 1 step with ribbon: Database Tools - Move Data - Sharepoint 2. Alternatively, you can also create new fields for linked Sharepoint lists directly in Access, as well as change existing fields. To do this, go to the datasheet view of the table and select the "Fields" tab in the ribbon. 3. After a list design change in Sharepoint, the list does not have to be deleted and relinked in Access. It is sufficient to right-click on the table in the navigation pane and from the context menu select: More Options - Update List

[@advantageapplications5712]

Hi Karl, thanks for checking out my video and for the great tips!

[@alanhunold7546]

I’m a total beginner when it comes to access. I do have experience programming in languages like PHP and JavaScript tho, so some concepts are transferable. I have many spreadsheets that I manage and they have related data between them so access just makes sense short of building a complete web app which comes with a whole new set of problems within itself

[@advantageapplications5712]

I agree. Unless you need a web interface, I would definitely go with Access.

[@StetsonMgtSolutions]

Sharepoint is a new level for me, as I have only worked on stand alone DB’s with linked tables on an internal network. I am working sandbox solutions currently and enjoying your presentations.

[@advantageapplications5712]

Thanks for checking out my channel and for the kind feedback! I'm still getting my bearings as a video creator, so I appreciate it. If there are any topics you would like to see me cover, or take a deeper dive into, please let me know. Take care!

[@travisnull273]

I could use some of the instruction you referred to at 21:00 , regarding the row source controls. I'm loving your videos as I learn more about this so I can build a database for my fuel transport company.

[@advantageapplications5712]

Thank you my friend! I'm glad you're finding the videos useful. I will definitely make some more material on that topic. If you would like to see a specific type of example, let me know. Take care!

[@aniwenetv]

I am hooked, let me go see if this guy has a full course on Access DB from beginner to Professional (using and coding macros)

[@advantageapplications5712]

Thanks for the awesome comment, my friend. I hope to work toward having that very type of thing available before too long. Please let me know if there are any particular topics you would be interested in.

[@abdulhamidalhaddadi2255]

Thank you

[@advantageapplications5712]

Thank you for watching my video!

[@bcapp7937]

Would an all Access solution (ie. not using Lists) located in Sharepoint be a good idea for a light use one-time project? I want to provide 5-7 colleagues with data entry forms to update certain columns in what is now an Excel file with about 30,000 rows. They would be working on it at the same time. I'm new this and was thinking about having an Access back-end table for all the data and separate (identical) front-end Access Forms for each user for the data entry. Both the front and back-end would be in Sharepoint. Trying to keep it as simple as is reasonable. Appreciate your content!

[@advantageapplications5712]

Hi and thanks for watching my video and for the question! A complete Access solution can work perfectly well to support the type of use case you described (5 to 7 concurrent users) but there are a couple caveats. First, it would need to reside on a fileshare instead of SharePoint. Unlike Excel, Access cannot run from SharePoint. In the solutions I develop, the data is on SharePoint as lists and the front end (Access files) reside on each user's workstation. The second consideration is whether or not your users would be accessing the database remotely? In other words, would they be in an office, on the company's network or would they be working remotely and accessing the database over a VPN. If there is ever a chance that users will be accessing the database remotely, the chance of poor performance and file corruption increase significantly. Finally, even in a scenario where the database is on a fileshare and the users are on the company network (not working remotely), it is still a good idea to only have the back end (tables) on the fileshare and the front end (forms, reports, queries, and code) on each user's workstation. This dramatically decreases the risk of file corruption and generally improves performance. I hope this helps and if there's ever anything we can do for you, please let us know. Take care!

[@bcapp7937]

Thank you for the guidance...much appreciated!

[@Jojosmith342]

all thumbs up to another Rick's excellent videos. Already subscribed, hope the others do too. Looking forward to more. thanks a lot Rick for sharing your knowledge

[@advantageapplications5712]

Thank you, Jojo, for that awesome encouragement my friend. It means a lot!

[@stanTrX]

9:19 thanks, i like to do such lookup at the form level

[@advantageapplications5712]

So do I, definitely my preferred method. Thanks for watching my video!

[@zincfive]

Really good content, thanks!

[@advantageapplications5712]

Thanks for the positive feedback and thanks for checking it out!

[@Rhythm515]

I have excitedly subscribed to the channel, and I look forward to your content... I have a question... Why do you refer to 'Tables' as 'Lists'? I don't understand what that is all about... Thanks

[@advantageapplications5712]

Hey! Thanks so much for subscribing! Great question... so, basically when tables reside in Access (either in a single file or in a backend file) they are just that... tables. But, in most of my "real world" Access solutions I actually store the data in SharePoint lists instead of Access tables. So that is why you may hear me sometimes refer to tables as lists. I hope that answers your question. Also, if you would like to hear a little bit more about the advantages of using SharePoint lists to store data for Access solutions, check out my video here. kzbin.info/www/bejne/iXOnf4atnbKjo5Isi=9cusG0mZa9N3zAEE

[@GerryHoch]

Love the video and great explanation. Newbie question... once the tables have been re-established from Sharepoint, can you make changes and/or edit any of the data in Access? Or do the edits need to be made in Sharepoint?

[@Jojosmith342]

I have the same questions. thanks for asking

[@advantageapplications5712]

Thanks so much for that kind feedback and for your question. Once you have your linked tables established in Access (pointing to your SharePoint lists) you will be able to perform all Create, Read, Update, and Delete transactions on the data right inside the Access front end. The only thing you cannot modify from Access is the table design. So, like adding a new field to the table / SharePoint list, changing the data type of a field, increasing the number of characters a field will hold, changing the data format... that would all need to be done on the SharePoint site. I'll make a short video on this topic on Friday and give you a more solid example of what I mean.

[@advantageapplications5712]

Hi Jojo, please see my reply to Gerry's comment. I will make a video on this topic Friday to give you a better example of how this works. Thanks!

[@Jojosmith342]

Thank you so much Rick for your quick update and reply. Rick is the BEST. Love your tutorials very useful. Thanks millions for your precious time@@advantageapplications5712

[@GerryHoch]

@@advantageapplications5712 Thank you for your quick reply. I do have one more question. How do the linked tables work with images? If I were to add an image in access to one of the linked tables, would the image transfer to the Sharepoint list?

[@RayaCurtis]

Hi Rich - great video. Once I have created the database, synced to SharePoint, then reconnected an Access front end, can I connect other Access front ends that are all linked to the same SharePoint back end? Talking a total of 3 users max, would we have issues if multiple people are working in an access front end at the same time?

[@advantageapplications5712]

Hi Ray, and thanks so much for checking out my video. Yes, you can definitely link other front-end Access files to the same SharePoint lists. In fact, that's one of the best parts about having your data on SharePoint instead of Access. One of the solutions I developed, and support literally has over 200 users all linked to the same SharePoint backend. They aren't all in there at the same time, but there are always several concurrent users. I hope that answers your questions. Take care!

[@teboseema3082]

Thanks very informative. How would one upload a file in a SharePoint document library in access using a linked table to the document library

[@advantageapplications5712]

Hi and thanks for watching my video! That is a great question, but the answer isn't quite as simple as it should be. I have very recently come up against this very problem and was unable to find an easy way to accomplish it. What I wound up doing was to create a simple PowerApp that is launched from a user form in my Access front end that allows users to browse files (local, file-share, and SharePoint) and include them as attachments in a list item. I'm in the process of making a video on that subject right now and I hope to have it done by next week. Work has been very busy and it's caused me to have to slow down on my channel a bit. Stay tuned though, I will have something out on this topic in the very near future.

[@bumpersmith]

I have the same question as @GerryHoch concerning photos. I have an Access application about genealogy that includes many photo and document attachments about individuals and families. How would this type of situation be handled with SharePoint lists as far as storing the photos and documents?

[@advantageapplications5712]

Hi BumberSmith, and thanks for checking out my video. I will be covering that very topic, either in this series, or in the near future. The good news is that it can be done and I currently store images in SharePoint lists for the "bolt-on" PowerApp I quickly show near the end of this video. I'll take you through that same process in my upcoming video. Take care, my friend!

[@aaronboydston3172]

When I export my access table to SharePoint, SP has a number column named "_OldID". Is that something to be concerned about?

[@advantageapplications5712]

No, I get those in some of my exports as well. As long as your primary key field is auto-incrementing as it should (if it was an auto-number field when it was in Access, of course) and the other fields you specified in your table design in Access are correct then you should be good to go.

[@SchoonerMonaro]

11:30 Wow, this explains my current problem. I'm migrating the tables from an existing database to Sharepoint and have noted a number of data errors in the result. I tried removing relationships before migrating, but they still occurred. Thank you for pointing out that Sharepoint is renumbering the key - it is obvious now I know it. I'm looking forward to your video on the process and tools you have developed for migrating an existing database. Very useful set of videos. Access still has a lot of advantages and I really like the combination of Access and Sharepoint Lists. Thank you from Australia!

[@advantageapplications5712]

Thank you for the very kind feedback! I hope to put that video out by the end of May. Work has really ramped up with a couple new projects I've started but I am still plugging along in making video content, just slowly. hahaha Take care, my friend!