尝试在 Microsoft Access 中制作高效的日历 [英] Trying to Make an Efficient Calendar in Microsoft Access

问题描述

我正在开发一个设备管理系统，前端使用 MS Access .mdb 文件，后端使用 SQL Server 2008.如果需要，我可以将前端转换为 MS Access 2010 文件.

I'm working on an equipment management system using a MS Access .mdb file for the front end, and SQL Server 2008 for the back end. If needed I can convert the front end to a MS Access 2010 file.

我创建了一个日历表单，用户可以在其中查看预订、退出或过期的设备.它看起来像这样:

I created a calendar form, where the users can see what equipment is booked, signed out, or over due. It looks like this:

我使用 42 个子表单制作了这个，不幸的是速度很慢.使用上面显示的数据，加载只需要大约 5 秒，但是一旦我使用真实数据，它就开始真正陷入无法接受的困境.我试图通过将子表单的源对象保持空白直到它们显示出来来提高效率，并且直到此时才加载记录源.这足以使上面看到的示例运行得相当快，但对于真实数据来说仍然不够.

I made this using 42 subforms, which is unfortunately slow. With the data shown above, it only takes about 5 seconds to load, but as soon as I use real data, it starts to really bog down unacceptably. I tried to make this more efficient by keeping the source object of the subforms blank until they are shown, as well as not loading the recordsource until this time. This helped enough to make the example seen above run passably fast, but it still isn't enough for real data.

所以我想做的，要么找到一种方法来提高效率，同时仍然使用子窗体，找到另一个代替子窗体工作的控件，或者用列表框切换子窗体，但不知何故仍然能够格式化行的颜色.我知道这对于列表框来说是不可能的，但我是一名程序员，如果不会浪费我太多时间，我愿意尝试子类化列表框来做到这一点.不幸的是，我从来没有做过任何 vba 子类化，所以我需要指出一些好的资源才能这样做.

So what I would like to do, is either find a way to make this efficient while still using subforms, find another control that works in place of subforms, or to switch the subforms out with listboxes, but somehow still be able to format the colours of the rows. I understand this is impossible with listboxes as is, but I am a programmer, and am willing to try subclassing listboxes to do this if it won't waste too much of my time. Unfortunately I have never done any vba subclassing, so I would need to be pointed to some good resources in order to do so.

每天子窗体的记录源设置代码如下:

The code to set the recordsource of each day subform is as follows:

f("sub" & X & Y).Form.RecordSource = "SELECT * " & _
                                     "FROM QRY_Calendar " & _
                                     "WHERE CDate(StartDate) <= #" & curDate & "# " & _
                                     "AND ((EndDate IS NULL OR CDate(EndDate) >= #" & curDate & "#)" & _
                                     IIf(CDate(curDate) <= Date, " OR ((Date_In IS NULL OR CDate(Date_In) >= #" & curDate & "#) AND Date_Out IS NOT NULL)", "") & ") " & _
                                     "ORDER BY IIF(Date_Out Is Not Null And (Date_In Is Null Or CDate2(Date_In)>=#" & curDate & "#) And CDate2(EndDate)<#" & curDate & "#,0,iif(CDate2(Date_Out)<=#" & curDate & "# And (Date_In Is Null Or CDate2(Date_In)>=#" & curDate & "#),1,2)), ID"

QRY_Calendar 如下所示:

QRY_Calendar looks like this:

SELECT B.ID, Person, Initials, ProjectNum & '-' & ProjectYear & '-' & Format(TaskNum,'000') AS Project, Sign_Out_Code, Value AS Type, StartDate, EndDate, Date_Out, Date_In
FROM (((TBL_Booking AS B INNER JOIN TBL_Person AS P ON B.PersonID = P.ID) INNER JOIN LKUP_List AS T ON B.EquipTypeID = T.ID) LEFT JOIN TBL_Usage AS U ON B.ID = U.BookingID) LEFT JOIN TBL_Equipment AS E ON U.Equipment_ID = E.ID;

表 TBL_Booking 中的 StartDate 和 EndDate 是预订的开始和结束，表 TBL_Usage 中的 Date_Out 和 Date_In 是注销的开始和结束.每个注销都通过外键 BookingID 链接到预订.如果 Date_In 为 NULL，则表示设备当前已注销.

StartDate and EndDate in the table TBL_Booking are the beginning and end of a booking, and Date_Out and Date_In in the table TBL_Usage are the beginning and end of a sign out. Each sign out is linked to a booking through the foreign key BookingID. If Date_In is NULL, that means that the equipment is currently signed out.

LKUP_List 是我几年前开始工作之前的一个名称不佳的表，我从来没有费心去改变它.它包含设备类型的列表(除其他外).预订是针对设备类型而非特定项目的，当用户注销其设备时，会在 TBL_Usage 中创建一条与特定设备相关联的记录.

LKUP_List is a poorly named table from before I started working on this years ago that I never bothered to change. It contains a list (among other things) of equipment types. Bookings are for equipment types and not specific items, and when a user signs out their equipment, a record in TBL_Usage is created which is linked to a specific piece of equipment.

如果有人对我应该采取哪个方向以及我可以在哪里寻求指导有任何想法，我将不胜感激.

If anyone has ideas on which direction I should take with this and where I can look for guidance it would be much appreciated.

推荐答案

首先，42个子表单的加载一个访问表单是非常快的，事实上我已经做了很多年了，而且加载42个子形式的时间实际上是瞬时的.

First of all, the loading of 42 sub forms an access form is extremely fast, and in fact I've been doing this for years and years and the load time of 42 sub forms is in fact instantaneous.

因此，这表明这里的读者可以忽略这里的一些评论，这些评论表明，与具有 Windows 高性能桌面应用程序相比，基于脚本或基于文本的解释系统(如 HTML)在某种类型的浏览器渲染系统中运行速度更快.NEAR direct 直接写入视频显卡的能力.

This thus suggests that readers here can ignore some comments here suggesting that a script based or text based interpreted systems such as HTML would somehow be faster running inside some type of browser rendering system as compared to a windows high performance desktop application which has NEAR direct ability to write directly to the video graphics card.

请记住，如果您了解 Windows 桌面应用程序可以直接写入视频卡的简单和基本知识，那么很少有人会尝试比较并建议 HTML 中的渲染系统在速度方面有任何真正的希望，如果我们去的话在这里比较两种不同的架构.

Remember if you have the simple and basic knowledge that windows desktop applications can near write directly to video cards then few would attempt to compare and suggest that a rendered system in HTML has any real hope of comparing in terms of speed if we going to compare the two differing architectures here.

所以这里真正的问题是 can 日历的运行速​​度有多快，42 个子表单是否会成为问题?

So the real issue here is how fast the can calendar can be made to run and will 42 sub forms be an issue?

答案很简单，42 个子表单不成问题，而且速度很快！

The answer is simply that 42 sub forms is not a problem and are FAST!

我的以下 Access 日历几乎立即呈现.

The following Access calendar of mine renders near instantly.

即使在生产环境中，我的上述 Access 日历也已使用多年.即使日历每天都有更多无法显示在屏幕上的数据，加载时间也是即时的.其中很多正在运行，其中桌面(客户端)通过标准互联网连接到在网站上运行的 SQL Server 的托管版本访问 SQL Server 后端.即使在这种带宽更有限的情况下，日历的加载时间和响应也几乎是即时的.因此，无论我是否使用 accDB(基于文件)后端、使用 SQL 服务器作为后端，甚至更令人惊奇的是，该表单都可以很好地与我的许多通过常规 Internet 运行此 Access 日历的客户配合使用后端是在托管网站上运行的 SQL 服务器的连接.而且我什至有一个使用 SharePoint(列表)后端运行的版本，它再次运行没有问题和明显的延迟.

The above Access calendar of mine has been use for years even in production environments. Even if the calendas has each day with MORE data that cannot fit on the screen it is instanct in load time. A good number of these are running in which the desktop (client) is hitting a SQL server backend OVER STANDARD INTERNET connections to a hosted version of SQL server running on a web site. And even in this more limited bandwidth case the load time and response of the calendar is near instant. So performance is without an issue regardless if I using an accDB (file based) back end, using SQL server for the back end, and even more amazing and as noted the form works well with many of my customers running this Access calendar OVER regular internet connections in which the back end is SQL server running on a hosted web site. And I even have a version running with a SharePoint (list) back end and again it runs without issue and noticeable delay.

上面的设计有 42 个子表单，正如没有数据所指出的，子表单几乎是即时加载的.说明这一点很重要，因此我提供了一些现实世界和事实证据来贬低那些显然不掌握和理解基本计算机体系结构的人在这里发表的其他评论.因此，这些人会建议加载 42 个子表单在某种程度上会降低软件速度，而事实上我可以很容易地证明情况并非如此.因此，这里其他人的证人和证词可以被证明是没有价值的，因此这种观点是基于对计算机的基本操作如何在我们的行业中运作缺乏了解.HTML 无法与这里的这种设置进行比较.

The above design has 42 sub forms, and as noted with no data the sub forms load absolutely near instant. It is important that state this and thus I have provided some real world and factual evidence to disparage the other comments made here by those who clearly do not grasp and understand basic computer architecture. These people would thus suggest that the loading of 42 sub forms is somehow in issue in terms of slowing down the software when in fact I can easily demonstrate this is not the case. As such the witness and testimony of others here can be shown to be without merit and as such this view is based on LACK of understanding of how the basic operations of computers work in our industry. HTML cannot hope to compare to such a setup here.

说到基于 Web 的 Access 允许 Web 发布，然后我发布以下视频，其中包含在 Web 浏览器中运行的 Access 内置日历.这个基于浏览器的日历仅使用 Access 构建，没有任何第三方工具.

And speaking of web based now that Access allows web publishing then I post the following video of a Calendar built in Access that runs in a web browser. This browser based Calendar was built ONLY using Access and without any third party tools.

http://www.youtube.com/watch?v=AU4mH0jPntI

上述视频的结果显示了此日历应用程序的流畅且即时响应的基于 Web 的版本.

The result of the above video shows a BUTTER SMOOTH and instantly responsive web based version of this Calendar application.

现在我应该指出，在上述基于 Web 的示例中，我没有使用 42 个子表单，因为在 Web 浏览器中，每个表单都是一个单独的框架，并导致重新呈现从服务器发送的表单.这意味着对于基于 42 个子表单的设计的 Access web 是不可能的.您将在渲染方面遭受巨大的性能损失(即使没有数据，因为 XMAL 表单是按需加载以节省时间，但在这种情况下，这种设置会受到伤害).

Now I should point out that in the above web based example I do not use 42 sub forms since in a web browser each form is a separate frame and causes a re-rendering of the form that is send from the server. This means for Access web based a design based on 42 sub forms is OUT of the question. You will suffer a huge performance hit in terms of rendering (even if no data since the XMAL form is loaded on demand to save time, but in this case this setup hurts).

但是，正如视频所示，基于 Web(也适用于基于客户端)的解决方案是填写一个表格，在该表格中将文本框绑定到该表格.因此，具有一个记录显示正如上面的视频中所指出的那样，并且显示了这样的结果意味着接近即时的响应，并且即使在网络浏览器中也是如此.

However as the video shows the solution for web based (and would also work for client based) was to fill out a table in which you bind the text boxes to that table. Thus having one record display is as noted and shown in the above video shows that such a result means near instantaneous response and as noted even in a web browser.

我强调基于 WEB 的应用程序，因为该视频仅使用 Access 构建，没有其他工具.

I stress the WEB based application in that that video was built only using Access and no other tools.

现在回到性能问题和基于客户端的应用程序.当然，我们现在知道加载 42 个子表单不是问题.

Now getting back to the performance issues and a client based application. The problem of course as we NOW KNOW that loading 42 sub forms is not an issue.

问题当然是运行 42 个单独的 SQL 查询，使用各种表达式将数据提取到这些子表单中，这是瓶颈和性能下降的地方.因此，如果我们使用 42 个文本框，甚至 42 个列表框，这个性能问题不会改变.

The issue of course is running 42 separate SQL queries with all kinds of expressions to pull data into those sub forms is where the bottleneck and slow performance will occur. As such this performance issue will NOT change if we use 42 text boxes, or even 42 listboxes.

所以问题在于尝试执行 42 个单独的 SQL 查询.请记住，每个 SQL 查询都需要时间来解析、检查语法，然后构建查询计划等.事实上，在数据甚至开始为那个给定查询流动之前，必须发生相当多的操作.事实上，我发现一个查询在带宽方面可能是大约 10,000 行数据流的成本.

So the issue is that of attempting to execute 42 separate SQL queries. Keep in mind that each SQL query takes time to parse, time to check for syntax, and then query plans etc. are built. In fact a rather large number of actions have to occur BEFORE data even starts to flow for that one given query. I in fact find that one query can be the cost of about 10,000 rows of data flow in terms of bandwidth.

基于以上信息，我设计的这 42 个子表单之所以可以立即加载和执行，是因为我只执行一个查询来返回整个月的数据.换句话说，我使用显示的开始日期和结束日期执行查询.然后，我运行 VBA 代码将生成的 reocrdset 中的数据处理为子表单 1 到 42.因此，VBA 代码将生成的记录集数据填充到 42 个子表单中.所以这是这里的关键概念和建议，以确保高性能计算并且不会降低速度.

Based on the above information, the reason why my with my design those 42 sub forms can load and perform instantaneous is due to the fact that I execute ONLY ONE QUERY to return the data for the whole month. In other words I execute a query with the start date and end date for the display. I then run VBA code to process that data from the resulting reocrdset into sub form 1 to 42. So VBA code stuffs the resulting record set data into the 42 sub forms. So this is the key concept and suggestion here to ensure high performance computing and not having a slowdown.

总结和总结:

性能瓶颈不是使用 42 个子表单，而是拥有 42 个记录集和 42 个查询，并且可能需要计算 42 次额外的代码和表达式.消除 42 次查询和 42 次并不得不重新执行此类 SQL 语句，这个瓶颈将几乎消失.

The performance bottleneck is not that of using 42 sub forms, but that of having 42 record sets and 42 queries, and potentially additional code and expressions having to be evaluated 42 times. Eliminate the 42 queries and the 42 times and having to RE execute such SQL statements and this bottleneck will pretty much evaporate.

我敢说，使用 42 个列表框，甚至只是 42 个文本框并继续执行 42 条这样的 SQL 语句，不会产生任何有价值的性能改进.

I dare say that using 42 list boxes, or even just 42 text boxes and continuing to execute 42 such SQL statements will not yield any worthwhile improvements in performance.

这篇关于尝试在 Microsoft Access 中制作高效的日历的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！