将Access mdb转换为SQL Server有多难？ [英] How hard is it to convert an Access mdb to SQL Server?
问题描述
We have an Access app (quite big) at www.orbisoft.com/download.
We have had requests by potential users to have it converted to an SQL
version for them since there corporate policy excludes them from buying mdb
backends.
Here''s the (million dollar?) questions :)
How long and how difficult a process would it be?
Which SQL platform would we be best to develop it on?
Are there third party companies that could do it all for us?
Anticipated costs?
Anticipated time frames?
Anticipated issues?
Thanks for any thoughts...
Regards
Mark
推荐答案
2004年1月7日星期三10:59:50 +1300，马克B
< re ************************** **********@ihug.co.nz>写道：
On Wed, 7 Jan 2004 10:59:50 +1300, "Mark B"
<re************************************@ihug.co.nz > wrote:
我们有一个Access应用程序（相当大）在 www.orbisoft.com/download 。
我们已经要求潜在用户将其转换为SQL版本，因为公司政策将其排除在外购买mdb
后端。
这是（百万美元？）的问题:)
这个过程有多长，多难？
这在很大程度上取决于应用程序。对于大多数应用程序而言，名义上可以在一天到一周之间的某个时间内完成大小的工作，但是接下来只需要很少的性能障碍就可以解决这个问题。因为它们是在
测试期间发现的。我建议在调整大小时保持MDB。如果您尝试将
切换到ADP，那么最终会重新处理应用程序的3/4。
我们最好在哪个SQL平台上开发它？
对于大多数具有ODBC驱动程序的后端，Access可以正常工作，但是对于MS SQL Server后端来说，它是最开心的。如果您使用MS SQL Server，那么
可以在表中使用TIMESTAMP（与DATETIME不同）列来提高乐观锁定的性能和可靠性，（你所有的
表单编辑将依赖于乐观锁定。
是否有第三方公司可以为我们做这一切？
当然 - 很多。任何具有Access C / S
申请专业知识的顾问或公司。如果我们能够达成双方都能接受的
交易，我可以帮到你。
预计成本？
不知道怎么说 - 关于应用程序的更多信息。
预计的时间框架？
可能在一周之内，但又一次，这在很大程度上取决于应用程序。
预期的问题？
We have an Access app (quite big) at www.orbisoft.com/download.
We have had requests by potential users to have it converted to an SQL
version for them since there corporate policy excludes them from buying mdb
backends.
Here''s the (million dollar?) questions :)
How long and how difficult a process would it be?
That depends quite a bit on the application. For most apps, a nominally
working up-size can be done in somewhere between a day to a week, but then a
few performance snags will have to be ironed out as they are discovered during
testing. I recommend keeping with an MDB during up-sizing. If you try to
switch to an ADP, you''ll end up reworking 3/4 of the app.
Which SQL platform would we be best to develop it on?
Access can work decently with most any back-end that has an ODBC driver, but
it''s happiest with an MS SQL Server back-end. If you use MS SQL Server, you
can use TIMESTAMP (not the same thing as DATETIME) columns in your tables to
improve the performance and reliability of optimistic locking, (and all your
form edits will rely on optimistic locking).
Are there third party companies that could do it all for us?
Sure - lots. Any consultant or company that has expertise in Access C/S
applications. I could do it for you if we can work out a mutually acceptable
deal.
Anticipated costs?
Impossible to say without knowing -lots- more about the app.
Anticipated time frames?
Probably under a week, but again, it very much depends on the app.
Anticipated issues?
再次，它在很大程度上取决于应用程序。
一个严重的问题通常是如果设计了Access应用程序
要直接在大型，未经过滤，未分页的表格上打开表单，它将会令人沮丧，并且会耗费您的网络资源。应用程序的这些
部分需要更改为使用更加C / S友好的设计
范例，例如向下钻取。
Again, it very much depends on the app.
One serious issue that commonly comes up is that if an Access app was designed
to open forms based directly on large, unfiltered, unaggregated tables, it
will berform dismally and place a drain on your network resources. These
parts of the app will need to be changed to use a more C/S-friendly design
paradigm such as a drill-down.
只需要我的2美分，但是一个完全膨胀到最大访问权限mdb =
一个MS Sql Server2000数据库容量的0.001％。 Sql Server是工业规模的RDBMS（与Oracle相同）。因此，您只需将
端口数据发送到Sql Server后端。访问是理想的前端系统
具有一些RDBMS功能（类似于VB6有一些OOP
功能，其中VB7（.net）完全是OOP）。如果你有基于
的表单，你可以在
Sql Server中创建视图来过滤你的数据集。即使使用ODBC，在后端使用Sql
Server也会比Access更有效。事实上，如果您的项目
包含多个Access应用程序，您可以将它们全部合并为一个Sql
服务器数据库。为了让您了解Sql Server维度，一个Sql Server
表可以支持1024列。您可以在Access中保持您的体系结构相同
（相同的表单 - 相同的表/查询名称）。诀窍就是将数据移植到Sql Server上。唯一的问题是Sql Server
在数据完整性方面比Access更不宽容（因此，更可靠的是
）。这是头痛开始的地方。我的解决方法是
在sql server中创建起始表，对所有
列使用nvarchar。然后使用实际数据类型创建实际表。如果
在将数据从一个表移植到
sql server中的下一个表时遇到错误，一旦你拥有所有这些就更容易找出问题
sql server中的数据。查询分析器为您提供了大量信息。
如果您确实需要性能并拥有基于表格的表格，请使用
存储过程（SP'' ）在Sql Server中填充Access表
只需要你需要的数据量并用ADO调用SP -
比ODBC快（ADO.Net是最快的）。你可以使用ODBC来支付
adhoc的东西。
Rich
***通过Developersdex http://www.developersdex.com ***
Don''只是参加USENET ...获得奖励！
Just my 2 cents worth, but one fully bloated to the max Access mdb =
0.001% of the capacity of one MS Sql Server2000 DB. Sql Server is the
industrial sized RDBMS (same as Oracle). As such, you are only going to
port data to a Sql Server backend. Access is ideally a front end system
with some RDBMS capabilities (kind of like VB6 has some OOP
capabilities, where VB7 (.net) is fully OOP). You can create views in
Sql Server to filter your datasets if you have forms that are based on
such. Even using ODBC, you will always have more performance using Sql
Server on the backend than Access. Matter of fact, if your project
includes several Access apps, you can combine all of them into one Sql
Server DB. To give you an idea of Sql Server dimension, one Sql Server
table can support 1024 columns. You can keep your architecture the same
in Access (same forms - same table/query names). The trick is just
porting your data to the Sql Server. The only catch is that Sql Server
is way less forgiving than Access in data integrity (thus, more
reliability). Here is where the headaches begin. My workaround is to
create beginning tables in sql server that use nvarchar for all the
columns. Then create the actual tables with the actual datatypes. If
you encounter errors when porting the data from one table to the next in
sql server it is way easier to figure out the problem once you have all
the data in sql server. Query Analyzer gives you a lot of information.
And if you really need performance and have forms based on tables, use
stored procedures (SP''s) in Sql Server to populate your Access tables
with just the amount of data that you need and invoke the SPs with ADO -
way faster than ODBC (ADO.Net is the fastest). You can use ODBC for
adhoc stuff.
Rich
*** Sent via Developersdex http://www.developersdex.com ***
Don''t just participate in USENET...get rewarded for it!
2004年1月6日23:39:33 GMT，Rich P< rp ***** @ aol.com>写道：
....
On 06 Jan 2004 23:39:33 GMT, Rich P <rp*****@aol.com> wrote:
....
如果你真的需要表演并拥有基于表格的表格，请使用
存储过程（SP'' s）在Sql Server中只使用您需要的数据量来填充Access表，并使用ADO调用SP - 比ODBC更快（ADO.Net是最快的）。您可以将ODBC用于
adhoc的东西。
And if you really need performance and have forms based on tables, use
stored procedures (SP''s) in Sql Server to populate your Access tables
with just the amount of data that you need and invoke the SPs with ADO -
way faster than ODBC (ADO.Net is the fastest). You can use ODBC for
adhoc stuff.
我对此大部分都有疑问。在许多情况下，JET / ODBC做得很好，如果你避免让它表现不佳的话，那么
表现得很好。在某些情况下，只要后端有一个本机ADO驱动程序，ADO就可以更快地获得
，但如果你非常小心，则只需要
。 ADO，特别是与ADP前端一起使用时，
在执行
更新之前往往要求提供大量关于后端的元数据，并且它没有''' t在查询之间缓存这些数据，除非你在同一个Command对象中重复使用
，这样可以反复减慢你的速度。
DAO也可以进行优化这是不可能的ADO
（除非使用JET提供程序，然后有什么意义）。首先，
您可以在本地缓存数据并在本地和远程数据之间进行连接。
哦，顺便说一下，有一个错误使TIMESTAMP基本上无法使用的ADP，这对使用乐观锁定的
更新的性能产生负面影响（所有更新都通过绑定表单，一件事） 。
当调整Access / JET数据库的大小时，鉴于JET / ODBC，正确趋势，
可以非常有效地运行大多数查询，并且考虑到ADP的学习曲线陡峭，以及在MDB中同时处理ADO和DAO与
的不一致，肯定有充分的理由坚持
DAO / ODBC，然后将各个查询升级到存储过程和视图，需要
，通过DAO访问它们。
I take issue with much of this. JET/ODBC does a fine job in many cases, and
performs well if you avoid the things that make it perform poorly. ADO can be
faster in some cases so long as the back-end has a native ADO driver, but only
if you''re very careful. ADO, especially when used with an ADP front-end,
tends to ask for lots of metadata about the back-end before performing an
update, and it doesn''t cache this data between queries unless you are reusing
the same Command object, so it can slow you down repeatedly.
There are also optimizations possible with DAO that are not possilbe with ADO
(unless using the JET provider, and then what''s the point). For one thing,
you can cache data locally and do joins between local and remote data.
Oh, and by the way, there is a bug in ADPs that make TIMESTAMP basically
impossible to use, and this has a negative impact on the performance of
updates using optimistic locking (all updates via bound forms, for one thing).
When up-sizing an Access/JET database, given that JET/ODBC, properly tended,
can run most queries perfectly well and efficiently, and given the steep
learning curve for ADPs, and the inconsistency of dealing with ADO and DAO at
the same time in an MDB, there is certainly a strong reason to stick with
DAO/ODBC, then upsize individual queries to stored procedures and views as
necessary, accessing them all via DAO.
这篇关于将Access mdb转换为SQL Server有多难？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
