使用Access来管理大型数据库 [英] Using Access to manage large database
问题描述
我目前正在使用MS Access + VBA构建报告应用程序，并在公司内部执行特别报告。但是，从2008年开始，我们计划改变源数据库的组织方式，并将更深入地挖掘其中的信息。这将导致数据库的大小相当大幅度增加，很可能我将拥有几个包含5M +行的表。我担心Access无法应对它。它实际上并不是数据库本身的大小，即远离2GB，但我担心使用这些表的计算可能会变得非常慢。
我想知道你的是什么关于这一点的意见以及您将提供的其他选择。我在考虑使用FoxPro来操作数据，然后在Access中使用它进行分析。我认为FoxPro会更快，更灵活，但同时我们无法迁移到FoxPro或任何其他软件，因为有很多人使用相同的数据库，除了MS Office之外，他们没有接受任何其他方面的培训。
所以基本上我正在寻找一种构建自动化应用程序的解决方案，该应用程序可以操作/合并/拆分大型数据库到较小的表中，以后可以用于Access中的分析。重要的是要了解在此阶段为此任务获取单独的服务器是不太可能的，因此所有工作负载都将落在桌面计算机上，数据存储在中央文件服务器上。
希望这能解释这种情况。任何想法都将不胜感激。
谢谢！
JZ
Hi,
I am currently using MS Access + VBA to build reporting applications and also do adhoc reports in the company. However, from 2008 we are planning to change the way the source database is organised and the information in it will be drilled down a bit deeper. This will cause the size of the database to increase rather dramatically and it is quite likely that I will have few tables with 5M+ of rows. I am worried that Access will not be able to cope with it. It is not really about the size of the database itself, i.e. it is far away from 2GB, but I am afraid that calculations using these tables may get unreasonably slow.
I wonder what your opinion regarding this is and what other options you would offer. I was thinking about using FoxPro to manipulate the data before using it for analysis in Access. I assume FoxPro would be faster and more flexible for this but at the same time we cannot migrate to FoxPro or any other software as there are many people using the same databases and they are not trained on anything else except MS Office.
So basically I am looking for a solution to building an automated application which would manipulate/consolidate/split large database into smaller tables which later can be used for analysis in Access. It is important to understand that getting a seperate server for this task would be very unlikely at this stage, so all workload would fall on desktop computers with data being stored on central file servers.
Hope this explains the situation. Any ideas would be greatly appreciated.
Thanks!
JZ
推荐答案
您好，
我目前正在使用MS Access + VBA构建报告应用程序，并在公司内部执行特别报告。但是，从2008年开始，我们计划改变源数据库的组织方式，并将更深入地挖掘其中的信息。这将导致数据库的大小相当大幅度增加，很可能我将拥有几个包含5M +行的表。我担心Access无法应对它。它实际上并不是数据库本身的大小，即远离2GB，但我担心使用这些表的计算可能会变得非常慢。
我想知道你的是什么关于这一点的意见以及您将提供的其他选择。我在考虑使用FoxPro来操作数据，然后在Access中使用它进行分析。我认为FoxPro会更快，更灵活，但同时我们无法迁移到FoxPro或任何其他软件，因为有很多人使用相同的数据库，除了MS Office之外，他们没有接受任何其他方面的培训。
所以基本上我正在寻找一种构建自动化应用程序的解决方案，该应用程序可以操作/合并/拆分大型数据库到较小的表中，以后可以用于Access中的分析。重要的是要了解在此阶段为此任务获取单独的服务器是不太可能的，因此所有工作负载都将落在桌面计算机上，数据存储在中央文件服务器上。
希望这能解释这种情况。任何想法将不胜感激。
谢谢！
JZ
Hi,
I am currently using MS Access + VBA to build reporting applications and also do adhoc reports in the company. However, from 2008 we are planning to change the way the source database is organised and the information in it will be drilled down a bit deeper. This will cause the size of the database to increase rather dramatically and it is quite likely that I will have few tables with 5M+ of rows. I am worried that Access will not be able to cope with it. It is not really about the size of the database itself, i.e. it is far away from 2GB, but I am afraid that calculations using these tables may get unreasonably slow.
I wonder what your opinion regarding this is and what other options you would offer. I was thinking about using FoxPro to manipulate the data before using it for analysis in Access. I assume FoxPro would be faster and more flexible for this but at the same time we cannot migrate to FoxPro or any other software as there are many people using the same databases and they are not trained on anything else except MS Office.
So basically I am looking for a solution to building an automated application which would manipulate/consolidate/split large database into smaller tables which later can be used for analysis in Access. It is important to understand that getting a seperate server for this task would be very unlikely at this stage, so all workload would fall on desktop computers with data being stored on central file servers.
Hope this explains the situation. Any ideas would be greatly appreciated.
Thanks!
JZ
如果你的计算是您可能希望考虑升级到访问前端数据库将连接到SQL Server后端的Access数据项目。您的用户将保持熟悉Microsoft Access，而处理本身将在驻留在您现有的一个文件服务器（最好是最强大的文件服务器）上的SQL Server环境（Microsoft SQL Server 2000桌面引擎）中完成。您不需要完整版本的SQL Server，因为Engine本身附带了更高版本的Access，并且该过程对用户来说相对透明。以下是Microsoft对Access Data Projects的简要说明：
If your calculations are going to be that intensive and varied, you may wish to consider Upsizing to an Access Data Project in which Access Front End DBs would connect to SQL Server Back Ends. Your Users would maintain their familiarity with Microsoft Access, while the processing itself would be done within a SQL Server environment (Microsoft SQL Server 2000 Desktop Engine) residing on one of your existing File Servers (preferably the most powerful). You will not need the full blown version of SQL Server since the Engine itself is shipped with higher versions of Access, and the process would be relatively transparent to the Users. Here is a brief explanation of Access Data Projects by Microsoft:
Microsoft Access项目（.adp）是一个Access数据文件，提供高效的本机模式访问Microsoft SQL Server数据库通过OLE DB（OLE DB：一种组件数据库体系结构，提供对许多类型的数据源的高效网络和Internet访问，包括关系数据，邮件文件，平面文件和电子表格。）组件体系结构。使用Access项目，您可以像创建文件服务器应用程序一样轻松地创建客户端/服务器应用程序。此客户端/服务器应用程序可以是基于表单和报告的传统解决方案，也可以是基于数据访问页面的基于Web的解决方案（数据访问页面：从Access发布的网页，与网络连接，具有与数据库的连接。在数据访问页面中，您可以查看，添加，编辑和操作存储在数据库中的数据。页面还可以包含来自其他来源的数据，例如Excel。），或两者的组合。您可以将Access项目连接到远程SQL Server数据库，本地SQL Server数据库或SQL Server 2000桌面引擎的本地安装。
A Microsoft Access project (.adp) is an Access data file that provides efficient, native-mode access to a Microsoft SQL Server database through the OLE DB (OLE DB: A component database architecture that provides efficient network and Internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets.) component architecture. Using an Access project, you can create a client/server application as easily as a file server application. This client/server application can be a traditional solution based on forms and reports, or a Web-based solution based on data access pages (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.), or a combination of both. You can connect the Access project to a remote SQL Server database, a local SQL Server database, or a local installation of SQL Server 2000 Desktop engine.
HI再次感谢您的建议！
听起来对我很好，但请原谅我缺乏知识因为我对更高级的数据库相对较新并且有更多（愚蠢的？）问题...
据我所知，我需要将我当前的Access数据库升迁为SQL服务器通过工具 - >数据库实用程序 - >升迁向导，然后将所有未来项目建立在与特定服务器的连接上。我尝试测试它在本地工作的原因，因为我在我的笔记本电脑上安装了MSDE 2000，但是当我使用升迁向导时，它出现了着名的''表被跳过，或导出失败''错误。我尝试使用谷歌搜索它看起来像是一个登录问题（虽然我尝试将登录留空，使用''sa''登录，也登录我用于网络）。我要求提供有关MSDE在机器上安装的配置的信息，因此也许会获得更多运气。
但是，我想知道我是否错过了其他任何内容。关于如何在互联网上使用MSDE，我找不到任何好的手册 - 使用Access 2000升迁工具是非常有用的，提供了一些关于它是如何完成的想法，但它不是世界上最新手友好的文件。所以只是想知道我是否应该知道其他任何事情，例如我需要做一些额外的事情，比如特殊的ODBC设置等吗？
很抱歉也许有太明显的问题，但任何帮助都会非常感激。
谢谢，
JZ
HI again and thanks for advice!
Sounds really good to me, but excuse my lack of knowledge as I am relatively new to more advanced databases and have some more (silly?) questions...
As far as I understand, I will need to upsize my current Access database to SQL Server through Tools -> Database Utilities -> Upsizing Wizard and then base all my future projects on connection to that particular server. I tried testing how it works locally as I have MSDE 2000 installed on my laptop, but when I use the Upsizing Wizard it comes up with the famous ''Table was skipped, or export failed'' error. I tried Googling it and it looks like it is a login problem (though I tried leaving the login blank, using the ''sa'' login, also login I use for network). I requested the information on what configuration MSDE was installed on the machine with, so perhaps will get some more luck then.
However, I wonder if I am not missing anything else. I couldn''t find any good manuals on how to use MSDE on internet - the "Using the Access 2000 Upsizing Tools" was rather useful giving some ideas on how it''s done but it''s not the most newbie-friendly document on earth. So just wondering if there is anything else I should know, e.g. do I need to do something extra such as special ODBC setup, etc?
Sorry for perhaps too obvious questions, but any help would be really appreciated.
Thanks,
JZ
还有一个问题要回FoxPro。
至于据我所知，FoxPro以与Access非常相似的方式工作，需要在进行任何计算之前将表中的所有数据从服务器传输到本地机器。即它也不是服务器端软件，因此与Access相比，它的速度没有太大提高。因此，如果您想要更好的工具与更紧密的数据库集成来构建应用程序而不是提高速度，那么它将更有用。
如果我在上述任何陈述中出错，请纠正我。
谢谢，
JZ
And one more question coming back to FoxPro.
As far as I understand, FoxPro works in a very similar way to Access and needs to transfer all data in the table from server to local machine before making any calculations. I.e. it is nor server-side software and as a result it does not give much improvement in speed as compared to Access. Therefore it is more useful if you want better tools with closer DB integration for building applications but not for improving the speed.
Correct me if I am wrong in any of the statements above.
Thanks,
JZ
这篇关于使用Access来管理大型数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
