I share experiences and practical insights gained during more than 25 years of experience creating custom business applications for some of the largest chemical companies in the world.
We'll explore how to solve complex problems with easily accessible tools like Microsoft's Power Platform, MS Office with VBA, and SharePoint.
I look forward to having you join me in creating useful tools or making existing tools even better.
After creating the Access front end with tables linked to Sharepoint, can I simply copy that front end file onto other user's workstations in the company? Do users need to be logged into their 365 and would I have to setup shared permissions on the sharepoint URL? In other words, anyone can't just open this front end file on any computer and expect it to work, correct? Thank you for your help!
Hello! Yes, once the linked tables are established, the front-end file can be distributed to your users and, as long as they are logged into Office 365 and their accounts have permission to access the SharePoint site where the lists reside, they will be able to use the front-end file without issue. Thanks for watching my video!
I am retired now but the last 25 or so years of my career were built on my Microsoft Access skills. I loved working with it but it worked best for a small department or office. My last employer was a large company so Access was not working for them. I understood that but I continued to use it for smaller needs.
Hi Rich from Advantage Applications. This is Mark. I have an access database that I created last year for my nonprofit organization. I am trying to find someway to auto update my access front end to about 5 computers. My network has a fileserver that is our main computer that all our documents are in. That is where my split back end and front end is. I use my laptop to split it save it in the fileserver. how much would you charge to help me do an auto update our front end. On my end i can delete the front end in the fileserver and create the new one. Then when people open the front end on their computers it will check to see if there is an update and a pop up will come up and say there is an update do you want to update? I hope to hear back from you and discuss this matter. Thanks.
Hello Mark, and thanks for reaching out! I would be more than happy to help out with getting your front-end file to self-install for your users. If you would like to send me a copy of the front-end and the backend, I could build in the auto-update feature then send it back to you with configuration instructions. I could do that for say, $100 even, if that works for you. Just let me know what your thoughts are. Take care my friend!
I really wish Microsoft would bring back .adp, access data projects. I developed an app that served over 100 users, geographically dispersed over 5 states from east to west coast. The app served users for over 8 years, was fast and fun to maintain.
@@advantageapplications5712 Ok, I agree but shouldn't it be late binding when done so when distributing the DB there will be no worries if the user is setup for early binding?
Thank you for the video! I have database app with 25k rows of data. If I am not mistaken sharepoint list can only take 5k rows of data. Is SQL server my only option? Or I have other option similar to sharepoint. Thanks!
Hey! Thanks for watching my video and for the question. Actually, SharePoint lists can hold up to 30 million list items (records, in our case). The 5k limitation is only for what SharePoint can display in list views. And since our "views" are in Access, we aren't affected by it at all. I have one database, in particular, that has two tables with over 75,000 records each. Sometimes, users will run reports that return more than 5K records and it handles it with no issues. So, basically, as long as you are using SharePoint as the data storage and Access as your front end, you will not be hindered by the 5k view limit in SharePoint. Best of luck!
I'm learning how to use Access and the impression i get is it's perfect for small-medium teams of people to keep track of small-medium amounts of information. At work my bosses like to make a quick and dirty ad-hoc excel sheet with very little data validation to keep track of things and it ends up being a monstrosity with very dirty, unusable data. So my plan is to create a split Access database with a main switchboard containing buttons that take you to a form for each thing we keep track of. All i would need to do is add a table and a button/form/relationship any time we start keeping track of something new. The strong data validation in Access combined with the querying and reporting means we can actually use the data we record, and the forms make it so less tech-savvy team members can contribute with clean, usable data. Access wouldn't be good enough for an entire enterprise to use but it's great for teams of 15-20 people to keep track of data.
This is just what I needed. Well thought out video presentation. The right mix of typing out code and using code that you've already typed, as well as including trial and error.
Taking query filter conditions from a Form can be done by simply putting form object names in the criteria in query design view. So why this VBA approach? Any specific advantage?
Great question, and you are correct... if we just wanted to run the query and pass it parameters from the form, we could reference the form's controls right in the query. But, for this use case... needing to open an Excel workbook that pulls data from the query that it is linked to, we need to change the query definition itself, otherwise the Excel data doesn't reflect what the user specified in the parameters form. I hope that makes sense and thanks for checking out my video!!
Hi thanks for the videos, appreciate it. I’m wondering when you move the tables to SharePoint and use Access as front end, does the Application convert into a Client Server environment? Meaning does SharePoint imports the tables into a SQL Server in the background. Reason I’m asking this question is because I want to know if say 30-50 users can use the app vs 5-10 Access can normally do (assuming db is split is done correctly). Thanks
Excellent question, and the answer is yes... which greatly improves the usability of your Access application and is one of the primary reasons for building (or migrating) solutions so that the data resides on SharePoint. I have an event notification system in place with a large chemical company and it supports upwards of 20 concurrent users at any given time.
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.
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?
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!
I stumbled apon this guide today and man you are a superstar. Im in a unique work situation where Im the IT Admin, Support and long term goal is to tke over from the production manager in chemical manufcturer factory. I have had sql training(intermedite levell) long time go in collegue and recently started self learning access. This project you are guiding us on is very valuable, not as a direct copy, but shows the potential with the tools in 365! Please keep this series alive, Ill be happy to donate in the future when I can.
Thanks so much for the kind encouragement, my friend! It's been a little slow going lately due to work obligations but more videos are certainly on their way. I just posted a pretty big one on migrating existing Access DBs to SharePoint and preserving the identity values in primary key fields. I hope it's useful. Take care!
Great video and congrats on the video doing so well. I look at MS Access as I do any tool. There a certain reasons I would use a jigsaw and certain reasons I might use a miter saw. Neither is necessarily better than the other (it depends on the project) and neither are going anywhere soon. Anyway, great video!
Great project.....trying to use outlook on my computer and not finding a method to use what you have here. Can you elaborate further? update: As an experiment, I changed all the customer email addresses to one I use for testing and then fired the routine and I get the "all done", but no emails were sent, and the immediate window does not show what was done.
Hey! That's a great idea, I think I'll make a video very soon on how to send emails through Outlook via MS Access. It's interesting that your immediate window isn't showing the emails in it. I believe you are a channel member on Buy Me a Coffee, correct? If so, have you downloaded the source file I used in this video to confirm that it works on your computer and to compare your code with it?
@@advantageapplications5712 Yes, I am a channel member, and I did use your code. I you do make a video on using outlook, that would be great. It is a daunting process currently. I am assuming that the current code will not use outlook as it stands. I am getting an "Error in Loading DLL" message when selecting Microsoft DAO 3.6object library
Until you start benefiting from MS Access as a developer to government and private and public institution, you may never appreciate the power of that small database application. More interesting is if you meet a good Access developer like me and the owner of this tutorial. I have used MS Access in collaboration with SQL Server and SharePoint to develop wonderful full fledge and interesting applications for government, banks and private sector driven organization. And even after years, they still appreciate the whole thing.
Great stuff, and very interested in building on my access knowledge base, and I really like your approach and presentation. I just “bought a cup of coffee” as I see you becoming a great resource in my learning quest. I am hoping you will be adding a a forum style option to allow subscribers/members to really delve into various topics with you and each other. For the record, I am an access enthusiast with limited experience and at 72 years old this is all about using my experience to help small startup businesses.
Wow, thanks for becoming a member... it means a lot! And kudos to you, sir, for using Access to assist in small business startups. I'm always amazed (and have been for years) at how quickly most consultants dismiss Access as a viable solution platform, especially for smaller businesses, who have to watch their tech investments very closely. I have been able to do a lot with it for companies of all sizes and I believe it still has a lot to offer. I really like your idea about a forum where members and I can discuss topics. Maybe a bi-monthly type event where we can get into a certain topic deeply and then set the topic for the next discussion. I'll think more about it. Thank you.
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.
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!
This is exactly what I have been trying to find for months now. How to set up using Sharepoint for backend. Thank you for this amazing breakdown of how to achieve this.
Hi Gregory, thanks for watching my video and thanks for the question! To kick users from a database where there is a front end and a back end, place the table "tblShutdown" on the back end and link to it from the front end. Everything else will be set up exactly as it is in the video, with the form that runs in the background residing on the front end of each database linked to your backend. When the shutdown flag is set to TRUE in tblShutdown, each front end running the form that checks for shutdown status will shutdown. When that happens the backend file will become editable again as well because there will be no users connected to it. I hope that answers your question.
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.
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!
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.
Not a good idea. You create a new field which is not related to data but to your code. I do the next way: I get max id value after run SQL. If id is AutoNumber this always give me an id of newly created record
Hi, thanks for watching my video! Getting the Max ID after a SQL Insert statement is a good idea unless you have a large, active user base. In that case, it is possible that another user (or process) could create a newer record than the one you get the ID of using Max ID. The risk may be slim but it isn't zero and increases with the number of users and process that can create records in the table in question. This is why I use an "earmark" value comprised of the user's login ID and the current data/time down to a mili-second. This chance of that value being duplicated is relatively nil. When I query that record to get the ID I am sure to get the ID of the record I want. Take care my friend!
Interested to know your thoughts of linked tables in sharepoint vs dataverse for speed and stability. I've coded MS Access databases for over 25 years and still find them to be so powerful for a data front end in many scenarios and a data backend for small/micro businesses.
I couldn't agree more, I believe that is exactly where Access really shines in the modern work environment. As for tables linked to dataverse, I have to be honest, I have zero first-hand experience. All of the projects I have worked on, and currently support, store data in either SQL Server, SharePoint, or Oracle. But, my assumption would be that it would perform similarly to linking to data in SharePoint lists. I'm going to make a note of this topic though, and I'll do some research and make a video on it. I'm definitely interested. Thanks for checking out my channel, by the way!
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!
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!
First used it after Microsoft bought it and put out version 1.1. It got much better with version 2.0 when they incorporated some key functions (speed optimizations) from FoxBase, they they also purchased. FoxBase was later named FoxPro. Both programs were very fast and easy to use. Back around that time Borland Paradox and Clarion were also quite popular. I used Access many times at my last corporate job. Also spent a good bit of time in Power BI. Thanks for sharing.
@advantageapplications5712 great content, thanks. After watching your videos I moved our backend to Sharepoint, not really big issues there, however now our access application is running super slow. All forms take mins to load and display. Curious thing is that if you go and actually type data on the tables it works as fast as a native table. Have you seen this issue with your applications working with SPO?
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.
@@advantageapplications5712 There are a couple of (small) limitations with Execute (but also doesn't require SetWarnings), so maybe that was what caused the errors? For example, Execute cannot process references to forms and reports within its query string, they need to be included outside the query string. Also, if Execute is changing the data in the form record that is already changed, the database will show a message that another user has modified the record. The "other user" is the database engine. In that case just set the Dirty flag to false before the Execute to prevent this message.
Without consideration to anything else, like: cost to own and maintain? Data storage fees? Compute fees? What stack will the app reside on and what are the trade offs for choosing it? How, where, and by whom will the app be used? How long will it take and how much will it cost to add new functionality to the app if business processes change? Simply hiring developers to build a "real" app is a fashion statement and often over extends company budget and makes them beholden to the developer's availability and pricing instead of the client having ownership of their tool. In my experience, if you can demo it in Access, then it is intended to be used in a PC environment and therefore is absolutely worth considering building in Access with a SQL Server or SharePoint back end. And I've helped many companies get out of the bad relationships with "real apps". ;)
Hmmm... good question... it may be possible but I kind of doubt it since it would defeat the purpose of the Trust Center. I doubt there would be a way to have a file make itself trusted. But I could be wrong. If you crack that one, let me know.
Nice approach! I built a front end/back end split solution about 12 years ago that is being used by about 100 people in 50 difference offices. This may have come in handy back in the day, but my concern has always been more with the user moving the backend database! I eventually built a version control system that uses a SQL server to check versions and then prompt the user to download new front ends. If I ever get the time to invest in my old dinosaur, I might borrow your idea to ensure that the front ends are placed in the correct location. This can even lead to an auto-updating application that will download itself and install in the correct folders.
Thanks for checking out my video! Yes, those back end files on file shares were always dodgy to manage... corruption, tampering, moving... they were nightmares. lol Every new database project I take on now, the data either resides on SharePoint as lists or on SQL Server. Take care!
Rich, when working with DAO as in your first example, there is an easy way to get the Id of a newly inserted record in Sharepoint, using the LastModified property after the update: rst.Addnew ... rst.Update rst.Bookmark = rst.LastModified newEventID = rst!EventDetailsID
I never any experience with FoxPro, myself. Everyone that I've talked to who has used it loves it though. They must have gotten something right to have that kind of loyalty. Thanks for watching!
No one can deny the power of MS Access especially when used a long with MS Excel for data exchange and analysis, despite being sabotaged since Web Apps were sadly deprecated by Microsoft.
That is definitely one of the most frustrating things about Microsoft... they will completely turn their backs on existing tech that people love, use, and depend on.
Actually, SharePoint lists CAN be designed to support the functional equivalent of Referential Integrity between tables. They do so via the Lookup Field method. If you use Lookup fields in the local Access tables prior to exporting them to SharePoint, they are preserved. Or, if you design the SharePoint list with the appropriate Lookup Fields, Access recognizes and maintains those relationships.
Greetings from Canada George! Funny to find you here??? Actually, if you use the look up wizard and go back to the relationships window? You will see the same standard relationships. (so, relationships created with the lookup wizard shows in the regular relationships windows). And in fact the reverse is also true (you can use the lookup wizard against existing relationships to modify them - they both do the same thing). So, you are free to use relationships window to setup a relationship, ,or use the lookup wizard - both work seamless and result in quite much the same thing and result. Either way, these created relationships migrate to SharePoint with relationships intact. Unfortunately, my now 13 year old video used a web database for the sample migration, and thus I had to use the lookup wizard. I think it perhaps time I add a new YT video showing how this works without any references to "web stuff" So as a FYI? So, there is no requirement to use the lookup wizard - you can use the standard relationships window, and those relations will migrate up to SharePoint just the same with the relations working and intact on the SharePoint site, or even the low cost 365 account. So, even today, the low cost bottom feeder office 365 account (the one without including office or Access subscription for about $12 per month) still supports related Access tables. There is no requirement to use the lookup wizards for this - the standard relationships window can be used.
Ya know, I have heard that but I have never had any luck at all with it. In fact, a strange thing seems to happen where either the table can't be written to or a duplicate table is created alongside the "real" table with an underscore in its name. Have you experienced this yourself?
@advantageapplications5712 I've not seen that behavior. In fact you don't even have to use the relationship and lookup wizard, but can use the regular relationship window for this. The only special requirement is the pk has to be a auto number, and the foreign key is long number. So pk and fk can't be natural ones such as text. So unlike access, tables sent to SharePoint can only use a auto number set for such RI to work. And cascade delete is all you get. During up sizing all auto numbers are changed, so relationships must be set correctly before sending to SharePoint else relationships are lost due to auto numbers changing during the transfer. With relationships setup before transfer then access knows to update all child fk values to new pk
