Could anyone here give me a complete and thorough set of instructions for connecting to a Microsoft Access Database from SQL Server? The SQL Server I'm using is 2008--the 64-bit version. I was reading some forum post saying that the 64-bit version of SQL Server has trouble with JET, but even if that is the case is there some way to do it by setting up an ODBC data source?
这里的任何人都可以给我一套完整而全面的指令,用于从SQL Server连接到Microsoft Access数据库吗?我使用的SQL Server是2008 - 64位版本。我正在阅读一些论坛帖子,说64位版本的SQL Server在使用JET时遇到了问题,但即使是这种情况,还是有办法通过设置ODBC数据源来实现吗?
Again, please be as thorough as possible. Here are some specific questions to help guide your answer: Do I need to enable Ad Hoc Queries? How do I set up the ODBC data source (assuming I have to do that)? Do I need to add the data source as a "linked server"? What sort of query can I write to access the data? For instance, can I use OPENDATASOURCE() or OPENROWSET()? What would that query look like?
再次,请尽可能彻底。以下是一些有助于指导您的答案的具体问题:我是否需要启用Ad Hoc查询?如何设置ODBC数据源(假设我必须这样做)?我是否需要将数据源添加为“链接服务器”?我可以编写什么类型的查询来访问数据?例如,我可以使用OPENDATASOURCE()或OPENROWSET()吗?那个查询会是什么样的?
Super big thanks, in advance!
非常感谢,提前!
1 个解决方案
#1
3
ODBC provides a standard software API method for accessing both relational and non-relational DBMS (source: Wikipedia). In other words MS Access (Jet) specific trickery isn't going to be available.
ODBC提供了一种用于访问关系型和非关系型DBMS的标准软件API方法(来源:*)。换句话说,MS Access(Jet)特定的技巧将无法使用。
How to set up ODBC datasource? Search on Google for 'set up odbc data source'
如何设置ODBC数据源?在Google上搜索“设置odbc数据源”
Should the MS Access database be configured as an SQL Server linked server? If you want to have stored procedures on the SQL Server accessing the Access database, then Yes - it must be linked. If you want to have both SQL Server and MS Access database data available to the same application, then you could access the two databases separately and avoid the strange link from SQL Server to MS Access.
MS Access数据库是否应配置为SQL Server链接服务器?如果要在访问Access数据库的SQL Server上有存储过程,则是 - 必须将其链接。如果要同时为同一应用程序提供SQL Server和MS Access数据库数据,则可以单独访问这两个数据库,并避免从SQL Server到MS Access的奇怪链接。
#1
3
ODBC provides a standard software API method for accessing both relational and non-relational DBMS (source: Wikipedia). In other words MS Access (Jet) specific trickery isn't going to be available.
ODBC提供了一种用于访问关系型和非关系型DBMS的标准软件API方法(来源:*)。换句话说,MS Access(Jet)特定的技巧将无法使用。
How to set up ODBC datasource? Search on Google for 'set up odbc data source'
如何设置ODBC数据源?在Google上搜索“设置odbc数据源”
Should the MS Access database be configured as an SQL Server linked server? If you want to have stored procedures on the SQL Server accessing the Access database, then Yes - it must be linked. If you want to have both SQL Server and MS Access database data available to the same application, then you could access the two databases separately and avoid the strange link from SQL Server to MS Access.
MS Access数据库是否应配置为SQL Server链接服务器?如果要在访问Access数据库的SQL Server上有存储过程,则是 - 必须将其链接。如果要同时为同一应用程序提供SQL Server和MS Access数据库数据,则可以单独访问这两个数据库,并避免从SQL Server到MS Access的奇怪链接。