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 d...
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.- Yes. Yes, they did.
It seems that, of late, Microsoft has been ignoring MS Access. I can't understand why MS would develop an application like Forms without creating the functionality that would allow for easy interface with an MS Access database app.
For the record, I am not an Access developer. I am a lawyer who lacks proficiency in any programming language but understands the concept of relational databases. Over the past twenty years, I have had numerous needs to create applications to collect and process data in a manner that integrated with daily tasks in order to avoid the creation of additional work that would ultimately get ignored when things got busy. To your point, my "amateur" applications, originally intended to be an interim solution, became the permanent solution after I left the project because no other colleagues were capable of communicating their detailed needs in a format that professional Access developers could understand.)
I have tried to learn other relational database systems, but MS Access has always been the easiest to use especially when the app demands complex functionality. In my experience, MS Access allows one to play the piano and perform complex pieces without first having to learn to read music. I hope that, in the future, MS becomes more aware of the importance of Access as a tool for small businesses, organizations, and projects who may not have the resources to hire a professional developer.
Elhanxyz The Excel files. Are they created daily and downloaded? Where are they stored, physically?
"...MS Forms hosts the Excel output on the website link to the Form..."
Unfortunately, I'm not sure I understand what that involves. What is that "website link"?
Are you getting .xlsx or .csv files?
In theory, at least, if these files are stored where you can import them (or link to them) from your Access accdb on a local computer, the import process can be automated. The key is being able to connect to the location where they are stored. It sounds like you are doing that now, in fact.
Using VBA, if you can get to these Excel files, you can automate the process of importing the data from them. Two factors make that easier or harder. One is naming conventions. If each Excel file has a new, unique name and is created daily, then the task is harder. The other factor is whether you can connect to the location of the files directly. It sounds as if this part is true, so it's down, I think, to being able to identify the file(s) you need automatically.
- It's a .xlsx file, which MS Forms offers as an option on its Responses tab. It both allows you to hit the "View Results" tab to see the responses as individual records while on the same webpage, or to "Open in Excel", which will download them as a .xlsx attachment. So I suppose my question is whether Access is capable of importing those results directly from that results webpage, saving the process of downloading the file, or whether I'd be better off just automating the import process while storing the file locally.
The frequency it's updated with is ad hoc - it's whenever the Form receives a new response - we check it twice weekly.
