Trying to Make an Efficient Calendar in Microsoft Access

Question

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.

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