“Expert Oracle Practices: Oracle Database Administration from the Oak Table” Book

November 30, 2009 (Follow-up Jan 1, 3, 4, 6, 8, 9, 12, 15, 20, 23, 28, Feb 12, 14, 16, Mar 21, May 7, Dec 28 2010)

I had the great pleasure of co-writing with Randolf Geist (http://oracle-randolf.blogspot.com/) two (very long) chapters for the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” book.  The chapter titles are “Understanding Performance Optimization Methods” and “Choosing a Performance Optimization Method”. The two chapters explore the vast number of ways to identify Oracle database performance problems, and demonstrate methods of attacking the problems once identified. Some of the techniques demonstrated in the chapters have not previously appeared in book form. The chapters also attempt to address some of the bad advice found when performing web searches on Oracle keywords. This blog, and that of Randolf Geist, provide a good idea of what our two chapters in the book cover – essentially everything from “guessing” based on what one would find through a Google search, to trying to use the buffer cache hit ratio, all the way to reading process stack dumps (and quite a number of other things in between). Several reproducible test cases are included in the chapters, with example output from Oracle 10.2.0.4, 11.1.0.6, 11.1.0.7, and 11.2.0.1.

The book description on the Apress website as well as Amazon seems to be a bit limited compared to the book’s table of contents, although the authors’ descriptions are thorough. Below is the table of contents for the book:

Book Contents

I DBA Fundamentals
1 Battle Against Any Guess – Alex Gorbachev
2 A Partly-cloudy Future – Jeremiah Wilton
3 Developing A Performance Optimization Method – Connie Green, Graham Wood, Uri Shaft
4 The DBA as Designer – Melanie Caffrey

II Network and Operating Systems
5 Running Oracle on Windows – Niall Litchfield

III SQL and PL/SQL
6 Managing SQL Performance – Karen Morton
7 PL/SQL and the CBO – Joze Senegacnik

IV Performance Optimization Methods
8 Understanding Performance Optimization Methods – Charles Hooper & Randolf Geist
9 Choosing a Performance Optimization Method – Randolf Geist & Charles Hooper

IV Operational Concerns
10 Managing the Very Large Database – Tim Gorman
11 Statistics Collection and Creation – Jonathan Lewis

V Troubleshooting
12 Troubleshooting Latch Contention – Riyaj Shamsudeen
13 Measuring for Robust Performance – Robyn Sands

VI Security
14 Securing Users – Pete Finnigan
15 Securing Data – Pete Finnigan

—

May 7, 2010:
An independent review of the Expert Oracle Practices book appeared in the May 2010 edition of the NoCOUG Journal, written by Dave Abercrombie (the same reviewer posted a short review on Amazon’s website).  The review is thorough, and covers items that were missed in my brief chapter summaries that are below.  Most of chapter 1, as well as an interview with the author of chapter 1, also appeared in the NoCOUG Journal.

—

http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684

Late follow-up (January 1, 2010):
I ordered a copy of the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” book from Amazon in the middle of November.  Today I bought an “Alpha” copy of the book from the Apress site so that I would have an opportunity to read the other chapters in the book (and so that I could have an electronic copy of the completed book).  The “Alpha” copy of the book is a formatting mine-field – the victim of Microsoft Word “auto-correcting” document styles, missing mono-spaced fonts in the code sections, and yellow highlighter all over a couple of the chapters (there is a reason for the yellow highlighter all over our two chapters – the cyan highlighter was too hard on the eyes).  The “Alpha” copy, at least for the two chapters that Randolf and I wrote, was captured just before we had an opportunity to address the concerns of the second editor (correction – the technical reviewer had made comments previously, the “Alpha” copy was captured before we had an opportunity to address the concerns of the first editor of the chapters – portions of the chapters were modified heavily based on his feedback) – there were three more rounds of technical edits after those concerns were addressed.  Don’t let the rough edges of the “Alpha” copy color your opinion of the finished product.

—

Late follow-up (Jan 3-28, Feb 12, 14, 16, Mar 21 2010):
Below are descriptions of each chapter of the book.

Chapter 1 (Final Version) – Alex Gorbachev, the founder of Battle Against Any Guess (BAAG Party), identifies examples of attempted problem solving through guess work, throwing the book at the problem, and shooting the problem with silver bullets – as the attempted guess work is suggested in various online forums.  He seems to have a strong bias against guessing and a bias for understanding the actual source of the problem.  In the chapter he attempts to answer the question of why the database is slow every Monday when it rains, but does not address the issue of why another database runs slowly when someone yells at the SAN (there was a serious YouTube video floating around a couple of months ago that demonstrated verbally abusing the SAN reduced its performance).  An interesting issue is raised in the chapter – is not (blindly) implementing a best practice actually a best practice – and is that advice then a circular reference that should be followed, or not followed?

Chapter 2 (Final Version) – Jeremiah Wilton, Amazon.com’s first DBA, provides insight into the meaning of cloud computing, describes the Software as a Service, Platform as a Service, and Infrastructure as a Service variants.  Jeremiah also describes the capabilities of several cloud computing providers, and describes in detail how to set up Amazon.com’s EC2.

Chapter 3 (Final Version) – Connie Green, Graham Wood, and Uri Shaft (you will find two of their names in the spdoc.txt file in the rdbms/admin directory) – some of the developers of the performance tuning and monitoring features in Oracle Database, describe the philosophy behind the development of a sound performance optimization method, debunk mysteries and black magic, and clean up after silver bullets miss their target. The chapter lists five steps to developing a time-based optimization method.  Describes when to use Enterprise Manager, Automatic Database Diagnostic Monitor (extended description), Active Session History (extended description), Automatic Workload Repository/Statspack reports (extended description), and SQL trace.  The final version of the chapter extends beyond the philosophy stage – if the performance is not the same as it was yesterday, something must have changed.  What is the scope of the problem: specific SQL statement, instance wide, or something else?  The section covering the usage of the ASH (Active Session History) data to pinpoint the starting point of performance problems seems to have been extended.  The chapter also describes what the DB time statistic really indicates, and how may it be used for troubleshooting.

Chapter 4 (Final Version) – Melanie Caffrey, Senior Development Manager in the Enterprise Linux group at Oracle, describes the philosophy of being an all-around fantastic, energetic, proactive DBA with the ultimate goal of having your evenings and weekends rarely interrupted by various database problems.  This chapter is not a simple bulleted list of what to do and what not to do – the topics include:

• How a DBA should develop a partnership with the developers to reduce the chances of performance problems when new applications are released for production use.
• Difference between Design Upfront/Develop Later/Waterfall approach, the Agile approach, and the Cowboy approach to development.
• Choosing the correct data type with an example of what could happen when defining character columns as VARCHAR2(4000), potential problems when comparing CHAR columns with VARCHAR2 columns, and cardinality calculation problems when numbers are stored in VARCHAR2 columns.
• Points against database independent design.
• Building integrity and other logic into the database, rather than at the application level.
• Preventing the Oracle database from appearing as a black box for dumping data.
• Recommended Oracle documentation to read.
• Learning from your mistakes.
• Leveraging the Oracle features that are part of the database package – getting your money’s worth from this expensive program while improving performance.

Chapter 5 (Final Version) – Niall Litchfield answers the question: Is Windows a serious platform for Oracle databases?  And if so, what does a Unix DBA/administrator need to know about running Oracle on the Windows platform.  Architecture differences on Windows and Unix-like operating systems (CPU, Memory, Storage, Network) are described.  Using SysInternal’s (Microsoft’s) Process Monitor to drill into Oracle activity for a single session, an overview of the Windows registry, and the Windows Scripting Host are also described.

Chapter 6 (Final Version) – Karen Morton states quite simply to begin at the beginning when trying to control SQL performance.  The author of the SQL in the application must repeat: “I am responsible for the performance of the code I write or maintain.”  The chapter explores some of the differences between EXPLAIN PLAN output and that of DBMS_XPLAN.DISPLAY_CURSOR.  The chapter also provides an introduction to extended SQL traces (event 10046), and why extended SQL traces are better than execution plans generated by DBMS_XPLAN.DISPLAY_CURSOR.  The chapter includes the investigation of a couple SQL performance problems – the case studies show how to detect that a problem is present and provide ideas for rectifying the problem: lack of a good index, skewed data, and SQL that should be rewritten because it is projected to perform poorly as the data volume increases.  (The Final Version of the chapter provides an additional case study that demonstrates the effects of calling a PL/SQL function from SQL, shows that the timings in DBMS_XPLANs may be deceptive when such functions are called, and shows how to determine the actual impact of context switches caused by combining SQL with PL/SQL.)

Chapter 7 (Final Version) – Joze Senegacnik explains the stages of a SQL statement hard parse; defines selectivity, cardinality, and cost; then unravels the mysteries of bad execution plans caused by PL/SQL functions that are present in the WHERE clause – and as a bonus explains how to correct such problems.

Chapter 8 (Final Version) – Charles Hooper and Randolf Geist explain that there is so much instrumentation (to help optimization) in an Oracle RDBMS that it is difficult to determine which instrumentation method is most appropriate, especially if the available instrumentation methods are not well understood.  This chapter attempts to cover a large number of the instrumentation and optimization methods including:

• Blindly changing system parameters based solely on information found on the Internet or books.
• Monitoring and reacting to the buffer cache hit ratio, or another similar ratio.
• Monitoring the delta (change) values of system and session statistics (V$SYSSTAT, V$SESSTAT, etc.).
• Monitoring file related activity (V$FILESTAT, V$TEMPSTAT, V$SEGSTAT, various system and session statistics, etc.).
• Monitoring the delta values of system and session wait events (V$SYSTEM_EVENT, V$SESSION_EVENT, V$SESSION_WAIT, etc.).
• Monitoring the CPU utilization (CPU used by this session system and session statistics, statistics from V$OSSTAT, statistics from V$SYS_TIME_MODEL and V$SESS_TIME_MODEL, etc.).
• Capturing Statspack snapshots and creating Statspack or AWR reports.
• Monitoring delta values of the statistics associated with each SQL statement in the library cache (CPU_TIME, ELAPSED_TIME, FETCHES, EXECUTIONS, BUFFER_GETS, DISK_READS, etc. in V$SQL).
• Examining plan statistics and changes in execution plans for SQL statements (V$SQL_PLAN_STATISTICS_ALL, V$SQL_WORKAREA_ACTIVE, V$SQL_SHARED_CURSOR, V$SQL_PLAN, DBMS_XPLAN.DISPLAY_CURSOR).
• Examining system and session level optimizer parameters affecting calculations performed while generating execution plans with the lowest calculated cost (V$SYS_OPTIMIZER_ENV, V$SES_OPTIMIZER_ENV).
• Generating 10053 cost based optimizer traces during hard parses in order to determine the calculated reason for an execution plan.
• Generating 10046 extended SQL traces at levels 4, 8, or 12 during the execution of individual SQL statements as well of normal application processes in order to determine specific causes of performance problems within critical application procedures.
• Examining Oracle database server stack traces.
• Reviewing the ADDM findings (with appropriate Enterprise Edition license and extra cost license options).
• Examining network packets and associated statistics (tcpdump, Wireshark/Ethereal, etc.).
• Examining client-side traces (sqlnet, Process Monitor, Spy++, etc.)
• Investigating enqueue type waits
• (And probably a couple other things along the way.)

Additionally, a couple of interesting test cases are introduced:

• Selecting 0.06% of the rows from a table (containing 100,000,000 rows) completes faster using a full table scan instead of an index range scan.
• Generating a deadlock on Oracle 11.1.0.6 through 11.2.0.1, while the exact same sequence of events fails to trigger a deadlock on Oracle 10.2.0.4.
• Executing a query using bind variables in a single session causes more than 1,024 child cursors to be created, resulting in multiple children with the same SQL_ID and CHILD_NUMBER to be created when the SQL statement is executed with 10,000 different sets of bind variable values.
• Head to head comparison of direct I/O compared to operating system buffered I/O with Oracle 11.2.0.1 during one of the test cases (and some of the potential pitfalls of each approach).
• What happens when a CPU load is applied inside and outside the database instance.
• TKPROF in 11.1.0.6 and above potentially shows the wrong Row Source Execution Plan when the same SQL statement is executed multiple times while tracing is enabled.

(Note that through the final proofing stages of the chapters, Chapter 8 was actually Chapter 9, and Chapter 9 was actually Chapter 10.  The opening section for Chapter 8 in the final version of the book states the following: “Chapter 10, which follows next, provides a decision-making process that we believe will help you apply the right method to the right problem.”  The same problem is present in Chapter 9 when it refers back to the concepts introduced in the previous chapter – Chapter 9 states to look at Chapter 9.  If you read the words “Chapter 10”, please interpret that as “Chapter 9”.  If you read the words “Chapter 9”, please interpret that as “Chapter 8”.  A similar problem appears in Chapter 6, which refers to Listing 7-1, Listing 7-2, etc.)
(I just caught a typo in the script at the top of page 186 – I am sure that this typo was corrected in one of the 25-30 edits of our chapters, but that change was somehow lost and not noticed in the final series of edits.  The script shows this “AND SS.OBJ#=DO.DATA_OBJECT_ID AND SS.DATAOBJ#=DO.DATA_OBJECT_ID” – it was supposed to show this “AND SS.OBJ#=DO.OBJECT_ID AND SS.DATAOBJ#=DO.DATA_OBJECT_ID”.  Another typo is found on line 8 in the script on page 218 – change the second TE.DISK_READS to TS.DISK_READS.)
(Mar 21, 2010: The SQL statement on page 188 should have included the following in the WHERE clause to reduce the chances of repeated rows appearing in the output, see the March 22, 2010 blog article for an explanation: AND S.SQL_CHILD_NUMBER=SQL.CHILD_NUMBER – ideally, the query would return the S.SQL_CHILD_NUMBER column also.)

Chapter 9 (Final Version) – Randolf Geist and Charles Hooper use real-world examples to demonstrate when to use the various monitoring methods in pursuit of improved performance, switching between monitoring methods as needed.  The chapter wraps up with various techniques to improve performance based on the data collected during database monitoring.

Chapter 10 (Final Version) – Tim Gorman describes what needs to be considered when managing very large databases.  His chapter is about those massive databases that don’t quite fit onto the 12 drive RAID 10 floppy drive array.  The databases of this variety are huge, growing at a rate of 25TB to 50TB per day.    He describes the raw throughput required to hit this level of growth – it is not a write rate of 600MB/s that is required, but instead one of roughly 10GB/s (OK, how many EXADATA V2 units were required to maintain this write level?). The chapter opens with a story about Tim accidentally dropping a 15TB index on a 45TB table, and learning from one’s mistakes.  And we later learn that “the fastest mass update or delete is actually an insert.”  He describes some of the advantages of using the extra cost partitioning option, and ASM, and also describes some of the challenges of backing up massive databases – at a backup rate of 1,000GB per hour, that 5PB database will be backed up in no time (no time soon – 200 days by Tim’s calculations).  After reading the chapter, I just have one question – is all of that data being generated just to prove that Oracle Databases are able to handle it, or is someone actually reading/using all of the data?

Chapter 11 (Final Version) – Jonathan Lewis attacks the subject of statistics.  This is not a simple chapter that shows how to collect table, index, and system statistics.  It is instead a chapter that exposes some of the problems that cause the cost-based optimizer to generate seemingly silly/stupid execution plans.  Is it caused by a bug, or is there a reason for the execution plan – and if there is a reason, is there a way to identify the source and correct the problem without adding multiple hints to every SQL statement?  In the chapter Jonathan offers a couple of clever, seemingly simple questions, for which most people will initially answer incorrectly – this is done as a demonstration of what potentially trips up the cost-based optimizer.  He also demonstrates how the optimizer could become flustered by the way the index clustering factor is calculated, how both dynamic sampling and  11g’s extended column statistics may be ineffective in certain cases, and how the nightly automatic statistics gathering job could cause problems for partitioned tables.  Also discussed is how a histogram’s impact on cardinality estimates changes when upgrading from Oracle 10.2.0.3 to 10.2.0.4 or 11g.  After reading the chapter you will be aware that to the optimizer, all possible outcomes of an inequality totals 11% of all rows in a row source, where is that proof that 1=2 when you need it?

Chapter 12 (Final Version) – Riyaj Shamsudeen drives into the details of latch contention – this is not a chapter that should be read unless you are fully awake – I think that I might need to re-read the chapter a couple of more times to fully understand what was presented.  The chapter begins with a brief description of latches and how latches differ from enqueues.  Identifying and finding the cause for various types of latch contention is demonstrated through numerous SQL statements that hit Oracle’s various fixed tables and performance views, and he even shows a couple of examples of using TKPROF’s ORADEBUG to dump memory contents for analysis.  The 0 key on the keyword might have been stuck when Riyaj described the SESSION_CACHED_CURSORS parameter.

Chapter 13 (Final Version) – Robyn Sands describes why it is important to measure twice, cut once, and then follow up with a re-measure every so often.  Whether it is a needle in the haystack, a silver rock mixed in with a bucket of sand, or an occasional database reporting problem hiding among normal activity, it is hard to know if you found a needle, a silver rock, or a database performance problem unless you periodically capture the measurements of the items in front of you.  After a brief introduction to what the expectations are of performance analysis, Robyn introduced topics that induced flashbacks of university-level linear algebra, statistics, and even political science while ripping through captured runtime performance statistics in search of the silver rocks (OK, I admit that they were red rocks) that indicate unexpected performance characteristics.

Chapter 14 (Final Version) – Pete Finnigan provides general information about securing database user accounts, stressing the need to eliminate those database user accounts that are installed by default but not used, with the goal of reducing the attack surface.  Pete also introduces several of the scripts that he hosts on his website: use.sql (assess built-in and customer installed user account permissions), find_all_privs.sql (determine the assigned privileges of a user account), cracker-v2.0 (you better hope that access to SYS.USER$ is restricted), and profiles.sql (retrieve the security settings for the user profiles configured in the database).  The author also provides several SQL statements throughout the chapter that may be used for analyzing various security settings.  The detailed description of Oracle’s one way password hashing algorithm is interesting.

Chapter 15 (Final Version) – Pete Finnigan advises “learn to think like a hacker” in this chapter that describes the difficulties of securing data in the database.  Pete introduces a couple more scripts from his website while peeling away the layers of security permissions that permit access to credit card (or other similar data) stored within the database.  He introduces several sources for additional security permissions for seeing the “secured” data – you better hope that bind variables are in use, and that people do not know how to view the bind variables submitted by other sessions.  The author mentions that database security extends to the whole server – don’t leave files containing unencrypted versions of the data stored in files in the filesystem of the server (and if you read chapter 14, you probably have already made up your mind to take down the post-it notes with the DBA passwords).  Auditing is briefly mentioned.

—

January 4, 2010:
The Final Version of the Electronic Book may be downloaded from Apress Now – if you bought the “Alpha” copy of the book, you should be able to download the final version for free.  The PDF is 593 pages including the front cover and index.

January 20, 2010:
I received notification that the printed version of the book shipped from Amazon on January 17, and should arrive by postal mail today.

January 23, 2010:
It appears that the first 100 pages of the book may be viewed on the Google Books site.

February 12, 2010:
Having now read through all of the chapters in the book, at the end of January the book authors discussed the description of the book, as currently listed on Amazon and the Apress site, and determined that it is inaccurate.  First, the page count in not 400, but actually about 532 pages when excluding the index at the back of the book.  Additionally, the bullet points under the heading “What you’ll learn” do not best describe what is in the printed book – those bullets are currently listed as follows:

• Eliminate guesswork by attacking database problems in a systematic manner.
• Manage transactional performance in SQL and PL/SQL.
• Secure your database from outside attack.
• Effectively deal with operating system platforms such as Windows and Linux.
• Diagnose database crashes.
• Choose the right block size to optimize I/O throughput.

I received notification today that Apress will be updating the description of the book contents based on feedback from the book authors.  Expect to see the bullet points changed to something similar to the following, hopefully by Monday:

• Adopt a rational approach to database management
• Add value to your organization as a database professional
• Utilize effective techniques for performance management and optimization
• Effectively exploit different platform technologies
• Effectively secure your organization’s data
• Gain a deeper understanding of database internal features and structures

May 7, 2010
Review of the book appeared in the May 2010 edition of the NoCOUG Journal along with most of chapter 1 of the book.

December 28, 2010
It appears that the scripts for chapter 8 of the book are well hidden.  The scripts may be downloaded from the Apress website.  Inside the Expert_Oracle_Practices_Oracle_Database_Administration_from_the_Oak_Table-4524.zip download file is a folder titled ch08n09_UnderstandingOptMethods, and inside that folder is another folder titled ch09Scripts – this folder contains all of the scripts for chapters 8 and 9 (those scripts are listed at the end of chapter 8 in the book).

November 17, 2011
It appears that the script library file download for this book on the Apress website is corrupt.  You can download the script library from here: Expert_Oracle_Practices_Scripts.zip (save with a .zip extension, and not a .doc extension).

Excel – Graphical Scheduled Usage Viewer for Production Equipment

January 1, 2010

This article is based on a sample included in a presentation that I conducted a couple of months ago.  Essentially, this example pulls utilization schedules from multiple equipment resources and generates both an equipment resource specific calendar and a graphical overview of the calendars for all equipment resources.  This demonstration uses ADO with a direct connection to an Oracle database for both scheduling the work orders that need to be processed at each of the equipment resources (using a very weak scheduling algorithm that does not go back to fill in unused time), and to create the graphical results in Excel.

The options area for the original Excel spreadsheet used in my presentation looked like this, but we will not create anything that complex in this article: 

– 

Before starting, we need to create source data for our example (note that some of the tables created are only needed for a short period of time, and may be dropped once all of the tables are created):

First, a statistic list of parts that will be manufactured:

CREATE TABLE
  T_PART_LIST AS
SELECT
  CAST(DBMS_RANDOM.STRING('U',15) AS VARCHAR2(30)) PART_ID,
  ROUND(DBMS_RANDOM.VALUE(1,30)) NUM_LOTS,
  ROUND(DBMS_RANDOM.VALUE(1,10)) NUM_OPERATIONS
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

Next, the operation numbers and machine resources that will be used during the manufacture of each of the parts:

CREATE TABLE
  T_PART_OPERATION AS
SELECT
  PL.PART_ID,
  OP.RN*10 OPERATION_SEQ_NO,
  CAST(ROUND(DBMS_RANDOM.VALUE(0,10),2) AS NUMBER(22,2)) RUN_HOURS,
  CAST('MACHINE'||TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,100)),'000') AS VARCHAR2(15)) RESOURCE_ID
FROM
  T_PART_LIST PL,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10) OP
WHERE
  OP.RN<=PL.NUM_OPERATIONS;

CREATE INDEX IND_T_PART_OP_PART_ID ON T_PART_OPERATION(PART_ID);

Next, the work order lots that will manufacture the parts:

CREATE TABLE
  T_WORKORDER_LIST AS
SELECT
  WO.WORKORDER_BASE_ID,
  CAST(LN.LOT_NUM AS VARCHAR2(3)) WORKORDER_LOT_ID,
  PL.PART_ID,
  TRUNC(SYSDATE+ROUND(DBMS_RANDOM.VALUE(0,400))) DESIRED_WANT_DATE
FROM
  (SELECT
    PART_ID,
    NUM_LOTS,
    ROWNUM RN
  FROM
    T_PART_LIST) PL,
  (SELECT
    CAST('W'||TO_CHAR(ROWNUM+10000) AS VARCHAR2(15)) WORKORDER_BASE_ID,
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=400) WO,
  (SELECT
    ROWNUM LOT_NUM
  FROM
    DUAL
  CONNECT BY
    LEVEL<=30) LN
WHERE
  PL.RN=WO.RN
  AND LN.LOT_NUM<=PL.NUM_LOTS
ORDER BY
  WO.WORKORDER_BASE_ID,
  LN.LOT_NUM;

Finally, a table that will eventually contain the schedules for the various machine resources:

CREATE TABLE T_RESOURCE_DAILY_SCH(
  RESOURCE_ID VARCHAR2(15),
  START_DATE DATE,
  FINISH_DATE DATE,
  RUN_HOURS NUMBER,
  PART_ID VARCHAR2(30),
  WORKORDER_BASE_ID VARCHAR2(15),
  WORKORDER_LOT_ID VARCHAR2(3),
  OPERATION_SEQ_NO NUMBER,
  DESIRED_WANT_DATE DATE,
  UNIT_ASSIGNED NUMBER);

CREATE INDEX IND_T_RESOURCE_DAILY_RES ON T_RESOURCE_DAILY_SCH(RESOURCE_ID);

We now switch to Excel to build the interface area with two ActiveX command buttons:

First, name the first worksheet as ShopCalendar  – this name is very important since it is referenced in the code for this example.  Next, add two ActiveX command buttons in the first two rows.  Give the first one the name cmdCreateSchedule, and the second one the name cmdDrawCalendar  – the names are not overly critical, but the names are referenced in the code.  Finally, double-click one of the buttons to access the Code Editor.

Delete any code that is shown in the Code Editor and add the following code:

Option Explicit

Dim dbDatabase As New ADODB.Connection 
Dim strDatabase As String
Dim strUserName As String
Dim strPassword As String
Private Function ConnectDatabase() As Integer
    Dim intResult As Integer

    On Error Resume Next

    If dbDatabase.State <> 1 Then
        'Connection to the database if closed
        strDatabase = "MyDB"
        strUserName = "MyUser"
        strPassword = "MyPassword"

        'Connect to the database
        'Oracle connection string
        dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

        dbDatabase.ConnectionTimeout = 40
        dbDatabase.CursorLocation = adUseClient
        dbDatabase.Open

        If (dbDatabase.State <> 1) Or (Err <> 0) Then
            intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")

            ConnectDatabase = False
        Else
            ConnectDatabase = True
        End If
    Else
        ConnectDatabase = True
    End If
End Function

The above code is the basic logic needed to permit connecting to the Oracle database.  Next, add a reference to the ActiveX Data Objects.  Now we need to add the code for the scheduling routine – this code uses bind variables to minimize the number of hard parses:

Private Sub cmdCreateSchedule_Click()
    Dim intResult As Integer
    Dim strSQL As String
    Dim i As Integer
    Dim intResourceIndex As Integer      'Index that indicates the current resource ID index
    Dim strResourceID(100) As String     'Keep track of the new resource IDs read in
    Dim varResourceLastDate(100) As Date 'Keep track of the last date for the resource
    Dim strWorkOrderLast As String       'Used to make certain that the next operation starts after the previous
    Dim varWorkOrderLastDate As Date     'Used to make certain that the next operation starts after the previous
    Dim varScheduleStart As Date         'The first date in the schedule
    Dim snpData As ADODB.Recordset
    Dim comData As ADODB.Command

    intResult = ConnectDatabase
    If intResult = False Then
        Exit Sub
    End If

    'Set the starting point for all resources
    varScheduleStart = Now
    For i = 1 To 100
        strResourceID(i) = ""
        varResourceLastDate(i) = varScheduleStart
    Next i

    Set snpData = New ADODB.Recordset
    Set comData = New ADODB.Command

    'Retrieve the list of work order operations to be scheduled
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  WO.WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WO.WORKORDER_LOT_ID," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID," & vbCrLf
    strSQL = strSQL & "  WO.DESIRED_WANT_DATE," & vbCrLf
    strSQL = strSQL & "  PO.OPERATION_SEQ_NO," & vbCrLf
    strSQL = strSQL & "  PO.RUN_HOURS," & vbCrLf
    strSQL = strSQL & "  PO.RESOURCE_ID" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  T_WORKORDER_LIST WO," & vbCrLf
    strSQL = strSQL & "  T_PART_OPERATION PO" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  WO.PART_ID=PO.PART_ID" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  WO.DESIRED_WANT_DATE," & vbCrLf
    strSQL = strSQL & "  WO.PART_ID," & vbCrLf
    strSQL = strSQL & "  WO.WORKORDER_BASE_ID," & vbCrLf
    strSQL = strSQL & "  WO.WORKORDER_LOT_ID," & vbCrLf
    strSQL = strSQL & "  PO.OPERATION_SEQ_NO"
    snpData.Open strSQL, dbDatabase

    If snpData.State = 1 Then
        'Remove the previous run
        dbDatabase.Execute "TRUNCATE TABLE T_RESOURCE_DAILY_SCH"

        'Set up the ADO command object to use bind variables
        With comData
            strSQL = "INSERT INTO T_RESOURCE_DAILY_SCH (" & vbCrLf
            strSQL = strSQL & "  RESOURCE_ID," & vbCrLf
            strSQL = strSQL & "  START_DATE," & vbCrLf
            strSQL = strSQL & "  FINISH_DATE," & vbCrLf
            strSQL = strSQL & "  RUN_HOURS," & vbCrLf
            strSQL = strSQL & "  PART_ID," & vbCrLf
            strSQL = strSQL & "  WORKORDER_BASE_ID," & vbCrLf
            strSQL = strSQL & "  WORKORDER_LOT_ID," & vbCrLf
            strSQL = strSQL & "  OPERATION_SEQ_NO," & vbCrLf
            strSQL = strSQL & "  DESIRED_WANT_DATE," & vbCrLf
            strSQL = strSQL & "  UNIT_ASSIGNED)" & vbCrLf
            strSQL = strSQL & "VALUES (" & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?)"

            .Parameters.Append .CreateParameter("resource_id", adVarChar, adParamInput, 15, "")
            .Parameters.Append .CreateParameter("start_date", adDate, adParamInput, 8, Null)
            .Parameters.Append .CreateParameter("finish_date", adDate, adParamInput, 8, Null)
            .Parameters.Append .CreateParameter("run_hours", adNumeric, adParamInput, 12, 0)
            .Parameters.Append .CreateParameter("part_id", adVarChar, adParamInput, 30, "")
            .Parameters.Append .CreateParameter("workorder_base_id", adVarChar, adParamInput, 15, "")
            .Parameters.Append .CreateParameter("workorder_lot_id", adVarChar, adParamInput, 3, "")
            .Parameters.Append .CreateParameter("operation_seq_no", adNumeric, adParamInput, 12, 10)
            .Parameters.Append .CreateParameter("desired_want_date", adDate, adParamInput, 8, Null)
            .Parameters.Append .CreateParameter("unit_assigned", adNumeric, adParamInput, 12, 10)

            .CommandText = strSQL
            .CommandType = adCmdText
            .CommandTimeout = 30
            .ActiveConnection = dbDatabase
        End With

        dbDatabase.BeginTrans

        Do While Not snpData.EOF
            'Find the last operation date for this resource
            intResourceIndex = 0
            For i = 1 To 100
                If strResourceID(i) = snpData("resource_id") Then
                    intResourceIndex = i
                    Exit For
                End If
                If strResourceID(i) = "" Then
                    'No match found, create a new one
                    intResourceIndex = i
                    strResourceID(i) = snpData("resource_id")
                    Exit For
                End If
            Next i

            If strWorkOrderLast <> snpData("workorder_base_id") & "//" & snpData("workorder_lot_id") Then
                varWorkOrderLastDate = varScheduleStart
            End If

            If varResourceLastDate(intResourceIndex) >= varWorkOrderLastDate Then
                comData("start_date") = varResourceLastDate(intResourceIndex)
            Else
                comData("start_date") = varWorkOrderLastDate
            End If

            If varResourceLastDate(intResourceIndex) >= varWorkOrderLastDate Then
                comData("finish_date") = DateAdd("n", snpData("run_hours") * 60, varResourceLastDate(intResourceIndex))
                varWorkOrderLastDate = DateAdd("n", snpData("run_hours") * 60, varResourceLastDate(intResourceIndex))
            Else
                comData("finish_date") = DateAdd("n", snpData("run_hours") * 60, varWorkOrderLastDate)
                varWorkOrderLastDate = DateAdd("n", snpData("run_hours") * 60, varWorkOrderLastDate)
            End If

            varResourceLastDate(intResourceIndex) = varWorkOrderLastDate
            strWorkOrderLast = snpData("workorder_base_id") & "//" & snpData("workorder_lot_id")

            comData("resource_id") = snpData("resource_id")
            comData("run_hours") = snpData("run_hours")
            comData("part_id") = snpData("part_id")
            comData("workorder_base_id") = snpData("workorder_base_id")
            comData("workorder_lot_id") = snpData("workorder_lot_id")
            comData("operation_seq_no") = snpData("operation_seq_no")
            comData("desired_want_date") = snpData("desired_want_date")
            comData("unit_assigned") = 1

            'Execute the insert statement with bind variables
            comData.Execute

            snpData.MoveNext
        Loop
        snpData.Close

        'Issue a COMMIT
        dbDatabase.CommitTrans
    Else
        intResult = MsgBox("Could not query the database." & vbCrLf & Error(Err), 16, "Excel Demo")
    End If

    Set snpData = Nothing
    Set comData = Nothing
End Sub

Switch back to the Excel window and turn off Design Mode.  Clicking the Create Schedule button should populate the T_RESOURCE_DAILY_SCH table.  Once that code finishes executing, switch back to the Code Editor and add the following below the rest of the code:

Private Sub cmdDrawCalendar_Click()
    Dim i As Long
    Dim j As Integer
    Dim intFlag As Integer
    Dim intBaseRow As Integer
    Dim intBaseRowMax As Integer
    Dim intDataRow As Integer
    Dim intWeek As Integer
    Dim intResult As Integer
    Dim intWeekDay As Integer
    Dim intGraphicalMovable As Integer      'Determines if a graphical view will be created
    Dim intGraphicalFixed As Integer        'Determines if a graphical metafile will be created
    Dim strSQL As String
    Dim strLastResourceID As String
    Dim strLine As String
    Dim varWeekStart(1000) As Date
    Dim varWeekEnd(1000) As Date

    Dim lngBoxHeight As Long
    Dim lngBoxLengthMult As Single
    Dim lngFontSize As Long
    Dim lngBoxTop As Long
    Dim lngBoxLeft As Long
    Dim lngBoxWidth As Long
    Dim lngMaxCapacity As Long
    Dim varBaseDate As Date

    Dim lngResourceTop As Long     'Current top position for the resource ID

    Dim strFilename As String
    Dim snpData As ADODB.Recordset
    Dim comData As ADODB.Command

    'Don't break the code if an error is returned
    On Error Resume Next

    intGraphicalMovable = True
    intGraphicalFixed = False

    intResult = ConnectDatabase

    If intResult = False Then
        intResult = MsgBox("Could not connect to the Visual database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")

        Exit Sub
    End If

    If intGraphicalFixed = True Then
        'See if we need to delete the worksheet from a previous execution
        For i = 1 To ActiveWorkbook.Sheets.Count
            If ActiveWorkbook.Sheets(i).Name = "ShopCalendarViewFixed" Then
                Sheets("ShopCalendarViewFixed").Select
                Application.DisplayAlerts = False
                ActiveWindow.SelectedSheets.Delete
                Application.DisplayAlerts = True
                Exit For
            End If
        Next i

        'Add a new worksheet to the workbook after the ShopCalendar worksheet
        ActiveWorkbook.Sheets.Add , Sheets("ShopCalendar")
        ActiveWorkbook.ActiveSheet.Name = "ShopCalendarViewFixed"
    End If

    If intGraphicalMovable = True Then
        'See if we need to delete the worksheet from a previous execution
        For i = 1 To ActiveWorkbook.Sheets.Count
            If ActiveWorkbook.Sheets(i).Name = "ShopCalendarView" Then
                Sheets("ShopCalendarView").Select
                Application.DisplayAlerts = False
                ActiveWindow.SelectedSheets.Delete
                Application.DisplayAlerts = True
                Exit For
            End If
        Next i

        'Add a new worksheet to the workbook after the ShopCalendar worksheet
        ActiveWorkbook.Sheets.Add , Sheets("ShopCalendar")
        ActiveWorkbook.ActiveSheet.Name = "ShopCalendarView"
    End If

    Sheets("ShopCalendar").Select

    Set snpData = New ADODB.Recordset
    Set comData = New ADODB.Command

    Sheets("ShopCalendar").Rows("13:10000").Delete Shift:=xlUp

    With comData
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  RESOURCE_ID," & vbCrLf
        strSQL = strSQL & "  START_DATE," & vbCrLf
        strSQL = strSQL & "  FINISH_DATE," & vbCrLf
        strSQL = strSQL & "  RUN_HOURS," & vbCrLf
        strSQL = strSQL & "  PART_ID," & vbCrLf
        strSQL = strSQL & "  WORKORDER_BASE_ID," & vbCrLf
        strSQL = strSQL & "  WORKORDER_LOT_ID," & vbCrLf
        strSQL = strSQL & "  OPERATION_SEQ_NO," & vbCrLf
        strSQL = strSQL & "  DESIRED_WANT_DATE," & vbCrLf
        strSQL = strSQL & "  UNIT_ASSIGNED" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  T_RESOURCE_DAILY_SCH" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  START_DATE<=TRUNC(SYSDATE+45)" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  RESOURCE_ID," & vbCrLf
        strSQL = strSQL & "  START_DATE," & vbCrLf
        strSQL = strSQL & "  FINISH_DATE," & vbCrLf
        strSQL = strSQL & "  WORKORDER_BASE_ID," & vbCrLf
        strSQL = strSQL & "  WORKORDER_LOT_ID"

        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
    End With

    Set snpData = comData.Execute

    varBaseDate = Date
    lngBoxLengthMult = 0.1
    lngBoxHeight = 8
    lngResourceTop = 30
    lngFontSize = 6

    varBaseDate = Date

    If Not (snpData Is Nothing) Then
    'If snpData.State = 1 Then
        Application.ScreenUpdating = False

        If intGraphicalMovable = True Then
            For i = 0 To 30
                lngBoxLeft = 70 + lngBoxLengthMult * (i * 60 * 24)
                lngBoxWidth = lngBoxLengthMult * (60 * 24) - 1
                With Sheets("ShopCalendarView").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight * 2 - 1))
                    With .TextFrame
                        .Characters.Text = Format(DateAdd("d", i, varBaseDate), "m/d")
                        .Characters.Font.Size = (lngBoxHeight - 2) * 2
                    End With
                    .Fill.ForeColor.RGB = RGB(255 - 4 * (Weekday(DateAdd("d", i, varBaseDate), vbMonday) - 1), 255 - 4 * (Weekday(DateAdd("d", i, varBaseDate), vbMonday) - 1), 255 - 4 * (Weekday(DateAdd("d", i, varBaseDate), vbMonday) - 1))
                End With
            Next i
        End If

        If Not (snpData.EOF) Then
            intBaseRowMax = 10

            'Find the week start and end dates of each week
            varWeekStart(1) = CDate(Format(DateAdd("d", -Weekday(snpData("start_date"), vbMonday) + 1, snpData("start_date")), "mm/dd/yyyy"))
            varWeekEnd(1) = DateAdd("d", 6, varWeekStart(1))
            For i = 2 To 1000
                varWeekStart(i) = DateAdd("d", (i - 1) * 7, varWeekStart(1))
                varWeekEnd(i) = DateAdd("d", 6, varWeekStart(i))
            Next i

            Do While Not snpData.EOF
                If strLastResourceID <> snpData("resource_id") Then
                    'Started a new resource ID
                    If intBaseRow > 10 Then
                        'Write out the week start dates for the last resource, and draw the borders
                        For i = intBaseRow To intBaseRowMax - 1
                            Sheets("ShopCalendar").Cells(i + 1, 1).Value = Format(varWeekStart(i - intBaseRow + 1), "mm/dd/yyyy")
                        Next i

                        Sheets("ShopCalendar").Range("B" & Format(intBaseRow) & ":H" & Format(intBaseRowMax)).Select
                        With Selection.Borders(xlEdgeLeft)
                            .Weight = xlThick
                        End With
                        With Selection.Borders(xlEdgeTop)
                            .Weight = xlThick
                        End With
                        With Selection.Borders(xlEdgeBottom)
                            .Weight = xlThick
                        End With
                        With Selection.Borders(xlEdgeRight)
                            .Weight = xlThick
                        End With
                        With Selection.Borders(xlInsideVertical)
                            .Weight = xlThin
                        End With
                        With Selection.Borders(xlInsideHorizontal)
                            .Weight = xlThin
                        End With
                    End If

                    If intGraphicalMovable = True Then
                        'For the graphical view
                        lngResourceTop = lngResourceTop + lngMaxCapacity * (lngBoxHeight) + 10
                        lngBoxLeft = 1
                        lngBoxWidth = 40
                        lngBoxTop = lngResourceTop

                        With Sheets("ShopCalendarView").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight - 1))
                            With .TextFrame
                                '.AutoMargins = False
                                .Characters.Text = snpData("resource_id")
                                .Characters.Font.Size = lngBoxHeight - 2
                                .MarginLeft = 0
                                .MarginRight = 0
                                .MarginTop = 0
                                .MarginBottom = 0
                            End With
                            .Fill.ForeColor.RGB = RGB(255, 255, 255)
                        End With
                    End If

                    lngMaxCapacity = 0

                    intBaseRow = intBaseRowMax + 4
                    strLastResourceID = snpData("resource_id")

                    Sheets("ShopCalendar").Cells(intBaseRow - 1, 1).Value = snpData("resource_id")
                    Sheets("ShopCalendar").Cells(intBaseRow - 1, 1).Font.Bold = True
                    Sheets("ShopCalendar").Cells(intBaseRow - 1, 1).Font.Size = 14
                    For i = 1 To 7
                        Sheets("ShopCalendar").Cells(intBaseRow, i + 1).Value = Format(DateAdd("d", i - 1, varWeekStart(1)), "DDD")
                        Sheets("ShopCalendar").Cells(intBaseRow, i + 1).Font.Bold = True
                    Next i
                End If

                If Not IsNull(snpData("unit_assigned")) Then
                   If lngMaxCapacity < snpData("unit_assigned") Then
                        'Need to update the maximum unit assigned value
                        lngMaxCapacity = snpData("unit_assigned")
                    End If

                    If (varBaseDate < snpData("start_date")) Or (varBaseDate < snpData("finish_date")) Then
                        If lngMaxCapacity < snpData("unit_assigned") Then
                            lngMaxCapacity = snpData("unit_assigned")
                        End If

                        If intGraphicalMovable = True Then
                            lngBoxLeft = 70 + lngBoxLengthMult * (DateDiff("n", varBaseDate, snpData("start_date")))
                            lngBoxWidth = lngBoxLengthMult * ((DateDiff("n", varBaseDate, snpData("finish_date")) - DateDiff("n", varBaseDate, snpData("start_date"))))
                            lngBoxTop = lngResourceTop + snpData("unit_assigned") * lngBoxHeight

                            With Sheets("ShopCalendarView").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight - 1))
                                With .TextFrame
                                    '.AutoMargins = False
                                    .Characters.Text = snpData("workorder_base_id") & "/" & snpData("workorder_lot_id") & " OP " & CStr(snpData("operation_seq_no"))
                                    .Characters.Font.Size = lngBoxHeight - 2
                                    .MarginLeft = 0
                                    .MarginRight = 0
                                    .MarginTop = 0
                                    .MarginBottom = 0
                                End With
                                If snpData("finish_date") > snpData("desired_want_date") Then
                                    .Fill.ForeColor.RGB = RGB(255, 0, 0)
                                Else
                                    .Fill.ForeColor.RGB = RGB(0, 255, 255)
                                End If
'                                If snpData("workorder_sub_id") = "0" Then
                                    .AlternativeText = "WO:" & snpData("workorder_base_id") & "/" & snpData("workorder_lot_id") & " OP:" & CStr(snpData("operation_seq_no")) & Chr(10) & "Part ID:" & snpData("part_id") & Chr(10) & Format(snpData("start_date"), "mm/dd/yyyy hh:nn") & " - " & Format(snpData("finish_date"), "mm/dd/yyyy hh:nn") & Chr(10) & snpData("resource_id")
'                                Else
'                                    .AlternativeText = "WO:" & snpData("workorder_base_id") & "-" & snpData("workorder_sub_id") & "/" & snpData("workorder_lot_id") & " OP:" & CStr(snpData("sequence_no")) & Chr(10) & "Part ID:" & snpData("part_id") & Chr(10) & Format(snpData("start_date"), "mm/dd hh:nn") & " - " & Format(snpData("finish_date"), "mm/dd hh:nn") & Chr(10) & snpData("resource_id")
'                                End If
                            End With
                        End If
                    End If
                End If
                For i = 0 To DateDiff("d", CDate(Format(snpData("start_date"), "mm/dd/yyyy")), CDate(Format(snpData("finish_date"), "mm/dd/yyyy")))
                    intWeekDay = Weekday(DateAdd("d", i, snpData("start_date")), vbMonday) 'Column
                    'Find the row to place the information into
                    For j = 1 To 1000
                        If varWeekEnd(j) >= CDate(Format(DateAdd("d", i, snpData("start_date")), "mm/dd/yyyy")) Then
                            'Found the week for this record
                            intDataRow = intBaseRow + j

                            If intDataRow > intBaseRowMax Then
                                intBaseRowMax = intDataRow
                            End If
                            Exit For
                        End If
                    Next j
                    If (i = 0) And (i = DateDiff("d", CDate(Format(snpData("start_date"), "mm/dd/yyyy")), CDate(Format(snpData("finish_date"), "mm/dd/yyyy")))) Then
                        'Need to include the start time and the end time
                        strLine = Format(snpData("start_date"), "hh:nn") & " - " & Format(snpData("finish_date"), "hh:nn") & "  " & snpData("part_id")
                    Else
                        If i = 0 Then
                            'Need to include the start time
                            strLine = Format(snpData("start_date"), "hh:nn") & " - C0:00" & "  " & snpData("part_id")
                        Else
                            If i = DateDiff("d", CDate(Format(snpData("start_date"), "mm/dd/yyyy")), CDate(Format(snpData("finish_date"), "mm/dd/yyyy"))) Then
                                'Need to include the end time
                                strLine = "C0:00" & " - " & Format(snpData("finish_date"), "hh:nn") & "  " & snpData("part_id")
                            Else
                                'Operation is continuing through this date
                                strLine = "C0:00" & " - C0:00" & "  " & snpData("part_id")
                            End If
                        End If
                    End If
                    strLine = strLine & "  " & snpData("workorder_base_id")
'                    If snpData("workorder_sub_id") <> "0" Then
'                        strLine = strLine & "-" & snpData("workorder_sub_id")
'                    End If
                    strLine = strLine & "/" & snpData("workorder_lot_id")
                    strLine = strLine & " OP " & Format(snpData("operation_seq_no"))

                    If Sheets("ShopCalendar").Cells(intDataRow, intWeekDay + 1).Value = "" Then
                        'Writing into a new cell
                        Sheets("ShopCalendar").Cells(intDataRow, intWeekDay + 1).Value = strLine
                    Else
                        'Writing into a cell already containing data
                        Sheets("ShopCalendar").Cells(intDataRow, intWeekDay + 1).Value = Sheets("ShopCalendar").Cells(intDataRow, intWeekDay + 1).Value & Chr(10) & strLine
                    End If
                Next i
                snpData.MoveNext
            Loop

            If intBaseRow > 10 Then
                'Write out the week start dates for the last resource
                For i = intBaseRow To intBaseRowMax - 1
                    Sheets("ShopCalendar").Cells(i + 1, 1).Value = Format(varWeekStart(i - intBaseRow + 1), "mm/dd/yyyy")
                Next i

                Sheets("ShopCalendar").Range("B" & Format(intBaseRow) & ":H" & Format(intBaseRowMax)).Select
                Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                With Selection.Borders(xlEdgeLeft)
                    .Weight = xlThick
                End With
                With Selection.Borders(xlEdgeTop)
                    .Weight = xlThick
                End With
                With Selection.Borders(xlEdgeBottom)
                    .Weight = xlThick
                End With
                With Selection.Borders(xlEdgeRight)
                    .Weight = xlThick
                End With
                With Selection.Borders(xlInsideVertical)
                    .Weight = xlThin
                End With
                With Selection.Borders(xlInsideHorizontal)
                    .Weight = xlThin
                End With
            End If
        End If
        snpData.Close
    End If

    Sheets("ShopCalendar").Rows("10:" & Format(intBaseRowMax)).VerticalAlignment = xlTop

    Sheets("ShopCalendar").PageSetup.PrintArea = "$A$13:$H$" & Format(intBaseRowMax)
    With Sheets("ShopCalendar").PageSetup
        .CenterFooter = "Generated " & Format(Now, "mm/dd/yyyy hh:nn")
        .RightFooter = "Page &P of &N"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintGridlines = False
        .Orientation = xlLandscape
         '.PaperSize = xlPaperLetter
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 300
    End With

    Sheets("ShopCalendar").Range("B2").Select
    ActiveWindow.FreezePanes = True

    Sheets("ShopCalendar").Columns("B:H").Select
    Selection.ColumnWidth = 44
    Selection.WrapText = True

    Sheets("ShopCalendar").Columns("A:A").ColumnWidth = 10.43
    Sheets("ShopCalendar").Range("A13").Select
    Application.ScreenUpdating = True

    Set snpData = Nothing
    Set comData = Nothing
End Sub

With the above code added, the Draw Calendar button should now work.  Click the Draw Calendar button, the output should look something like what you see below:

A second tab is added to the worksheet that shows a graphical overview, with cyan colored bars representing operations that will complete before the work order due date and red colored bars representing operations that will complete after the work order due date:

I may post a follow-up at some point that shows how to generate an enhanced Windows metafile (EMF) which shows the graphical view of the resource utilization.