Objective
In this lab, students will complete the following objectives.
• Create a connection to an Access database.
• Create various SQL queries to extract information from a database.
• Format extracted data with column headers.
Element K Network Connections
For this lab, we will only need to connect to vlab-PC1. The computer vlab-PC1 is the computer on the left side while vlab-PC2 is on the right. If you leave the cursor on the PC icon for a few seconds, a tool-tip message will appear indicating the hostname of the PC. Open vlab-PC1 and log in as Administrator with the password password.
Lab Overview
Even though we are only using vlab-PC1 to complete our lab assignment, the database we will be accessing (Computers.accdb) is actually located on the computer vlab-PC2 in the directory C:Database. This directory is shared as a ReadOnly network share by vlab-PC2. The Universal Naming Convention (UNC) name for this share is \vlab-PC2Database. Our VBScript program vlab-PC1 will have to open the \vlab-PC2Database share and map it to the local X: drive. The path specified fro the database will then be X:Computers.accdb.
The IT department maintains an Access database on vlab-PC2 that is used to inventory the computers in the various rooms. Fields in the database include: Computer Type, Hostname, Room Number, CPU Type, Number of Bits, Speed, Number of Processors, Operating System, Memory, and Hard Drive Size. We need to query this database to determine upgrades and replacements for existing computers.
Below (and on the following page) is a listing of the Computers.accdb database contents:
Task 1: Understanding the Net Use Commands in ComputerDatabase.vbs
• Open Notepad++. Use the menu option File/Open to open the VBScript program: C:ScriptsComputerDatabase.vbs.
Task 2: Understanding the ADODB.Connection and ADODB.Recordset Objects
• In NotePad++, look at the following code lines.
Line 11 contains the SQL Query String named sqlStr. This is the line you will have to modify to properly query the Computer database. The SQL Query “SELECT * FROM Computers” will select all fields from the database table Computers.
Lines 12 and 13 uses a string named dataSource to specify the Microsoft Driver and the name and location of the local database: X:Computers.accdb.
Line 14 Creates the “ADODB.Connection” object while line 15 opens the connection to the database.
Line 16 Creates the “ADODB.Recordset” object while line 17 provides access to the records using the SQL Query String and the Connection object. Line 18 moves the objRecordSet pointer to the first record.
Task 3: Displaying the Record Headers and Database Records
• In NotePad++, look at the following lines of the ComputerDatabase.vbs program.
Lines 20–24 display the Database fieldnames as column headers. Note the use of & to concatenate (add) string values together and _ which is the VBScript line continuation character.
Lines 25–37 are a Do Until loop that sequences through the database looking for records
that match the SQL Query String. The objRecordSet.EOF method checks to see if we have reached the last record in the database. This required because reading past the end of a database will cause an error. recordStr is a string variable initially set to “”. recordStr is used to create a multi-line string that contains the column headers and records that match the SQL query. The WScript.Echo recordStr statement in line 40 displays the column headers and records to the console or desktop windows depending on whether cscript or wscript is used to run the program.
Lines 38 and 39 close the database connections made by the ADODB.Connection and ADODB.Recordset objects.
The function pad(byVal strText, ByVal len) in lines 44–46 are used to format the field values with added spaces so the tab positions will line up correctly.
Task 4: Write and Run Database Query Program 1
In this scenario, we need to query the Computer database to determine which computers need to be replaced. Our decision will be based on the CPU speed, Number of Processors, and the size of the Hard Drive.
• Open the ComputerDatabase.vbs program in NotePad++ and Save As the program with the name ComputerReplace.vbs.
• Modify the SQL Query String (sqlStr) in line 11 to extract the following information from the database.
Fields Displayed from Computers Table (specified by the SELECT clause).
Computer
Room_Num
Speed
Num_CPUs
OS_Type
HDD_Size
Replacement Criteria (specified by the WHERE clause).
Any computer with a single CPU
Any computer with a CPU speed less than 2.1 GHz
Any Computer with a Hard Disk Drive size less than 300 GBytes
Sort Criteria (specified by the ORDER BY clause).
Sort the extracted records by the “Room_Num” field.
• Modify lines 20–24 to display the correct field headers for the fields being displayed.
• Modify the Do Until loop body to include only the fields being displayed. Use the pad( ) function as needed to make the header and field values line up.
• Press the <F6> function key and in the Commands box, type wscript ComputerReplace.vbs. Click OK to run the program and verify correct formatting and query results.
• This query should generate eight records displayed in order by room number. If you have any errors, do not get the correct results or your columns are mis-aligned; modify your program
as required until you get the correct output.
Copy and paste your ComputerReplace.vbs program sourcecode from NotePad++ and the desktop window from your Run into the spaces provided in your lab-report document. Answer the questions about the Replacement SQL Query in the lab-report document.
Task 5: Write and Run Database Query Program 2
In this scenario, we need to upgrade our company computers based on the Operating System and the amount of memory. We want to ensure that all Fedora 10 machines are upgraded to Fedora 14 and all Windows XP machines are upgraded to Windows 7. If we find any computers with only 2 GB of memory, we will upgrade the memory to 4 GB.
• Open the ComputerDatabase.vbs program in NotePad++ and Save As the program with the name ComputerUpgrade.vbs.
• Modify the SQL Query String (sqlStr) in line 11 extract the following information from the database.
Fields Displayed from Computers Table (specified by the SELECT clause).
Computer
HostName
Room_Num
OS_Type
Memory
Replacement Criteria (specified by the WHERE clause).
Note: String values in fields must be delimited by single quotes.
Any computer with the Fedora 10 Operating System (‘Fedora 10’)
Any computer with the Windows XP Operating System (‘Windows XP’)
Any computer with 2 GB of memory
Sort Criteria (specified by the ORDER BY clause).
Sort the extracted records by the “OS_Type” field.
• Modify lines 20–24 to display the correct field headers for the fields being displayed.
• Modify the Do Until loop body to include only the fields being displayed. Use the pad( ) function as needed to make the header and field values line up.
• Press the <F6> function key and in the Commands box, type wscript ComputerUpgrade.vbs. Click OK to run the program and verify correct formatting and query results.
• This query should generate 16 records displayed in order by OS_Type. If you have any errors, do not get the correct results, or your columns are mis-aligned; modify your program as required until you get the correct output.
Copy and paste your ComputerUpgrade.vbs program sourcecode from NotePad++ and the desktop window from your Run into the spaces provided in your lab-report document. Answer the questions about the Upgrade SQL Query in the lab-report document.
Student Name ____________________________ Date _____________
VBScript Database Query Lab Report
Task 4: Write and Run Database Query Program 1
In this scenario, we need to query the Computer database to determine which computers need to be replaced. Our decision will be based on the CPU speed, Number of Processors and the size of the Hard Drive.
• In the space provided in your Lab Report document, paste your modified VBScript program and the RUN.
In the table cell below, paste your ComputerReplace.vbs Program
In the table cell below, paste the desktop RUN from your ComputerReplace.vbs Program
How many Computers will be replaced due only to CPU Speed < 2 GHz?
How many Computers will be replaced due only to Number of CPUs = 1?
How many Computers will be replaced due only to HDD Size < 300?
How many Computers will be replaced due to 2 or more reasons?
Task 5: Write and Run Database Query Program 2
In this scenario, we need to upgrade our company computers based on the Operating System and the amount of memory. We want to ensure that all Fedora 10 machines are upgrade to Fedora 14 and all Windows XP machines are upgraded to Windows 7. If we find any computers with only 2 GB of memory, we will upgrade the memory to 4 GB.
In the table cell below, paste your ComputerUpgrade.vbs Program
In the table cell below, paste the desktop RUN from your ComputerUpgrade.vbs Program
How many Fedora 10 Computers will be upgraded?
How many Window 7 Computers will be upgraded due to 2 GB memory?
How many Windows XP Computers will need a Memory and OS upgrade?
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
Our Services
Custom Writings Help is a Quality-Oriented Company in Online Writing as a result of hiring exceptional professionals to execute clients' tasks.
Essays
At Custom Writings Help,We understand the struggle of research paper writing, and that is why at Custom WritingS Help, we are all out to help you. We pride ourselves on having a team of clinical writers. The stringent and rigorous vetting process ensures that only the 'BEST' Writers are chosen for the job. We have highly qualified Ph.D. and MA writers working with us; we equally offer these experienced writers specific bonuses and incentives to make them deliver highly original, unique, and informative content at reasonably low prices.
Admissions
Thesis Writing Service
Worlwide, Many Masters Students are struggling with Thesis Completion. A thesis is likely to be the longest and most challenging piece of work a student has ever completed. However, unlike essays and other assignments, a student can pick a particular interest topic and work on their initiative. Fortunately, we are there for you. At Custom Writings Help, you are assured of an authentic, imaginative, informative, linguistically great, and advantageous thesis that adheres to all your needs. So, why continue considering different writers when you have discovered the best in the field?
Editing
Custom Papers
Not a single student can avoid writing custom papers. However, a total lack of experience, skills, and time makes it very hard to produce a superb writing piece. Therefore, if you are seeking professional help, turn to us. Our specialized and experienced writers compose a variety of model papers, including custom essays, college term papers, research papers, book reports, MBA essays, executive summaries, dissertations, Ph.D. theses, admission essays, and research proposals for college and university students at any level.
Coursework
Essay Writing
Most of the students disregard the critical principles of essay writing and compose papers below sensible guidelines. Therefore, with Custom Writings Help, one should not worry about his/her essay. Our Writers compose informative and engaging content on all complexities and topics. We write meaningful and smart essays while prioritizing all aspects that bring about a good grade, such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines.
Coursework
Coourse Work Writing
Don't let the seemingly never-ending onslaught of writing assignments get you down. If you are looking where to get course work assistance online, the writers at Custom Writings Help are here to assist you with all of your writing needs. We undertake to unique delivery of papers that meet the professor's requirements. The content is proofread, edited, and checked plagiarism before submission to customers. No matter how big or small your work is, we will deliver on time. Try US Now! !
Coursework
Dissertation Writing Service
High-Quality Dissertation Writing Services are rare. They require Ph.D. academicians – not easily found. However, are an exception. The years, time, and resources we have invested in the dissertation world has given us a competitive advantage over others. Choose to come to Custom Writings Help; You will find perfect Ph.D. consultants who have written hundreds of dissertations theses ready to help you. Let our dissertation-writing services help you craft your dissertation, for you are assured we will give you the results.