Following and Sub Procedure Essay

Submitted By mimoharaj
Words: 577
Pages: 3
Open Document
Lecture 20 _ Exercise Manual
1. Please go download a driver (mySQL) from the BB (file named mysql-connector-odbc-5.1.9-win32.msi), and install
2. If you are using other OS, go to http://dev.mysql.com/downloads/connector/odbc/5.1.html and download the correct driver and install
NOTE you need to make sure the appropriate driver is installed. My computer (64bits) though uses 32 bit driver.
3. In Excel VBE, tools->reference->Microsoft ActiveX Data Objects 2.8 Library

4. Now, let's work on the first example, a simple query
1. in module, create a sub procedure as following:

Sub querydata() src = "SELECT * FROM Manufacturers" Dim cntMyConnection As ADODB.Connection Set cntMyConnection = New ADODB.Connection CnctSource = "DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;Database=YOURDB; Uid=root; pwd=YOURPASSWD; OPTION=3;" cntMyConnection.Open ConnectionString:=CnctSource Dim rstFirstRecordset As ADODB.Recordset Set rstFirstRecordset = New ADODB.Recordset rstFirstRecordset.Open Source:=src, ActiveConnection:=cntMyConnection i = 4 Worksheets("DatabaseConnection").Activate With ActiveSheet .Cells(3, 1) = rstFirstRecordset(0).name .Cells(3, 2) = rstFirstRecordset(1).name While Not rstFirstRecordset.EOF .Cells(i, 1) = rstFirstRecordset(0).Value .Cells(i, 2) = rstFirstRecordset(1).Value rstFirstRecordset.MoveNext i = i + 1 Wend End With Set rstFirstRecordset = Nothing cntMyConnection.Close Set cntMyConnection = Nothing
End Sub
2. now on the spreadsheet, add a button (ActiveX Button, or FORM button, you make the choice), and connect the button to the sub procedure named querydata.

NOTEs
First of all, you need to remember it is connected to the DB in your MySQL... what is the name of your DB you are connecting?
Second, you need to make sure use the correct password you are using in connecting to your mySQL.
Third, in looking at the src = "SELECT * FROM Manufacturers" in the coding, Manufacturers is the table we created in the DB in our earlier mySQL exercise!
Last, in the above coding, Worksheets("DatabaseConnection").Activate, I have the spreadsheet named "DatabaseConnection", if your spreadsheet has different name, do make sure you make necessary changes...
Same Notes apply for the following two sub procedure!

5. Now, let's work on the second example, query on selection criteria
1. create a sub procedure as following

Sub querydata_selectionCriteria() Dim test As String test = InputBox("Please input the selection criteria for manufacturer's name:")

src = "SELECT * FROM Manufacturers where Name =" & "'" & test & "'" Dim cntMyConnection As ADODB.Connection Set cntMyConnection = New ADODB.Connection CnctSource = "DRIVER={MySQL ODBC 5.1 Driver};Server=localhost;Database=YOURDB; Uid=root; pwd=YOURPASSWD; OPTION=3;" cntMyConnection.Open ConnectionString:=CnctSource Dim rstFirstRecordset As ADODB.Recordset Set rstFirstRecordset = New ADODB.Recordset
Show More

Related Documents: Following and Sub Procedure Essay

Programming: Programming Language and Hierarchical Program Organisation Essay

principle implies that program instructions should be executed in the order in which they appear. The selection principle implies that instructions may be executed selectively using IF-THEN and/or IF-THEN-ELSE statements. These statements work in the following way. IF a condition is met or is true, THEN a specific set of instructions will be executed. If the condition is false, then another set of instructions will be executed. MT 512: Programming Design Page no: 38 For example, IF an employee works…

Words 1877 - Pages 8

Lime And Cement Case Study

of expansive sub-grade soils. Lime is used to stabilize fine grained soils where as cement is effective for granular materials (Little and Nair, 2009). In the present study two chemical stabilizers namely a locally manufactured hydrated lime and imported cement based industrial product ANSS were used for the stabilization of the sub-grade soil. Laboratory tests were conducted on hydrated lime-soil and ANSS-soil mixtures to evaluate the performance of these chemical additives on the sub-grade soils…

Words 1006 - Pages 5

Enrollment System High School Essay

Introduction Nowadays computer serves as an important role in our society, most especially in school premise. Most school uses computer to help their students deal in our modern technology. But as the population of the students continue to grow, many school face difficulties in handling their enrolment in a manual process. And one of the solutions in this problem is to make their manual enrollment into a computerized system. The School was conceptualized by the Division of City Schools of Cabanatuan…

Words 4473 - Pages 18

Pros And Cons Of Constitutional Provisions

the disposition or omission made by the public agencies other than the state or local governments, shall be the heads of central administrative agencies concerned. (2) The requester may request an administrative ruling without going through the procedures for application for objection pursuant to the provisions of Article 16 (3) The member of the administrative appeals commission who is involved in the administrative ruling concerning the decision on disclosure of information may not reveal the secrets…

Words 6669 - Pages 27

Essay Billing and Coding with Simulations: Study Guide

Billing and Coding with Simulations Week 2 Study Guide BC3030 Please review the following topics in preparation for your week 2 coursework. Objectives for “Next Step” Chapter 9 (used in conjunction with the ICD-9-CM and CPT-4 code books) • Objective 1 o Remember to use the main term in the index of the ICD-9-CM and the appropriate sub-terms for the best code options. Read the guidelines for specific information regarding additional coding and code sequencing. o…

Words 338 - Pages 2

Tutorial 1 Solution Essay

GENG1002 Introduction to Engineering Computations TUTORIAL 1 - Week 2 TIME ALLOWED - 2 HOURS Complete the following exercises. All programs should be suitably commented, formatted (i.e. use indentation) and variables assigned meaningful names. Parts of this tutorial that you do not complete in class should be completed in your own time before the next tutorial. Exercise 1 Refer to Lecture notes of Week 1 (Slides 47-55) and develop ‘Hello World’ program by a - MS Visual Studio using a Console Application…

Words 568 - Pages 3

Unit 2a Titration

For example, for sub-unit 2A, I learnt many skills that came into play when I was carrying out the titration standardisations. These skills involved calibrating a balance, a glass pipette and a pH meter – this skill meant my titration would then be more accurate and reliable as the equipment was of a good quality as it provided accurate results during the calibration. For sub-unit 2B, I learnt how to calibrate the two different thermometers as well as learning how to accurately draw a cooling curve…

Words 1283 - Pages 6

Mr. Herrington Essay

Mark Hecksel October 20, 2012 Radi 211 Contrast Agent Study 2 (2o points each) 1. Name of procedure/exam (one only) (1 point) Shoulder Arthrography 2. Exam Indications (2 points) * Assessment of the integrity of the rotator cuff * Assessment of shoulder instability * For diagnosis of glenoid labrum tear * For diagnosis of adhesive capsulitis 3. Patient instructions according to textbook (before, during or after the exam) (2 points) E.g. preparation, diet…

Words 1212 - Pages 5

Calm and Success on an Exam Essay

schedule, eating healthy meals, and studying the material in various ways. At the end of each quarter or semester, each student is required to take a final exam, but not all the students are mentally and emotionally prepared for an exam. By following this procedure, students will be more confident in obtaining an “A” on their exams. The first step to success is to construct a schedule covering three weeks of preparation for the exam. A student should learn more about the class syllabus by discussing…

Words 468 - Pages 2

Essay about Accreditation Audit: Raft Task 1

occurrence of wrong site, wrong procedure and wrong person surgery and other procedures in Joint Commission accredited organizations” (Joint Commission, 2013). The Standards of Universal Protocols (UP) are: UP 01.01.01 Conduct pre-procedure verification process UP 01.02.01 Mark the procedure site UP 01.03.01 Perform a Time-Out before the procedure. To…

Words 1455 - Pages 6