如何在 VBA 中使用 ADODB 连接连接到 MS Dataverse
我已通过以下子例程成功连接到 VBA 中的 MS SharePoint 列表：
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
我现在想知道如何在自定义工作环境中连接到 MS PowerApps Dataverse 表，但找不到任何相关文档主题。这可能吗？我无权访问任何 PowerApps API，因为我的 IT 部门不允许这样做。基本上，我试图在下面的环境中找到表的连接字符串：
任何帮助是赞赏。
我尝试在connectionstrings.com 上寻找合适的解决方案，但还没有找到任何解决方案。我也尝试过使用各种关键字搜索谷歌。也许我正在寻找错误的东西。
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:
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对 Dataverse 的访问是通过一组中央权限进行控制的，如果您的 IT 组阻止对其 API 的访问，那么它也会阻止您对 TSQL 端点的访问。此外，必须在 Dataverse 上启用 TSQL 才能访问它（可以禁用）
您可以在此处找到有关如何连接的信息：https://learn.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query 。
我建议，在投入大量时间在 VBA 上之前，先检查 SQL Management Studio 以确保您可以登录和查询。您可以从这里获取：https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
如果您可以登录和查询，则可以使用输入到 Management Studio 中的相同数据构建连接字符串，
您应该记住，Dataverse TSQL 需要 AAD 授权才能登录。
请务必花时间阅读此https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver15 关于哪个提供商使用以及如何形成连接字符串。
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.