0

I have been successful in connecting to a MS SharePoint list in VBA with the following sub-routine:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sConn As String
Dim sSQL As String
 
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
"DATABASE=https://<ORGANIZATION>.sharepoint.com/sites/<SITE>;" & _
"LIST={<LIST ID>};"

With cn:
    cn.ConnectionString = sConn
    Call cn.Open
End With

sSQL = "SELECT tbl.[<COLUMN NAME>] FROM [<LIST NAME>] as tbl;"
rs.Open sSQL, cn, adOpenStatic, adLockOptimistic
Application.ActiveSheet.Range("A2").CopyFromRecordset rs

I would now like to know how to connect to a MS PowerApps Dataverse table in my custom work environment, but can't find any documentation on this subject. Is this possible? I don't have access to any PowerApps APIs as my IT department disallows this. Basically I'm trying to find a connection string for a table in this environment below:

Power Apps Dataverse Web URL

Any help is appreciated.

I have tried to look on connectionstrings.com for a suitable solution, but haven't come up with anything. I have also tried searching google using various keywords. Maybe I'm searching for the wrong thing.

mf_cbn
  • 3
  • 2

1 Answers1

0

Access to Dataverse is controlled via a central set of permissions, if your IT Group is blocking access it its API, its also blocked for your access to the TSQL endpoint. Additionally TSQL has to be enabled on Dataverse for you to access it (It can be disabled)

You can find information on how to connect here: https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query.

I would recommend that, before investing a lot of time in VBA on it, check with SQL Management Studio first to make sure you can login and query. You can get that from here: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

If you can login and query, you can construct the connection string using the same data you input into the Management Studio,

You should keep in mind that Dataverse TSQL requires AAD Authorization to login. Make sure you take the time to read though this https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver15 as to which provider to use and how to form the connection string.

MattB
  • 171
  • 4
  • I read the article in your third link but I cannot see where it specifies the provider to use. I am trying to create an ADODB.Connection in Access VBA. The Microsoft "ODBC Driver 17 for SQL Server" cannot see the server, but Access is happy to link to tables using the "Dataverse" link manager. The properties of these linked tables shows a connection string "OData 4.0;HDR=NO;IMEX=2;DATASOURCE_CLASS=Dataverse;ACCDB=YES;DATABASE=;TABLE=" but that does not appear to specify a provider.
     – Simon Sellick Mar 24 '23 at 18:01
  • As I understand it, Authentication support was added in sql 18+ drivers. Access is using the dataverse api as only read is supported via sql. – MattB Mar 26 '23 at 14:51