I m using Access 97 database.It has lots of forms. Data bulked. I have to upgrade it quickly. I bought SQL Server 2005 enterprise edition.
我使用的是Access 97数据库。它有很多表单。数据量大。我必须快速升级它。我买了SQL Server 2005企业版。
I want to use SQL Server for data holder. I m going to use Access forms regularly. I just want to export data to the sql server.
我想使用SQL Server作为数据持有者。我将定期使用Access表单。我只想将数据导出到sql server。
Is it possible to use "Linked" data storing?
是否可以使用“链接”数据存储?
3 个解决方案
#1
While I agree with HLGEMs first paragraph I respectfully disagree with HLGEMs second paragraph. There are quirks you need to know about of which I'm somewhat ignorant. Such as changing boolean fields to LittleInt. But otherwise it's a lot of tedious work to recreate the database schema. And it'll be error prone such as missing indexes or relationships.
虽然我同意HLGEM的第一段,但我不同意HLGEM的第二段。你需要知道的怪癖,我有点无知。比如将布尔字段更改为LittleInt。但是否则重新创建数据库模式需要做很多繁琐的工作。并且它会出错,例如缺少索引或关系。
There is a tool from the SQL Server group which is a lot better than the Upsizing Wizard especially the Access 97 version. SQL Server Migration Assistant for Access (SSMA Access) http://www.microsoft.com/sql/solutions/migration/access/default.mspx
SQL Server组中有一个工具比升迁向导,特别是Access 97版本要好得多。用于访问的SQL Server迁移助手(SSMA访问)http://www.microsoft.com/sql/solutions/migration/access/default.mspx
As you discover these quirks you can change the scripts to recreate the database with the appropriate changes.
当您发现这些怪癖时,您可以更改脚本以使用适当的更改重新创建数据库。
#2
I concur with Tony Toews (and you should trust him on this, he's an Access guru): use SSMA to help you move data to SQL Server, it does a more complete job than the upsizing Wizard integrated in Access (which doesn't work for upsizing to SQL Server 2008 anyway).
我同意Tony Toews(你应该相信他,他是一个Access大师):使用SSMA帮助你将数据移动到SQL Server,它比集成在Access中的升迁向导(它不起作用)做得更完整无论如何都要升级到SQL Server 2008)。
You have to be wary of a few caveat though; I've made a blog post about some of the things you should check out.
The point is that if the original Access database was designed without relying too much on the liberties that Access allows (strange characters in table and column names for instance), then the process will be much easier.
Pay special attention to all the warning and errors reported by SSMA, they are really useful in helping you focus on the issues you must solve.
你必须要警惕一些警告;我发了一篇关于你应该检查的一些事情的博客文章。关键是如果设计原始Access数据库时没有过多依赖Access允许的*(例如表和列名称中的奇怪字符),那么该过程将更加容易。特别注意SSMA报告的所有警告和错误,它们非常有助于您专注于必须解决的问题。
With regards to performance, moving to SQL Server isn't necessarily going to make things faster.
In some areas it will actually be slower, sometimes much much slower:
Access is pretty good at optimizing certain forms of data access but once the database moves outside of its reach, it doesn't have as much control.
Most things will work fine though.
在性能方面,迁移到SQL Server并不一定能让事情变得更快。在某些领域,它实际上会更慢,有时慢得多:访问非常适合优化某些形式的数据访问,但是一旦数据库超出其范围,它就没有那么多的控制权。大多数事情都会很好。
You will probably have to rewrite a few queries, maybe move them as views on SQL Server instead of keeping them in your Access application.
Little things such as using % instead of * as wildchars in queries using LIKE in their WHERE clause can also cause strange issues like queries not returning any records.
您可能需要重写几个查询,可能将它们作为SQL Server上的视图移动,而不是将它们保存在Access应用程序中。在WHERE子句中使用LIKE的查询中使用%而不是*作为wildchars的小事情也会导致奇怪的问题,例如查询不返回任何记录。
By the way, I'll post a very good resource Tony has on his own website regarding SQL upsizing: My random thoughts on SQL Server Upsizing from Microsoft Access.
顺便说一下,我将在他自己的网站上发布一个关于SQL升级的非常好的资源:我对SQL Server升级的随机想法从Microsoft Access升级。
There is also a good and detailed read about things to consider when using SQL Server from Access: Optimizing Microsoft Office Access Applications Linked to SQL Server
从Access中使用SQL Server时,还有一个关于要考虑的事项的详细阅读:优化链接到SQL Server的Microsoft Office Access应用程序
#3
YOu can add the SQL Server tables to access as linked tables. Then you will want to start looking at your slowest queries and convert them to stored procs.
您可以添加SQL Server表作为链接表进行访问。然后,您将要开始查看最慢的查询并将它们转换为存储过程。
Do not use the upgrade wizard in Access to create the SQl Server tables becasue it will make poor choices for datatypes. Do the work yourself to create the scripts, choosing the best datatypes. It takes longer this way, but your database will perform better and you will gain a better understanding of how to do things in SQL Server. You should start right now, learning to do everything through a script and never from the GUI. Best to learn good habits in SQL Server from the start.
不要在Access中使用升级向导来创建SQl Server表,因为它会对数据类型做出糟糕的选择。自己动手创建脚本,选择最佳数据类型。这种方式需要更长的时间,但您的数据库性能会更好,您将更好地了解如何在SQL Server中执行操作。你应该立即开始,学习通过脚本完成所有事情,而不是从GUI开始。最好从一开始就学习SQL Server的好习惯。
#1
While I agree with HLGEMs first paragraph I respectfully disagree with HLGEMs second paragraph. There are quirks you need to know about of which I'm somewhat ignorant. Such as changing boolean fields to LittleInt. But otherwise it's a lot of tedious work to recreate the database schema. And it'll be error prone such as missing indexes or relationships.
虽然我同意HLGEM的第一段,但我不同意HLGEM的第二段。你需要知道的怪癖,我有点无知。比如将布尔字段更改为LittleInt。但是否则重新创建数据库模式需要做很多繁琐的工作。并且它会出错,例如缺少索引或关系。
There is a tool from the SQL Server group which is a lot better than the Upsizing Wizard especially the Access 97 version. SQL Server Migration Assistant for Access (SSMA Access) http://www.microsoft.com/sql/solutions/migration/access/default.mspx
SQL Server组中有一个工具比升迁向导,特别是Access 97版本要好得多。用于访问的SQL Server迁移助手(SSMA访问)http://www.microsoft.com/sql/solutions/migration/access/default.mspx
As you discover these quirks you can change the scripts to recreate the database with the appropriate changes.
当您发现这些怪癖时,您可以更改脚本以使用适当的更改重新创建数据库。
#2
I concur with Tony Toews (and you should trust him on this, he's an Access guru): use SSMA to help you move data to SQL Server, it does a more complete job than the upsizing Wizard integrated in Access (which doesn't work for upsizing to SQL Server 2008 anyway).
我同意Tony Toews(你应该相信他,他是一个Access大师):使用SSMA帮助你将数据移动到SQL Server,它比集成在Access中的升迁向导(它不起作用)做得更完整无论如何都要升级到SQL Server 2008)。
You have to be wary of a few caveat though; I've made a blog post about some of the things you should check out.
The point is that if the original Access database was designed without relying too much on the liberties that Access allows (strange characters in table and column names for instance), then the process will be much easier.
Pay special attention to all the warning and errors reported by SSMA, they are really useful in helping you focus on the issues you must solve.
你必须要警惕一些警告;我发了一篇关于你应该检查的一些事情的博客文章。关键是如果设计原始Access数据库时没有过多依赖Access允许的*(例如表和列名称中的奇怪字符),那么该过程将更加容易。特别注意SSMA报告的所有警告和错误,它们非常有助于您专注于必须解决的问题。
With regards to performance, moving to SQL Server isn't necessarily going to make things faster.
In some areas it will actually be slower, sometimes much much slower:
Access is pretty good at optimizing certain forms of data access but once the database moves outside of its reach, it doesn't have as much control.
Most things will work fine though.
在性能方面,迁移到SQL Server并不一定能让事情变得更快。在某些领域,它实际上会更慢,有时慢得多:访问非常适合优化某些形式的数据访问,但是一旦数据库超出其范围,它就没有那么多的控制权。大多数事情都会很好。
You will probably have to rewrite a few queries, maybe move them as views on SQL Server instead of keeping them in your Access application.
Little things such as using % instead of * as wildchars in queries using LIKE in their WHERE clause can also cause strange issues like queries not returning any records.
您可能需要重写几个查询,可能将它们作为SQL Server上的视图移动,而不是将它们保存在Access应用程序中。在WHERE子句中使用LIKE的查询中使用%而不是*作为wildchars的小事情也会导致奇怪的问题,例如查询不返回任何记录。
By the way, I'll post a very good resource Tony has on his own website regarding SQL upsizing: My random thoughts on SQL Server Upsizing from Microsoft Access.
顺便说一下,我将在他自己的网站上发布一个关于SQL升级的非常好的资源:我对SQL Server升级的随机想法从Microsoft Access升级。
There is also a good and detailed read about things to consider when using SQL Server from Access: Optimizing Microsoft Office Access Applications Linked to SQL Server
从Access中使用SQL Server时,还有一个关于要考虑的事项的详细阅读:优化链接到SQL Server的Microsoft Office Access应用程序
#3
YOu can add the SQL Server tables to access as linked tables. Then you will want to start looking at your slowest queries and convert them to stored procs.
您可以添加SQL Server表作为链接表进行访问。然后,您将要开始查看最慢的查询并将它们转换为存储过程。
Do not use the upgrade wizard in Access to create the SQl Server tables becasue it will make poor choices for datatypes. Do the work yourself to create the scripts, choosing the best datatypes. It takes longer this way, but your database will perform better and you will gain a better understanding of how to do things in SQL Server. You should start right now, learning to do everything through a script and never from the GUI. Best to learn good habits in SQL Server from the start.
不要在Access中使用升级向导来创建SQl Server表,因为它会对数据类型做出糟糕的选择。自己动手创建脚本,选择最佳数据类型。这种方式需要更长的时间,但您的数据库性能会更好,您将更好地了解如何在SQL Server中执行操作。你应该立即开始,学习通过脚本完成所有事情,而不是从GUI开始。最好从一开始就学习SQL Server的好习惯。