Link Microsoft Forms Excel to Access Database

Question

I'll admit I'm an Access/365 novice at best.

We use MS Forms for registrations for conferences and events (virtual events as of late, thanks COVID). What I ultimately want to do is link the info from the Excel sheet that populates into an Access database for better management of the data in real time. Is this possible? Any help or ideas are welcome! Screenshots would be marvelous as I only have a very basic idea on how all the programs are organized in 365 and all that.

I hope my question makes sense!

Thanks!

Answer 1

Dear Catie,

Thanks for your updates and information.

The Open in Excel option in Microsoft Forms is just to download the Excel file which means that it can't sync data between Forms and Excel. To sync the data between Forms and Excel, you may upload the Excel in SharePoint manually and use Power Automate to sync the data between Excel and Microsoft Forms.

As Microsoft Forms and Access can't be synced directly, you may use Excel as a transformation place, I suggest you sync the Excel file with the OneDrive sync client first. Then open the Access file>External Data>Linked Table Manager>Add>Excel>select the Excel file in the local OneDrive sync folder and then you can use Relink and reopen the Access file to see if the data is updated. I'm afraid that this way can't be updated automatically in Access and you may need to update data by a manual action and the sync may take long time.

So I think that Excel may be not a good transformation place. I suggest you use SharePoint Online list. Then you can transfer the data from Microsoft Forms to SharePoint Online list via Power Automate, click on External Data in Access>New Data Source>From Online Services>SharePoint List>Link to the data source by creating a linked table and then you can submit the response in Teams and the data can be transferred to SharePoint Online list by Power Automate automatically and you can just reopen the Access file to see the updated data. In this way, the sync is quick by just closing the Access file.

Best Regards,

Cliff

Answer 2

Dear Catie,

From your description, it seems that you create an Excel sheet with the Forms for Excel template in the OneDrive for Business site or SharePoint Online sites and want to sync the data between Excel and Access.

If yes, you can first sync the Excel file with the OneDrive sync client, open the Access file>External Data>Linked Table Manager>Add>Excel>select the Excel file in the local OneDrive sync folder and then you can use Refresh and reopen the Access file to see if the data is updated.

If something is misunderstood, welcome to post back with the updates for us to confirm the requirement.

Best Regards,

Cliff

Answer 3

Hi Cliff,

The Excel sheet I use is the one populated from our Microsoft Form when we're checking responses and click "Open in Excel". The Form was created using a Forms tab in Teams, so it's connected to a Team. The Excel sheet looks like it's on the SharePoint for the Team. I'm ultimately hoping for a way when someone inputs new data on our Microsoft Form that an Access database will be updated. So I suppose it doesn't have to use the Excel sheet populated from Forms, that's just the way I'm most familiar with, but I'm open to learning (and teaching the other secretaries when I hopefully find a good way to do this).

I hope this cleared some things up and didn't make them more confusing!

Thanks!

Answer 4

Dear Catie,

Welcome to share any updates when you have time if you need further help on this issue.

Best Regards,

Cliff