I am planning to upsize an Access 2010 database to a SQL Server version 11 (these are the versions I use in the moment).
我计划将Access 2010数据库升级为SQL Server版本11(这些是我目前使用的版本)。
I am familiar with Access since version 1.0 and, to a lesser extent, with SQL-Server since a couple of years. But the last time I upsized an Access database to SQL Server was many years ago.
我从1.0版开始熟悉Access,并且在较小程度上熟悉SQL-Server几年。但是很多年前我最后一次将Access数据库升级为SQL Server。
Now I am studying articles on the internet about the automatic and manual upsizing. But almost all of these articles relate to Access versions 2003 or 2007 and earlier SQL Server versions.
现在我正在互联网上研究有关自动和手动升迁的文章。但是,几乎所有这些文章都与Access版本2003或2007以及早期的SQL Server版本相关。
Now my question: Did anything significant change about the upsizing process over the last years and versions or is it basically still the same process? Did certain things change so much that a recommendation i.e. for Access 2007 is irrelevant or maybe even wrong for 2010?
现在我的问题是:过去几年和版本的升迁过程有什么重大变化,还是基本上仍然是同一个过程?某些事情是否发生了如此大的变化,以至于对于Access 2007的建议与2010年无关或甚至可能是错误的?
One example is this article which “Applies To: Access 2007”:
Move Access data to a SQL Server database by using the Upsizing Wizard
https://support.office.com/en-us/article/Move-Access-data-to-a-SQL-Server-database-by-using-the-Upsizing-Wizard-5D74C0DF-C8CD-4867-8D07-E6E759D72924
本文的一个例子是“适用于:Access 2007”:使用升迁向导将访问数据移动到SQL Server数据库https://support.office.com/en-us/article/Move-Access-data-to -a-SQL-Server的数据库按使用最升迁-向导5D74C0DF-C8CD-4867-8D07-E6E759D72924
Or this article from year 2000:
ACCESS DATABASES ( DSN vs DSN-LESS)
http://www.powerasp.net/content/database/dsn_vs_dnsless.asp
或者这篇2000年的文章:ACCESS DATABASES(DSN vs DSN-LESS)http://www.powerasp.net/content/database/dsn_vs_dnsless.asp
I am willing to read and learn but obviously I don’t want to waste my time reading staff which is outdated and now maybe wrong.
我愿意阅读和学习,但显然我不想浪费时间阅读过时的员工,现在可能是错的。
How is your experience with upsizing a new version of Access compared to an older version? Did something significant change?
与旧版本相比,您对升级新版本Access的体验如何?有什么重大变化吗?
1 个解决方案
#1
The Version of Microsoft SQL Server is negligible regarding your Question.
关于您的问题,Microsoft SQL Server的版本可以忽略不计。
There are two important changes in the history of Access regarding SQL-Server-Backends.
有关SQL-Server-Backends的Access历史记录有两个重要更改。
1.) With Access 2000 Microsoft introduced the new ADP (Access Data Project) file type that allows closer integration of MS-SQL-Server-Databases as Backend using ADO (ActiveX Data Objects). This was the recommend way to build Access applications with SQL-Backend for a couple of years. However, after the release of Access 2010 Microsoft decided that they are not going to support the ADP-File-Type anymore and they removed all support for ADPs in Access 2013!
1.)使用Access 2000,Microsoft引入了新的ADP(Access Data Project)文件类型,允许使用ADO(ActiveX数据对象)将MS-SQL-Server-Databases更紧密地集成为后端。这是使用SQL-Backend构建Access应用程序几年的推荐方法。但是,在Access 2010发布后,Microsoft决定不再支持ADP-File-Type,并且他们在Access 2013中删除了对ADP的所有支持!
2.) Up until Access 2003 the DAO-Library (Data Access Objects) included support for ODBC-Direct-Workspaces, which allowed you to call stored procedures and functions on SQL-Server via DAO. But with Access 2007 Microsoft removed ODBC-Direct and hinted towards the ADO and ADP-Features to implement such stuff in your application. – When they later (Access 2013) changed their recommendation to use DAO instead of ADO/ADP for SQL-Connectivity, they did not provide any useful replacement for ODBC-Direct.
2.)直到Access 2003 DAO-Library(数据访问对象)包括对ODBC-Direct-Workspaces的支持,它允许您通过DAO在SQL-Server上调用存储过程和函数。但是使用Access 2007 Microsoft删除了ODBC-Direct并暗示了ADO和ADP-Features在您的应用程序中实现这些内容。 - 当他们稍后(Access 2013)将他们的建议改为使用DAO而不是ADO / ADP进行SQL连接时,他们没有为ODBC-Direct提供任何有用的替代品。
The current recommendation (by Microsoft) is to use Access with the DAO-Library and linked tables via ODBC to connect your Access-Frontend-Application to SQL-Server. Pass-Through-Queries are recommended for anything that is beyond a linked table or linked view.
当前的建议(由Microsoft)使用Access与DAO-Library和链接表通过ODBC将Access-Frontend-Application连接到SQL-Server。对于链接表或链接视图之外的任何内容,建议使用Pass-Through-Queries。
I personally advise to extend this approach by combining DAO/linked-tables with an ADO-Connection to SQL-Sever in VBA to call Stored Procedures and Functions on SQL-Server, instead of Pass-Through-Queries. This obviously creates a bit of a technology mix-up but it tremendously increases your possibilities to interact with business logic implemented in the SQL-Server-Database.
我个人建议通过将DAO /链接表与VBA中的SQL-Sever的ADO-Connection相结合来扩展此方法,以在SQL-Server上调用存储过程和函数,而不是传递查询。这显然会产生一些技术混乱,但它极大地增加了与SQL-Server-Database中实现的业务逻辑交互的可能性。
If you keep all that in mind, most of the advice regarding Access-SQL-Server-Upsizing is still valid, no matter how old it is.
如果你记住这一切,那么无论多大年纪,关于Access-SQL-Server-Upsizing的大多数建议仍然有效。
#1
The Version of Microsoft SQL Server is negligible regarding your Question.
关于您的问题,Microsoft SQL Server的版本可以忽略不计。
There are two important changes in the history of Access regarding SQL-Server-Backends.
有关SQL-Server-Backends的Access历史记录有两个重要更改。
1.) With Access 2000 Microsoft introduced the new ADP (Access Data Project) file type that allows closer integration of MS-SQL-Server-Databases as Backend using ADO (ActiveX Data Objects). This was the recommend way to build Access applications with SQL-Backend for a couple of years. However, after the release of Access 2010 Microsoft decided that they are not going to support the ADP-File-Type anymore and they removed all support for ADPs in Access 2013!
1.)使用Access 2000,Microsoft引入了新的ADP(Access Data Project)文件类型,允许使用ADO(ActiveX数据对象)将MS-SQL-Server-Databases更紧密地集成为后端。这是使用SQL-Backend构建Access应用程序几年的推荐方法。但是,在Access 2010发布后,Microsoft决定不再支持ADP-File-Type,并且他们在Access 2013中删除了对ADP的所有支持!
2.) Up until Access 2003 the DAO-Library (Data Access Objects) included support for ODBC-Direct-Workspaces, which allowed you to call stored procedures and functions on SQL-Server via DAO. But with Access 2007 Microsoft removed ODBC-Direct and hinted towards the ADO and ADP-Features to implement such stuff in your application. – When they later (Access 2013) changed their recommendation to use DAO instead of ADO/ADP for SQL-Connectivity, they did not provide any useful replacement for ODBC-Direct.
2.)直到Access 2003 DAO-Library(数据访问对象)包括对ODBC-Direct-Workspaces的支持,它允许您通过DAO在SQL-Server上调用存储过程和函数。但是使用Access 2007 Microsoft删除了ODBC-Direct并暗示了ADO和ADP-Features在您的应用程序中实现这些内容。 - 当他们稍后(Access 2013)将他们的建议改为使用DAO而不是ADO / ADP进行SQL连接时,他们没有为ODBC-Direct提供任何有用的替代品。
The current recommendation (by Microsoft) is to use Access with the DAO-Library and linked tables via ODBC to connect your Access-Frontend-Application to SQL-Server. Pass-Through-Queries are recommended for anything that is beyond a linked table or linked view.
当前的建议(由Microsoft)使用Access与DAO-Library和链接表通过ODBC将Access-Frontend-Application连接到SQL-Server。对于链接表或链接视图之外的任何内容,建议使用Pass-Through-Queries。
I personally advise to extend this approach by combining DAO/linked-tables with an ADO-Connection to SQL-Sever in VBA to call Stored Procedures and Functions on SQL-Server, instead of Pass-Through-Queries. This obviously creates a bit of a technology mix-up but it tremendously increases your possibilities to interact with business logic implemented in the SQL-Server-Database.
我个人建议通过将DAO /链接表与VBA中的SQL-Sever的ADO-Connection相结合来扩展此方法,以在SQL-Server上调用存储过程和函数,而不是传递查询。这显然会产生一些技术混乱,但它极大地增加了与SQL-Server-Database中实现的业务逻辑交互的可能性。
If you keep all that in mind, most of the advice regarding Access-SQL-Server-Upsizing is still valid, no matter how old it is.
如果你记住这一切,那么无论多大年纪,关于Access-SQL-Server-Upsizing的大多数建议仍然有效。