后端访问DB&喷气发动机 [英] Back End Access DB & Jet Engine

问题描述

大家好，


感谢很多人的好运和帮助，我即将完成我的第一个数据库/应用程序。


但目前Jet Engine后端无法处理负载


这里有一些规格我的数据库：

后端：访问 - >喷气发动机

前端：接入 - >喷气发动机

通常不超过2个用户访问数据库，后端存储在一台用户计算机上，这是一台相当新的计算机。


以下是我认为可能导致问题的一些结构性错误：


1.每当我添加新记录时，我都会使用ADO连接到整个表作为记录集，我会认为这将给Jet后端带来不必要的负担，将最新记录添加到记录集的最简化方法是什么。


2.我有外部查询对象（在前端），外部查询对象会在我的前端加载更多负载然后VBA中的SQL字符串会吗？


非常感谢

Hi guys,

Thanks to a lot of luck and help from you great people, I am on the verge of completing my first Database/Application.

but at the moment the Jet Engine back end is not able to handle the load

here are some specs re my database:
Back End: Access -> Jet Engine
Front Ends: Access -> Jet Engine
usually not more than 2 users accessing the database and the back end is stored on one of the users computers which is quite a new computer.

Here are some structural mistakes I believe that could be causing the problem:

1. Whenever I add a new record, I use ADO to connect to an entire table as a recordset, I would think that would be putting unnecessary load on the Jet back end, what is the most streamlined way to add a new record to a recordset.

2. I have external query objects (in the front ends), will external query objects put more load on my front end then SQL strings in VBA would?

Thank You Very Much

我不确定问题是什么，不过我认为你确实错了。

Access数据库Back End从不使用Jet Engine或其他任何东西 - 它只是一个文件服务器。所有数据都在前端处理。因此，当你有一个大表并运行查询来选择几条记录时，无论如何必须将整个表转移到前端。

I am not sure what the problem is, however I think you do take one thing wrong.
Access database Back End never uses Jet Engine or anything else - it''s just a file server. All data is processed at the Front End. So when you have a big table and you run a query to select a few records, the whole table must be transfered to the Front End anyway.

推荐答案

1. 打开表通常不会加载所有数据。这不是问题。让Access处理它。
2. 如果你的前端是QueryDefs，那么使用它们比使用VBA SQL字符串更好。前者在首次使用时被优化。 SQL字符串需要在每次运行时都进行优化猜测。 Jet Engine将在Back End数据库上非常开心地工作。

PS。我不想与其他专家发生冲突（对迪马这么道歉）但我觉得我在这个场合是对的。如果我需要学习一些东西，我会很高兴被证明是错的。我可能会请其他专家查看。

PS. I don''t like to contradict other Experts (so apologies for that Dima) but I think I''m right on this occasion. I would be happy to be proved wrong if there''s something I need to learn. I may ask some other Experts to have a look in to check.

让Access处理它。


我必须说，我认为这是本次或任何其他Access论坛上提供的最好的建议之一！经过这么多年，我仍然惊讶于人们会多久坚持重新发明轮子！当然，Access并不完美;有些事情可以通过滚动你自己在Access中做得更好。但总的来说，如果Access提供它，它会比编写自己的hack更经济！我们多少次阅读过一篇帖子，询问如何使用ID \Client Name \ Company Name？检索某条记录？为此目的存在一个组合框选项！更令我惊讶的是，有人会回应告诉OP使用组合框，然后继续提供半页代码来完成任务，当Combobox向导将在大约60秒内完成同样的事情！ br />

对于The Pond这边的每个人来说，都有一个伟大的阵亡将士纪念日！还有一个特别的谢谢！对那里的所有退伍军人来说，无论你在哪个方面，即使是那些Down Under！

"Let Access handle it."

I have to say that I think this is one of the best pieces of advise that has ever been offered on this or any other Access forum! After all these years, it still amazes me how often people will insist on re-inventing the wheel! Granted, Access isn''t perfect; there are some things that can be done better in Access by "rolling your own," but overall, if Access offers it, it''ll be more economical than writing your own hack! How many times have we all read a post asking "How can I retrieve a certain record using the ID\Client Name\Company Name?" when a combo box option exists for this very purpose! Even more amazing, to me, is that someone will respond telling the OP to use a combobox, and then proceed to give half a page of code to accomplish the task, when the Combobox Wizard will accomplish the same thing in about 60 seconds!

To everyone on this side of The Pond, have a great Memorial Day! And a special "Thank you!" to all the veterans out there, no matter which side of The Pond you''re on, even those "Down Under!"

我倾向于同意两者同意dima69和NeoPa。


如果前端和后端都是Access，那么它将是前端PC。执行所有处理，而后端只是一个共享文件，无论它在哪里。这就是我们为大型项目转到SQL Server的原因，因为这会将大量工作卸载到服务器（后端）并大大减少网络流量。


然而，我的（有限的）经验倾向于支持NeoPa的断言，即打开数据集不一定会读取所有记录，因此不会对前端，后端或网络造成大的负担。


也就是说，除非你正在处理看起来完全改变规则的UNION查询。


在这种情况下，我会建议如果有疑问的，试试看。查找或创建非常大表（数百万条记录）并从前端以预期方式打开它。您很快就能判断您是否正在阅读整个数据集。

I''m inclined to agree partly with both dima69 and NeoPa.

If both front- and back-end are Access, then it will be the "front-end PC" doing all of the processing, while the back-end is simply a shared file, regardless of where it lives. That''s why we go to SQL Server for larger projects, as that unloads a lot of work to the server (back-end) and greatly reduces network traffic.

However, my (limited) experience would tend to support NeoPa in his assertion that opening a dataset does not necessarily read all of the records, and will thus not place a large load on the front-end, back-end or network.

That is, unless you are dealing with UNION queries, which appear to completely change the rules.

In this sort of case, I would suggest "if in doubt, try it out". Find or create a very large table (millions of records) and open it the intended way from the front-end. You will soon be able to tell whether you are reading the entire dataset or not.

这篇关于后端访问DB&喷气发动机的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！