试图在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秒钟，但是一旦我使用真实数据，它开始真的陷入无法接受的地步。我试图通过保持子窗体的源对象为空，直到它们显示，以及不加载recordsource直到这个时候，使这更高效。这有助于使上面的示例运行得非常快，但它仍然不足以实现真正的数据。

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.

所以我想做的是，一种在仍然使用子表单时使其有效的方法，找到代替子表单的另一个控件，或者使用列表框切换子表单，但仍然能够格式化行的颜色。我明白这是不可能的listboxes是，但我是一个程序员，并且愿意尝试子类化列表框这样做，如果它不会浪费我太多的时间。不幸的是，我从来没有做过任何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;

表中的StartDate和EndDate TBL_Booking是预订的开始和结束，Date_Out和Date_In表TBL_Usage是注销的开始和结束。
每次退出通过外键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.

推荐答案

如果有任何人对于我应该采取的方向有什么想法和我可以寻找指导，首先，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.

这表明这里的读者可以忽略这里的一些注释，表明基于脚本或基于文本的解释系统（如HTML）会在某种类型的浏览器渲染系统与具有NEAR直接写入视频显卡的直接能力的windows高性能桌面应用程序相比。

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.

所以这里的真正的问题是如何快速的日历可以做

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个子表单不是问题，是FAST！

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服务器后端OVER STANDARD INTERNET连接到运行在网站上的托管版本的SQL服务器。即使在这种更有限的带宽情况下，日历的加载时间和响应也接近即时。所以性能是没有问题，无论我使用accDB（基于文件）后端，使用SQL服务器为后端，甚至更令人惊讶，并注意到这种形式适用于许多我的客户运行此Access日历OVER常规互联网连接，其中后端是在托管网站上运行的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发布，然后我发布以下视频的日历内置Access在Web浏览器中运行。这个基于浏览器的日历仅使用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

上述视频的结果显示了这个日历应用程序的BUTTER SMOOTH和即时响应的网络版本。

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

现在我应该指出，在上面的基于Web的示例中，我不使用42个子表单，因为在Web浏览器中每个表单是一个单独的框架，并导致重新渲染表单从服务器发送。这意味着基于Access基于42个子窗体的设计是OUT的问题。你将在渲染方面遭受巨大的性能损失（即使没有数据，因为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的解决方案（并且也适用于基于客户端）是填充一个表，其中绑定文本框到该表。因此，具有一个记录显示如上述视频中所示和示出的，这样的结果意味着接近瞬时的响应，并且甚至在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次，并必须RE执行这样的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屋！