ODBC 连接专有驱动程序/Excel 信任设置
我有一个 Excel 电子表格，它使用软件的 odbc 驱动程序（操作请求系统）连接到内部数据库。 效果很好。 现在，我尝试将 Excel 文件移动到 SharePoint 网站，以便我们的团队可以在同一电子表格中查看数据并做笔记。
因此，首先我尝试将 Excel 文件移动到服务器，但意识到文件中保存的连接指向我计算机上的连接文件。 所以我将其移至 SharePoint 网站，这似乎有效...除了：
1) 在某些计算机上，它打开得很好。 但如果要求数据库登录和密码的对话框是自定义的，我更喜欢它。
2）在其他计算机上，安装了软件和驱动程序，但未显示在用户源列表中，因此抛出错误。 当用户创建新连接时，驱动程序就在那里（因此它不在第一个列表中，但在更大的列表中）。
3) Mac 可以打开该文件，但 Excel 2008 无法执行宏和连接。
4) 从同一站点打开同一文件每次都会引发信任中心警告，即使在同一台计算机/同一用户上也是如此。
有没有办法执行以下任一操作？
有一个非宏可以阻止用户保存（从而覆盖）工作表，直到确认用户不会对连接/宏进行更改吗？
允许用户从本地配置进行连接（如果他们可以设置一个在主配置失败时仍能正常工作的配置），这样他们就可以使用该配置进行连接，但不会给其他人带来麻烦。
设置信任设置（我认为是本地或服务器上），以便来自特定 SharePoint 的任何文件始终受信任？
最后，这是最重要的......
您可以使用不在本地计算机上的驱动程序创建连接吗？ 如果驱动程序安装在远程服务器上，Excel 可以使用该驱动程序吗？ 如果是这样的话，那几乎可以解决所有其他问题（除非您使用的是 Mac）。
我觉得我的做法有点错误，但由于团队都需要查看彼此的笔记，我们唯一的其他解决方案是将数据（从数据库）上传到 MySQL 数据库，以便他们可以从他们的笔记中做笔记浏览器。 这使得它更加普遍可用，但迫使我们编写许多 Excel 中标准函数的脚本并创建冗余数据库。
I have a spreadsheet in Excel that connects to an internal DB using the odbc driver for the software (Action Request System). That works fine. Now I'm trying to move the Excel file to a SharePoint site so that our team can review the data and make notes in the same spreadsheet.
So, first I tried just moving the Excel file to the server, but realized that the connection saved in the file was pointing to the connection file on my computer. So I moved that to the SharePoint site, and this seems to be working... Except:
1) On some of the computers, it opens just fine. But I'd prefer it if the dialog that asks for the login and pw for the DB was customized.
2) On other computers, the software and drivers are installed, but not showing up in the user source list, so it throws an error. When the user goes to create a new connection, the driver is there, (so its not in the first list, but it is in the larger list).
3) Macs can open the file, but Excel 2008 can't do the macros and connections.
4) Opening the same file from the same site throws the Trust Center warning each time, even on the same computer/same user.
Is there a way to do any of the following?
Have a non-macro that keeps the user from saving (and thus overwriting) the sheet until it is confirmed that the user won't make changes to the connections/macros?
Allow the user to connect from a local config (in case they can set one up that works when the main one fails) so that they can connect using that one but not screw it up for everyone else.
Set the trust settings (local or on server, I suppose) so that any files from a specific SharePoint are always trusted?
Finally, and this is the big one...
Can you create a connection using a driver not on the local machine? If the driver is installed on a remote server, can Excel use that one? If that were the case, that would solve almost all other problems (Unless you're on a Mac).
I feel like I'm going at this slightly wrong, but since the team all need to see each others notes, our only other solution is to upload the data (from the DB) into a MySQL db so that they can make notes from their browser. This makes it more universally available, but forces us to script a lot of functions that are standard in Excel and create redundant DBs.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
哇！ 通过 ODBC 访问 ARS！ 九十年代，当我在 Remedy 总部工作时，我向架构师建议了这个功能。 美好的时光。
无论如何，我认为您共享连接到 ODBC 源的 Excel 工作表的方法并不是最简单的方法。 （正如您已经发现的那样。）]据我所知，
ODBC 始终是本地驱动程序。 客户端在本地连接到 ODBC。 然后ODBC就可以使用网络连接到DB。
相反，我建议您研究使用远程连接到 ARS 系统的表来设置 Access。 然后，您可以通过文件共享（而不是 dbms 连接）远程打开 Access 表。 我相信这会比你目前的道路更容易走下去。
HTH，
拉里
Wow! Access to the ARS via ODBC! I suggested that feature to the architect when I worked at Remedy HQ back in the nineties. Great times.
Anyhow, I think your approach of sharing an Excel sheet that connects to an ODBC source is not the easiest path. (As you've been discovering.) ]
ODBC is always a local driver, afaik. A client locally connects to ODBC. ODBC then can use the network to connect to the DB.
Instead, I suggest that you investigate setting up Access with a table that is remotely connected to the ARS system. You could then remotely open the Access table via file sharing as opposed to a dbms-connection. I believe that this would be easier to get going than your current path.
HTH,
Larry
听起来您正在使用用户 DSN 来建立连接。 您可以使用连接字符串切换到无 DSN 连接。 如果您需要一些帮助，您可以发布需要修改的代码吗？
It sounds like you are using a User DSN to make the connection. You can switch to a DSN less connection by using a connection string. If you need some help with that, can you post the code that needs modified?
首先感谢两位提供答案的网友。 你们俩都对我解决这个问题很有帮助。
因此，事实证明，虽然您无法轻松地直接连接到 Action Request DB，但 AR 确实支持 Web 服务。
我没有尝试让 Excel、Access、PHP 或任何其他系统来创建 Soap 客户端并自行处理工作，而是提出了一个我认为相当聪明的替代方案（如果我自己这么说的话）：
1) 创建使用请求的数据调用 Web 服务的 php 脚本，
2) 该脚本将响应解析为更通用的 XML 形式，
3) 脚本回显 xml
4) 将脚本命名为“AR_Data_Request.xml”
5) 配置目录，通过 .htaccess 文件，像对待 php 一样对待 xml 文件
现在，我有一个静态 xml 文件，它始终包含来自 Action Request 的最新数据。 我可以将任何其他应用程序（特别是 Excel）指向该 xml 文件，而不必编写 VBA 代码来直接查询 Web 服务（并且仍然需要为 php 再次执行此操作）。
唯一的问题是我无法让 php 连接到 Web 服务（doh！），但这将是我的下一个问题。
再次感谢！
First, thanks to both users that provided answers. Both of you were really helpful in getting my mind around the problem.
So, it turns out that while you can't easily connect to Action Request DB directly, AR does support Web Services.
Rather than attempt to make Excel, Access, PHP, or any other system deal with creating a Soap Client and handle the work themselves, I came up with an alternative that I think is fairly clever, if I do say so myself:
1) Created a php script that calls the Web Service with the requested data,
2) The script parses the response into a more generic XML form,
3) script echoes out the xml
4) Name the script "AR_Data_Request.xml"
5) Configure the directory, via the .htaccess file, to treat xml files like php
Now, I have a static xml file which always contains the most up-to-date data from Action Request. I can point any other apps (specifically Excel) to that xml file instead of having to write VBA code to query the web service directly (and still have to do it again for php).
The only problem is that I can't get the php to connect to the Web Service (doh!) but that will be my very next question.
Thanks again!