Trying to Make an Efficient Calendar in Microsoft

问题:

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:

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.

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

回答1:

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.

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.

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?

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

The following Access calendar of mine renders near instantly.

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.

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.

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

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

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

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.

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

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.

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.

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.

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.

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.

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.

回答2:

A question related to calendars was asked not long ago: 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.

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.

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

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.

Web-enabled controls

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

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

Here is an example of what a sample online calendar looks like inside a WebBrowser control in an Access form:

回答3:

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.

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)

回答4:

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

You run 1 query- fast!

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

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.

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.

回答5:

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