从访问升级 [英] Upgrading from access
问题描述
我正在研究的数据库正在通过使用访问来达到其局限性。
我正在考虑升级到SQL服务器或mySQL。
项目的目的是从使用访问作为前端而将另一个作为后端开始。这只是介绍阶段。
计划是一起消除访问的使用。但是，我将从头开始学习其中一个系统。我理解关系数据库的大多数概念，而不是任何一个系统的具体细节。
作为一个更广泛的操作范围，数据库将从一个服务器和3开始客户通过内部网络。更大的计划是让多个用户参与网络访问。
我对项目的主要关注点是：
总拥有成本
学习曲线
可用性
如果有人可以提供任何建议，为什么选择或不选择一个系统而不是另一个系统。我知道细节是sparce。我只是在寻找一般性讨论。
感谢您的帮助！
A database I am working on is reaching its limitations by using access.
I am considering upgrading to SQL server or mySQL.
The intent of the project is to start off by using access as the front end and the other as the back end. This is just for the introductory phase.
The plan is to eliminate the use of access all together. However I will be learning one of these systems from scratch. I understand most of the concepts of relational databases, just not the specific details of either system.
As a wider scope of the operation, the database will start out with a single server and 3 clients over the in house network. The greater plan is to involve web access for multiple users.
My major concerns for the project are:
Total cost of ownership
Learning curve
Useability
If anyone could offer any advice why to choose or not choose one system over the other. I understand the details are sparce. I''m just looking for general discussion.
Thanks for you help!
推荐答案
这是一个有趣的问题。我自己一直在问自己，我希望得到答案！这是一个很大的主题，就像任何方向请求一样，答案取决于你从哪里开始！
如果你只有中等的Access功能并最终打算完全取消Access（如在你的帖子中）然后你会更好地学习其他东西（待决定）
但是，听起来你现在在Access数据库上投入了相当大的资金。你说它正在达到它的局限性。以什么方式？数据大小？反应速度？用户数量？上网？
升级到Microsoft SQL Server（MSSS）的一种便宜方法是免费下载SQL Server Express软件包。然后，您必须将Access后端迁移到MSSS。 MS提供了一些工具，我认为它们与简单的数据库一起工作，但我相信Nulls存在问题，Access接受的有趣（无效）日期，但MSSS不会，Yes / No字段（'cos Access可以是Yes / No / Null）。每个表都必须有一个唯一的索引（无论您是否使用它，例如，您可能选择SalesOrderDetails作为SalesOrder编号和行号作为复合键。那么MSSS将需要一个唯一的ID字段来进行内部优化）是一个我不记得的其他Gotchas的列表，我不确定MS工具的最新版本是否适合他们，但如果你谷歌''访问升级''你会发现一些商业工具是必须帮助您解决这些问题。我们不允许在这里做广告，所以我无法给出直接链接。
我想到的网站上有视频来解释问题并展示它是如何完成的。
只需将数据保存在SQL Server数据库中就不会自动改善响应时间。如果您有包含数十万行的表，那么Access仍然会通过网络要求所有数据来显示您想要的一条记录。在这些情况下，您可能需要修改表单以将条件发送到MSSS以在服务器上执行查询，以便通过网络发送所需的行。
服务器上的查询称为视图。我使用的商业工具有选项从表单中提取所有查询并在服务器上将它们写为视图。
我有访问前端的用户访问MS SQL Server上的数据相隔几百英里的WAN，所以我知道它有效。
我也有IT部门告诉我他们不想在桌面上访问（阻止用户开发自己的应用程序！所以我正在探索使用Web服务器后端的Web窗体。同样稳定的供应工具可以帮助解决这个问题，但我一直懒惰而且还没让他们为我工作！
我希望这是一个开始，并有兴趣阅读体验其他。
S7
Hi,
This is an interesting question. I''m constantly asking it myself and I wish I had the answer! It is a big subject, and like any request for directions, the answer depends where you are starting from!
If you only have moderate Access capabilities and ultimately intend to eliminate Access altogether (as in your post) then you would be better off learning something else (to be decided)
However, it sounds like you have quite a big investment in an Access database at the moment. You say that it is reaching it''s limitations. In what way? Size of data? Speed of response? Number of users? Internet access?
One cheap way to upsize to Microsoft SQL Server (MSSS) is to download the SQL Server Express package for free. You will then have to migrate your Access backend to MSSS. MS supply some tools which I believe work with simple databases but I believe have problems with Nulls, funny (invalid) dates that Access accepts but MSSS will not, Yes/No fields (''cos Access can be Yes/No/Null). Every table must have a unique index (whether you use it or not e.g. you may be selecting SalesOrderDetails by a SalesOrder number and a Line number as a compound key. Well MSSS will want a unique ID field for it''s internal optimisation)There is a list of other Gotchas which I can''t remember and I''m not sure that the latest version of the MS tool accomodates them, but if you Google ''Access Upsizing'' you will find some commercial tools that are a MUST to help you resolve these issues. We are not allowed to advertise here so I cannot give a direct link.
The website I am thinking of has videos to explain the issues and show how it is done.
Just holding the data in a SQL Server database will not neccessarily automatically improve response times. If you have tables with hundreds of thousands of rows, then Access will still demand all the data over the wire to show the one record you want. In these cases you may have to revise your form to send the criteria to MSSS to execute the query on the server so just the rows you want are sent over the wire.
Queries on the server are called Views. The commercial tool I use has options to extract all the queries out of the forms and write them as Views on the server.
I have users with Access front ends accessing data on MS SQL Server over a WAN hundreds of miles apart, so I know it works.
I also have IT departments telling me they do not want Access on the desktop (to stop users developing their own apps!)So I am exploring the use of Web Forms with a SQL server backend. The same stable supply tools to assist with that, but I have been lazy and not got them working for me yet!
I hope that is a start and will be interested to read the experience of others.
S7
Sierra7：
只是将数据保存在SQL Server数据库中不会自动改善响应时间。如果您有包含数十万行的表，那么Access仍然会通过网络要求所有数据来显示您想要的一条记录。在这些情况下，您可能需要修改表单以将条件发送到MSSS以在服务器上执行查询，以便通过网络发送所需的行。
Sierra7:
Just holding the data in a SQL Server database will not neccessarily automatically improve response times. If you have tables with hundreds of thousands of rows, then Access will still demand all the data over the wire to show the one record you want. In these cases you may have to revise your form to send the criteria to MSSS to execute the query on the server so just the rows you want are sent over the wire.
这是一个非常有用的答案。如果我选择这一段然后它只显示其余部分的质量。
我不相信这一般是正确的。我的理解是Access（Jet）确定（不一定100％成功地介意）它可以以标准的方式发送到BE服务器的内容。因此，许多情况都是优选的，因此很少需要通过电线传输数据。传递查询也是可能的，其中几乎所有SQL都传递给BE，但在这种情况下，SQL必须采用正确的BE服务器本机格式。然而，在某些情况下，这种优化是不可能的，或者只是最低限度的有效，并且这些可能导致大量数据被传输。这是出于显而易见的原因尽可能避免的情况。
@ Wisni1rr。
如果我可以被允许就你自己的位置提出建议而不会造成任何冒犯，我对你的数据库能力的估计是你会遇到除Access以外的BE数据库系统。它们往往比Access更加复杂，而且您似乎正处于使用Access的过程中。
访问通常也被认为是获取创意的最快方法到一个工作项目。许多认真的开发人员仍然认为这是提供FE功能的最合适方式。有些人不同意，S7关于向最终用户提供这样一个工具的观点经常涉及这样的建议，但是没有人可以提供一种快速开发的替代方案。
因此，对于您的情况，我建议您掌握适合您的数据存储BE系统（这本身就是一个足够大的跳跃），但继续磨练您的技能，理解Access中的代码库仍然存在。
祝你好运不过你决定:-)
-NeoPa。
This is a very helpful answer. If I pick at this one paragraph then it it only shows the quality of the rest by contrast.
I don''t believe this is generally true. My understanding is that Access (Jet) determines (not necessarily 100% successfully mind) what it can send in the way of criteria to the BE server. Many situations are thus optmised so that very little of the data need pass over the wire. Pass-thru queries are also possible where literally all the SQL is passed to the BE, but in such cases the SQL must be in the correct native format for the BE server. Nevertheless, there are situations where such optimasations are not possible, or are only minimally effective, and these can result in large volumes of data being transferred. This is a situation to avoid where at all possible for obvious reasons.
@Wisni1rr.
If I can be allowed to advise on your own position without causing any offense, my estimation of your database abilities is that you would struggle with the BE database systems other than Access. They tend to be considerably more involved than Access, and you seem to be in the process of getting to grips with Access.
Access is also generally agreed to be the quickest approach for getting ideas to a working project. Many serious developers still consider this is the most appropriate way to provide the FE functionality. Some disagree, and S7''s point about giving such a tool to end users is often involved with such advice, but none can offer an alternative that''s as quick to develop in.
Therefore, for you in your situation, I''d suggest getting to grips with the proper BE system of your choice for the data storage (which will be a big enough jump in itself), but continue to hone your skills, understanding and code-base in Access still.
Good luck whatever you decide though :-)
-NeoPa.
如果你通过''局限'定义你的意思，那么它会更容易帮助。
在任何情况下，我都使用Access和SQL服务器作为后端，在两者上维护各种数据库后，我最近将所有数据库转换为SQL Server。所有前端仍然在Access中，因为没有理由将Access作为前端应用程序退出。预先构建的功能将为您节省大量时间。
现在，我移动后端的原因各不相同。一些问题是Access的限制，一些是性能，有些只是因为我更容易在单一类型的环境中管理各种应用程序。
Access具有2-4 GB的限制，具体取决于版本，在某些情况下我很容易超过这个限制。当超过5-6个用户同时在那里时，访问后端也往往会挣扎。我还继承了许多Access DB的维护，这些Access DB本质上存储相同的数据，但根据上次更新的不同而具有不同的值。
将BE移动到SQL Server解决了所有这些问题。
所有这些都说明了，了解SQL Server很重要仍然只是像Access这样的软件程序。我在一家非常大的公司工作，我的SQL Server数据库位于一台专门用作SQL服务器且有26个处理器的机器上。如果你把它加载到你办公室的单处理器机器上......那么，谁知道它将如何运行。可能和Access一样。
至于拉数据，没有理由你不能使用Access。正如NeoPa指出的那样，诀窍是使用传递查询。通过这样做，SQL Server将处理查询并只发送结果。我有这样的基于50万条记录的查询，存储在距离我一百英里的数据库中，并在1秒内返回结果。唯一需要注意的是，您必须学习SQL Server的SQL语法，如果您具有在Access查询中使用的函数，则必须找到它们的SQL Server等效项，或者自己在SQL服务器上编写它们。它是值得学习的，即使它听起来很痛苦。
如果你选择不使用传递查询，Access并不一定要求所有记录，如某些人所述。在你的什么地方的任何东西子句通常是服务器端处理的。避免使用HAVING您的GROUP BY条款因为这几乎肯定会在你身边加工。
好吧，这比我想要的更啰嗦，所以我会留下它。如果您有任何具体问题，我会尽力帮助。
If you define what you mean by ''limitations'', it would be easier to help.
In any case, I''ve used both Access and SQL server as backends, after maintaining various databases on both, I''ve recently converted all of them over to SQL Server. All the front-ends are still in Access because there is no reason to retire Access as a front-end application. The pre-built functionality will save you gobs of time.
Now, the reasons I moved the backends varied. Some issues were limitations of Access, some were performance, and some were just because it was easier for me to manage the various applications within a single type of environment.
Access had a 2-4 GB limit depending on version, and I was exceeding that easily in some instances. Access backends also tend to struggle when more than 5-6 users are in there at the same time. I also inherited the maintenance of numerous Access DB''s that were essentially storing the same data, but had different values depending on the last time it was updated.
Moving the BE''s to SQL Server solved all of these issues.
All of that said, it''s important to understand that SQL Server is still just a software program like Access. I work for a very large company and my SQL Server database is on a machine that is dedicated to acting only as a SQL server and has 26 processors. If you load it on single processor machine in your office...well, who knows how it will perform. Probably about the same as Access.
As for pulling data, there is no reason you can''t use Access. The trick, as NeoPa pointed out, is to use Pass-through queries. By doing this, the SQL Server will process the query and just send results. I have queries written like this that are based on a half million records, are stored in a DB that''s a hundred miles from me, and return results in under 1 second. The only caveats are that you must learn the SQL syntax for SQL server and if you have an functions that you use in your Access queries, you must either find their SQL Server equivalent or write them yourself on the SQL server. It''s worth learning even it sounds painful.
If you choose not to use Pass-through queries, Access doesn''t necessarily request all records as some one stated. Anything in your "WHERE" clause is usually processed server side. Avoid using the "HAVING" clause on your "GROUP BY" fields because that will almost certainly be processed on your side.
Ok, that''s a bit more long-winded than I intended so I''ll leave it at that. If you have any specific questions, I''ll help if I can.
这篇关于从访问升级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
