Forum Discussion
Linking Microsoft Forms to Microsoft Access
At the moment, I am working on a project receiving responses via Microsoft Forms. This stores these in an Excel doc accessible through the website link to the form. Is there a way to connect Access directly to this, or would some further work be required? Tried searching for this online but haven't been able to find much that is of help.
Well, the answer is neither a simple "yes", nor a simple "no".
Let's start with the basic concepts of a relational database application to see why.
Relational database applications consist of three core components.
- Data Storage Layer. This is the location in which all data for the application is stored. In Access, this is the tables in the built-in ACE database engine. You can also use other data storage locations, such as SQL Server, SharePoint Lists, and even Excel worksheets, although these are probably best understood as workarounds for short term purposes rather than long term storage.
- Interface Layer. This is the collections of forms and reports through which users interact with the data. In Access, this would be forms bound to the tables. You might use Microsoft Forms in a browser for the same purpose, and even connect them to the same data. In this way, and ONLY in this way, can one logically refer to using Microsoft Forms and Access Forms, i.e. to work with the same data in a source accessible to the desktop and to the browser or wherever you're running your MS Forms.
- Logic Layer. This is the code used to manage both the interface and the data. In Access (and in Excel) this is VBA. Access also has its own built-in macro language, a much simplified language suited for simple tasks. Note that Excel uses macro and VBA interchangeably, which is not appropriate in Access.
So, your task is to put the data into a data source to which both your MS Forms and your MS Access forms can link. That can be Excel, in a very modest application. I would prefer NOT to risk it, but if all you need to do is query that data, it can be a reasonable work around.
That probably leaves you with additional questions, so follow up if you have them.
- Thank you for your response. To clarify, it will be a very modest application, as this is just an interim solution until a more permanent one is brought in, and will not be seeing very great case volumes.
Currently the process is set up to download the Excel file with the responses from MS Forms, and then import this into Access. My understanding is you can create a linked Excel spreadsheet with a file stored on a LAN, but as MS Forms hosts the Excel output on the website link to the Form, is there any way you can directly specify this online output it creates as the source of the data, or do you have to download it to a hard drive?"To clarify, it will be a very modest application, as this is just an interim solution until a more permanent one is brought in..."
Lots and lots of Access relational database applications started out with that same profile and lived to very ripe old ages as the "permanent" solution remained elusive. Ask any Access developer who's been around very long at all.
