cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VinnyClegg
Level: Powered On

PowerApp - Connect to access web app data

Im trying to create a power app to connect to data stored in an access web app.

 

Is this possible?


Accepted Solutions
VinnyClegg
Level: Powered On

Re: PowerApp - Connect to access web app data

The best method I have is, enable the read/write connection within access and then create a DSN file in notepad using the following text:

 

[ODBC]
DRIVER=SQL Server Native Client 11.0
UID=(username)
Pwd=(password)
Encrypt=yes
DATABASE=(database name)
SERVER=(servername)

 

Save the file with the extension .dsn

 

You can then load this as a file data source in access or wherever to create a linked table.

 

As with any linked table you will not be able to edit the table design.


All Replies
VinnyClegg
Level: Powered On

Re: PowerApp - Connect to access web app data

Further to the above, I seem to be having issues connecting to a "normal" Azure database too.

 

If anyone could help that would be great

Microsoft Employee

Re: PowerApp - Connect to access web app data

Hi,

 

At present, PowerApps is unable to connect to Access. I suggest you export Access data to excel or store data in Azure database.

 

What issues did you meet? Could you provide error message or screenshot about the issue?

 

More information about Azure SQL Database for your reference:

https://powerapps.microsoft.com/en-us/tutorials/connection-azure-sqldatabase/

 

Thanks,

 

Dean Wang

 

Steelman70
Level 10

Re: PowerApp - Connect to access web app data

Yes, it is possible to connect to an Access Web App.  I assume that you know how to get the server, database, username and password information from Access, but if not here is how.

Enable Connections for your Access Web App

  1. Open the Access Web App in the Access client
  2. Click the File tab > Info > click Manage within the Connections group
  3. If your Access App is on SharePoint Online, select From Any Location or From My Location to allow connections. Note: If you are on an IPv6 network, you will only have the option to connect From Any Location. In an on-premise installation, these options are not available.
  4. Select either Enable Read-Only Connection or Enable Read-Write Connection
  5. Depending on the previous selection, select either View Read-Only Connection Information or View Read-Write Connection Information
  6. Copy and paste the Server, Database, UserName and Password values into Notepad or Word, or leave the Connection Information window open so you can copy and paste the information from it later.

 

Then in PowerApps the source type to use is SQL Azure, and you will be asked for a connection string.  Use the following

 

Server=tcp:MYSERVER.database.windows....,1433;Database=MYDATABASE;User ID=MYUSER;Password=MYPASSWORD;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

 

This worked very well for me.  Please let me know if anything is not clear.

VinnyClegg
Level: Powered On

Re: PowerApp - Connect to access web app data

Is writing to an access web app possible??

 

Steelman70
Level 10

Re: PowerApp - Connect to access web app data

Hello DeanWang, an Access Web App is actually an Azure SQL database back end generated in SharePoint where the web front end is edited in Access desktop.  I love it for simple rapid application development but unfortunately it seems Microsoft is not putting any effort in further developing it.

Steelman70
Level 10

Re: PowerApp - Connect to access web app data

I assume it is if you use the read-write connection details provided by Access, although I have not tested it yet.  I will do later today and confirm.

VinnyClegg
Level: Powered On

Re: PowerApp - Connect to access web app data

Whenever I have tried to connect to the back end in other instances, it comes up with asking to configure the IP firewall, which you cant do with the Web app's azure DB

Steelman70
Level 10

Re: PowerApp - Connect to access web app data

I can confirm success in writing to the back end of the Access Web App.  In Access I left the option of "From Any Location" selected, I am not sure if that is your issue.  Also, to be clear I am using SharePoint online (as part of an Office365 subscription) and not on premise.

When you say "it comes up with asking", what is "it"?

JulianKirkness
Level: Powered On

Re: PowerApp - Connect to access web app data

I create Access Web Apps and sell them on the App Store and having been looking at Power Apps for a while. It's certainly possible to create read/write connections to AWA databases (as long as they aren't deployed 'locked' which mine are) - this looks like a very promissing way to build a hybrid mobile / web application (which is, after all, what most apps end up being). At the moment, there are limitations on the controls you can bind to the data etc - but I'm sure that will change soon.

 

I have also tested connecting a Flow to an AWA database and running a Data Macro from the AWA back end (which is basically a stored procedure) within the flow. You can't trigger a flow based on a new record in SQL yet - but it still opens up some possibilities.