ODBC缺少字段 [英] ODBC Missing Fields
问题描述
我有一个MS Access 2010前端到SQL Server 2012后端数据库我使用OLE DB连接到该数据库。 我已经在我的PC上开发了这个，后端是一个本地版本的SQL Server，我有完整的访问权限。 一切正常，
罚款。但是，在生产中，前端必须挂钩到我没有访问权限的生产SQL Server数据库。问题是当我从生产数据库重新连接链接表时，我使用的视图中的多个字段消失了。事实上，消失的
字段是我用来链接表的主键和外键。据我所知，我可以完全访问SQL Server中的数据库而不是SA访问，因为该服务器中还有其他商业数据库，我不需要访问
到该服务器位于我们的数据中心。 如果我进入SQL Server，我可以很好地看到这些字段。只有当我在生产服务器上使用ODBC连接时，它们才不存在。我有什么办法可以解决这个问题？
您最初是否在本地sql上创建数据库，然后将其放在服务器上？
您也没有提到是否通过Windows身份验证连接，或者您是否使用SQL登录？
如果您使用SQL studio（SSMS） ）连接到公司的SQL服务器实例，确保使用链接+连接Access时使用的SAME登录。 （即：Windows身份验证用户或SQL登录）。
如果使用SELECT"GRANT"授予您某些权限，则可能会将某些权限分配给给定列。那些表（并且他们指定的列代替了表）。
换句话说，当然可以将权限和权限应用于各个列，但不会发生"意外" "（因此关于原始数据库来自何处的问题 - 来自您的建议似乎是在开发过程中使用的本地实例
）。
所以它当然可能如果使用不同的登录进行连接，则表"可以"显示不同的列。
另一个可能的原因是您使用的是不同的SQL驱动程序。 （但是这种"通常"导致列的数据类型在Access中显示错误，而不是它们被隐藏）。
如果你使用windows"默认"SQL驱动程序，那么任何较新的datetime2列将显示为stings并使事情变得混乱。 （因此，您希望选择Native 11或更高版本的驱动程序，并使用它来链接表以确保支持SQL Server中较新类型
的数据列。）
所以在测试期间，确保在本地计算机上启动SSMS以"连接"到生产SQL服务器时，使用链接表的SAME登录。
因此，从本地计算机上启动SSMS并使用相同的登录信息来链接同一台计算机上的表，您能看到这些列吗？
我还应该指出，如果你在谈论Access中的链接表，你不能使用OLE DB来连接到sql server（唯一的例外是你会使用在Access 2010之后折旧的ADP的应用程序，并且不会支持）。
所以OLEdb即将推出 - SQL Azure并不真正支持，Access中的链表永远不会使用OLEdb（除了折旧的ADP或所谓的访问数据项目）。
如果编写VBA代码并创建ADO记录集，仍然可以使用OLEdb，但对于标准链接表，您使用的是ODBC而不是OLEdb。
您当然可以连接到生产服务器，并使用此命令"检查"任何列权限：
sp_column_privileges @table_name ='dbo.Company'
此外，您可能希望确保使用相同的SQL驱动程序。首选默认的"遗留"SQL驱动程序，但如果您使用较新的列类型，则需要使用SQL本机11或更高版本的驱动程序 - 但默认情况下不安装这些驱动程序，并且必须安装
每个工作站。
关心，
Albert D. Kallal（访问MVP，2003-2017）
加拿大艾伯塔省埃德蒙顿
I have an MS Access 2010 Front End to SQL Server 2012 Backend Database I use OLE DB to connect to that Database. I've developed this on my PC with the backend to a local version of SQL Server that I have full sa Access to. Everything works fine. However, in production the Front End must hook to our Production SQL Server Database which I do not have sa Access. The issue is when I reconnect the linked tables from the Production Database Several Fields in the views I use disappear. In fact the fields that vanish are the Primary and Foreign Keys I use to link the tables. To the best of my knowledge I have full access to that database in SQL Server just not SA access because there are other commercial databases in that server that I don't need Access to and that server is located at our Data Center. If I go into SQL Server I can see those fields just fine. It's only when I connect using ODBC on the Production Server they're not there. Any idea on what I can do to fix this?
Did you originally create the database on your local sql, and then have it placed on the server?
You also don’t mention if you connecting via windows authentication, or are you using SQL logons?
If you using SQL studio (SSMS) to connect to the company’s instance of SQL server, MAKE sure you using the SAME logon you using when you link + connect with Access. (Ie: windows authenticated user, or SQL logons).
It is certainly possible that some permissions were assigned to given columns if a SELECT "GRANT" was used to give you rights to some of those tables (and they specified columns in place of just the table).
In other words is certainly possible to apply rights and permissions to individual columns, but it not going to occur by "accident" (hence the question as to where the original database came from – from your suggesting that seemed to be a local instance of SQL used during development).
So it certainly possible that if you connect using different logons, then tables "can" present different columns.
Another possible reason is you using a different SQL driver. (However this "usually" results in the data types of columns being shown wrong in Access, not that they are hidden).
If you use the windows "default" SQL driver, then any newer datetime2 columns will appear as stings and make a real mess of things. (So you want to choose the Native 11 or later driver, and link the tables using that to ensure support for the newer types of data columns in SQL server).
So during testing, make sure when you launch SSMS on your local machine to "connect" to the production SQL server, you use the SAME logon that you link tables with.
So from your local computer, launching SSMS with same logon you use to link your tables on that same machine, can you see those columns?
I should also point out that you can’t be using OLE DB to connect to sql server if you talking about linked tables in Access (the only exception to this would you be using ADP’s applications which are depreciated after Access 2010, and will not be supported).
So OLEdb is on its way out – not really supported by SQL Azure, and linked tables in Access never used OLEdb (except for the depreciated ADP or so called Access data projects).
You can still use OLEdb if you writing VBA code and creating ADO record sets, but for standard linked tables, you ARE using ODBC and not OLEdb.
You can certainly connect to the production server, and "check" of any column permissions with this command:
sp_column_privileges @table_name = 'dbo.Company'
Also, you likely want to ensure you using the same SQL drivers. The default "legacy" SQL driver is prefer, but if you using newer column types, then you want to use the SQL native 11 or later drivers – but these drivers are NOT installed by default and will have to be installed on each workstation.
Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada
这篇关于ODBC缺少字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
