vba 尝试在 Microsoft Access 中制作高效的日历

Trying to Make an Efficient Calendar in Microsoft Access

提问by NinjaMeTimbers

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.

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

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

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

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.

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

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.

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

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 looks like this:

QRY_Calendar 看起来像这样：

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 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.

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

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.

LKUP_List 是我几年前开始工作之前的一个名字很差的表，我从来没有费心去改变过。它包含设备类型列表（除其他外）。预订是针对设备类型而不是特定项目，当用户注销他们的设备时，会在 TBL_Usage 中创建一条记录，该记录链接到特定设备。

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

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

采纳答案by Albert D. Kallal

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.

首先，加载42个子表单访问表单非常快，实际上我已经这样做了很多年，42个子表单的加载时间实际上是瞬时的。

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.

因此，这表明这里的读者可以忽略这里的一些评论，这表明与具有 NEAR 直接能力的 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.

请记住，如果您具有 Windows 桌面应用程序几乎可以直接写入视频卡的简单和基本知识，那么如果我们要比较这里有两种不同的架构。

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!

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

The following Access calendar of mine renders near instantly.

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

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.

我的上述 Access 日历即使在生产环境中也已使用多年。即使日历每天都有更多无法显示在屏幕上的数据，但它在加载时间中是即时的。其中很多正在运行，其中桌面（客户端）通过标准互联网连接到 SQL 服务器后端，连接到在网站上运行的托管版本的 SQL 服务器。即使在这种带宽更有限的情况下，日历的加载时间和响应也几乎是即时的。因此，无论我是否使用 accDB（基于文件的）后端，使用 SQL 服务器作为后端，甚至更神奇的是，该表单都适用于我的许多通过常规互联网运行此 Access 日历的客户，因此性能没有问题后端是在托管网站上运行的 SQL 服务器的连接。

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.

上面的设计有 42 个子表单，正如没有数据所指出的，子表单绝对接近即时加载。陈述这一点很重要，因此我提供了一些真实世界和事实证据来贬低那些显然不掌握和理解基本计算机体系结构的人在这里发表的其他评论。因此，这些人会建议加载 42 个子表单在降低软件速度方面存在某种问题，而实际上我可以很容易地证明情况并非如此。因此，这里其他人的证人和证词可以被证明是没有根据的，因此这种观点是基于对计算机的基本操作如何在我们的行业中工作的缺乏了解。HTML 不能与这里的这种设置相提并论。

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.

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

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

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

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

上述视频的结果显示了此日历应用程序的黄油平滑和即时响应的基于 Web 的版本。

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 的示例中，我没有使用 42 个子表单，因为在 Web 浏览器中，每个表单都是一个单独的框架，并且会导致重新呈现从服务器发送的表单。这意味着对于基于 Access web 的设计来说，基于 42 个子表单的设计是不可能的。您将在渲染方面遭受巨大的性能损失（即使没有数据，因为 XMAL 表单是按需加载以节省时间，但在这种情况下，此设置会受到影响）。

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 的解决方案（也适用于基于客户端）是填写一个表格，您可以在其中将文本框绑定到该表格。因此，如上述视频所示，具有一个记录显示表明这样的结果意味着近乎瞬时的响应，并且即使在网络浏览器中也是如此。

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

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

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

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 查询以将数据拉入这些子表单，这是瓶颈和性能下降的地方。因此，如果我们使用 42 个文本框，甚至 42 个列表框，这个性能问题不会改变。

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 个单独的 SQL 查询。请记住，每个 SQL 查询都需要时间来解析、检查语法，然后构建查询计划等。事实上，在数据甚至开始为该给定查询流动之前，必须执行相当多的操作。我实际上发现，就带宽而言，一个查询可能是大约 10,000 行数据流的成本。

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 个子表单可以加载并立即执行的原因是因为我执行 ONLY ONE QUERY 以返回整个月的数据。换句话说，我使用显示的开始日期和结束日期执行查询。然后我运行 VBA 代码来处理结果 reocrdset 中的数据到子表单 1 到 42 中。因此 VBA 代码将结果记录集数据填充到 42 个子表单中。所以这是这里的关键概念和建议，以确保高性能计算和不减速。

So in summary and conclusion:

所以总结和结论：

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 个查询，并且可能需要评估 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.

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

回答by Renaud Bompuis

A question related to calendars was asked not long ago: Creating a 'calendar matrix' in Access

不久前问了一个关于日历的问题：Creating a 'calendar matrix' in Access

That said, you're probably never going to achieve good performance with 43 subforms bound to non-trivial queries.

也就是说，使用 43 个绑定到非平凡查询的子表单，您可能永远不会获得良好的性能。

Minor possible improvement

微小的可能改进

You're not saying if your data is on a backend server, in which case each subform has to fetch data across the network.
If that's the case, you may be better off doing one query to the server to pull all the data and cache it in the front end. You would then only have to do simple filtering on a local table, which should be be faster, although the 42 subforms are probably going to be a big bottleneck to performance.

您并不是说您的数据是否在后端服务器上，在这种情况下，每个子表单都必须通过网络获取数据。
如果是这种情况，最好向服务器执行一次查询以提取所有数据并将其缓存在前端。然后您只需要对本地表进行简单的过滤，这应该会更快，尽管 42 个子表单可能会成为性能的一大瓶颈。

A simple INSERT INTOquery could get you started, provided you have created a local table called myCacheTablebased on the returned data from your normal query.

一个简单的INSERT INTO查询可以帮助您入门，前提是您已经创建了一个myCacheTable基于正常查询返回的数据调用的本地表。

Lightweight controls

轻量级控件

The first thing you should probably try, it to use the venerable listbox.
It is fairly lightweight and there are many ways to configure them.
If you combine that with caching data from the server as I mentioned above, you could get better performance.

您可能应该尝试的第一件事是使用古老的listbox.
它相当轻量级，并且有很多方法可以配置它们。
如果您将其与我上面提到的从服务器缓存数据相结合，您可以获得更好的性能。

Web-enabled controls

启用 Web 的控件

As HelloWmentioned, it may be a good idea to simply use textboxes set to TextFormat = RichTextand supply them with simple HTML (it doesn't support much) to format the data inside:

正如HelloW 所提到的，简单地使用设置为的文本框TextFormat = RichText并为它们提供简单的 HTML（它不支持太多）来格式化其中的数据可能是一个好主意：

Full-on web page

完整的网页

Maybe a bit more complex to setup, but difficult to beat in terms of UI, could be to use an existing Javascript library like FullCalendar, or inject your own html directly into the browser document (you could use simple <table>to format the calendar).

设置可能有点复杂，但在 UI 方面很难被击败，可以使用现有的 Javascript 库，如FullCalendar，或者将您自己的 html 直接注入浏览器文档（您可以使用 simple<table>来格式化日历）。

Here is an example of what a sample online calendarlooks like inside a WebBrowsercontrol in an Access form:

下面是一个示例在线日历WebBrowser在 Access 表单中的控件中的外观示例：

回答by Alexey

Another solution would be to make many textbox controls, each one representing the record in the calendar day.

另一种解决方案是制作许多文本框控件，每个控件代表日历日中的记录。

You run 1 query- fast!

您运行 1 个查询 - 快！

In VBA cycle through each textbox control and assign to it horizontal and vertical position, content (value), format, visibility (you do not need to show ones without data).

在 VBA 中循环遍历每个文本框控件并为其分配水平和垂直位置、内容（值）、格式、可见性（您不需要显示没有数据的内容）。

Down side is you have to decide in advance how many text box controls you need and may be it will be not enough for all records delivered by query.

不利的一面是您必须提前决定您需要多少个文本框控件，并且可能不足以满足查询提供的所有记录。

To overcome this problem you could arbitraryly decide that you have 100 controls only first showing more important records from your recordset (ex. Overdue), and making a note that "not all records shown". If user wants to see all records, he clicks particular date, and more detailed subform opens, which shows all records for this date.

为了克服这个问题，您可以任意决定您有 100 个控件，仅首先显示记录集中更重要的记录（例如过期），并注明“未显示所有记录”。如果用户想要查看所有记录，他单击特定日期，将打开更详细的子表单，其中显示该日期的所有记录。

I never tried in the calendar, but I have a form with Gantt chart, showing time bars. Each timebar is a textbox control. I have 120 controls and it works instantly.

我从来没有在日历中尝试过，但我有一个带有甘特图的表格，显示时间条。每个时间栏都是一个文本框控件。我有 120 个控件，它可以立即工作。

回答by HelloW

I agree with the idea that this will work best in HTML.
Replace each subform with a text box formatted for rich text. Then on form load (or some other event ) get a recordset for the month and loop through it adding the text to each text box. The HTML subset available is usually enough to get most of the formatting that you need.

我同意这在 HTML 中效果最好的想法。
用格式为富文本格式的文本框替换每个子表单。然后在表单加载（或其他一些事件）上获取该月的记录集并循环遍历它，将文本添加到每个文本框。可用的 HTML 子集通常足以获得您需要的大部分格式。

Considerations

注意事项

1. This will take some real thinking as far as the vba goes. Most likely you are fine with this since you have already gotten this far.
2. There is only one query so the speed can be more acceptable
3. Your key HTML tags will be <br>and the color tags
4. You won't be able to change the background color just the text color (I may be wrong here)

1. 就 vba 而言，这需要一些真正的思考。很可能你对此没问题，因为你已经做到了这一点。
2. 只有一个查询所以速度可以更容易接受
3. 您的关键 HTML 标签将是<br>和颜色标签
4. 您将无法仅更改文本颜色的背景颜色（我可能在这里错了）

回答by Trevix

Another solution is to use Tcal, which I just updated to version 1.4.2 What is Tcal? Tcal is a Client-Server cross platform calendar with a complete graphic interface. Using TcalServer and TCP/IP, Tcal records events, deadlines and working hours to your company personalized FileMaker Pro, Microsoft Access or MySQL database files. You can allocate resources and job names to your events, see other resource calendars, accept or decline invitation. Tcal works on Mac OSX and Window (7 or XP) and is FREE, for max 2 connected users. You can find Tcal here: http://www.tcal.it/eng/index.html

另一个解决方案是使用 Tcal，我刚刚将其更新到 1.4.2 版 什么是 Tcal？Tcal 是一个具有完整图形界面的客户端-服务器跨平台日历。使用 TcalServer 和 TCP/IP，Tcal 将事件、截止日期和工作时间记录到您公司的个性化 FileMaker Pro、Microsoft Access 或 MySQL 数据库文件中。您可以为您的活动分配资源和工作名称、查看其他资源日历、接受或拒绝邀请。Tcal 适用于 Mac OSX 和 Window（7 或 XP），并且是免费的，最多可供 2 个连接用户使用。你可以在这里找到 Tcal：http://www.tcal.it/eng/index.html