Say Goodbye to Access Web Apps
As the retirement of Access Web Apps (AWA) nears, now is the perfect time to switch to using PowerApps. AWA offers a simple export method for converting your data to SharePoint lists, and following the few steps below, you’ll be able to get a PowerApp running on that data in minutes.
Export Your Data from Access
AWA Export to SharePoint
As mentioned above, AWA has provided a convenient method for exporting your data. If you open up your Access Database, in the top right click the “cog” for settings > Export to SharePoint Lists.
This process will go through provisioning a subsite (named the same as the Access Database) and provides a status list to track the progress.
As for a general gauge of time, it varies on the size. I had a client that had three tables, about 7.4k records in one table and the overall export took about two hours.
Export Status List showing Progress
Data Cleanup
Depending on how you setup the Database originally, most likely you’ll need to do some data cleanup / re-design. In order to give PowerApps a fighting change to query your data well, It’s best to make sure that any fields you are searching / filtering by are single line of text fields. Now there are a few ways you can do this especially with PowerShell, but I just took the easy route and changed the few multi-line fields to single-line and didn’t have any data loss.
What to do with the List View Threshold
Of course once the data was migrated over, the lovely list view threshold showed it’s face. Microsoft discussed at Ignite this past year how predictive indexing was being implemented and I have been impressed with the results from the UI. However this is only a soft fix and does not actually raise the list view threshold, rather just created index columns for you based on usage (Joann Klein provides great detail in her blog post). With the use of indexed columns, PowerApps was able to work with all 7.4k items that were migrated, but it was kind of slow. It still handled basic CRUD operations, but we ended up archiving some of the old data anyways.
NOTE: As of this post, the out-of-the-box remove() function is still limited and does not work through PowerApps or Flow. I posted in the Flow Forum and am still waiting to hear back.
Even though the app worked with 7.4k records, I wanted to get that to a more manageable state, so I created a PowerShell script to archive old items to a separate list. This is a topic for another article but if you are not familiar with the PnP PowerShell Cmdlets, I highly recommend you look into it. They will make your life a lot easier working with SharePoint / anything. You can check out how to get started here: https://github.com/SharePoint/PnP-PowerShell.
Spin up the PowerApp
Once the data is in SharePoint, it couldn’t be simpler to create a PowerApp. You have a few different options to choose from for creating the app:
- List Form Using PowerApps – If using the Modern Experience, you can customize the list form right from the toolbar (Customizing SharePoint list form with PowerApps)
- Provision your App from Data – From the PowerApps dashboard, you can create an app from whatever data source. This is a great way to get your hands on a template to work off of, and can help you understand how certain functionality works in PowerApps. (NOTE: this only offers a phone layout)
- Start From Scratch – Some people might be hesitant to start from scratch but I typically prefer it. Especially if I am trying to create a tablet layout you pretty much have to go this route.
AWA Views vs. PowerApps Search
Search Box and dropdown change the search parameters
One final thing I want to discuss is the difference in how you view your data in AWA compared to PowerApps. PowerApps provides methods (i.e. SortByColumns, Filter, Lookup, etc.) to pull data, and is much more robust then the views that AWA has to offer. My client was using views in AWA that would search against all columns, and created a very poor search experience. With PowerApps, search can be dynamically configured to search against specific fields. It also has the ability to be presented however you like (see the screenshot to the right).
To achieve this search by property functionality, I created a drop-down with select fields and a search bar.
The Dropdown in the provided screenshot shows three options to search and filter by. A gallery will normally update as the search box is changed. However, it can get hung up and stuck if it is updated to frequently. In order to reduce the amount of requests made, I have the results stored in a context variable called searchResults. This has the benefit of making the request only when I press the search button.
Search Icon OnSelect() Property:
UpdateContext({searchResults:
If(searchFilter.Selected.Value="Client Ref",
Filter(SortByColumns('Matter-Invoice_',"Filed_",Descending),StartsWith(Client_Ref_,searchBox.Text)),
If(searchFilter.Selected.Value="Matter Name",
Filter(SortByColumns('Matter-Invoice_',"Filed_",Descending),StartsWith(Matter_name_,searchBox.Text)),
If(searchFilter.Selected.Value="Invoice No",
Filter(SortByColumns('Matter-Invoice_',"Filed_",Descending),Invoice_No_=Value(searchBox.Text)),
SortByColumns('Matter-Invoice_',"Filed_",Descending))))
})
In the above code, The context variable called searchResults has some conditional logic to either filter and sort either by Client Ref, Matter Name, Invoice No, and will just default to a sorted data set otherwise (just in-case). Because the Client Ref and Matter Name fields are single line, we are able to use the StartsWith() function. Using this approach, we were able to create a much cleaner search experience.
NOTE: You will need to review the field types being used and design the app accordingly. Review the PowerApps article about delegation to learn more: https://docs.microsoft.com/en-us/powerapps/delegation-list
Considerations & Next Steps
Hopefully this was able to provide some guidance on how to tackle your move to SharePoint from Access. Make sure to take note of the differences in architecture I’ve outlined and feel free to leave a comment! The Export functionality is kind of quirky so if you have any questions post it here. I’d be happy to discuss in more detail the migration experience we had.
