Introduction to Procedures

1. Introduction to Procedures • Modules • A module is a file that holds code or pieces of code in a Visual Basic application • you can open the object in Design View and click the View Code button harshana-fernando.blogspot.com

2. Creating a Module • In Microsoft Access, on the Ribbon, click Create. In the Other section, • click the arrow under the Macro button and click Module In Microsoft Visual Basic On the main menu, click Insert -> Module On the Standard toolbar, click the arrow of the Insert Module button and click Module harshana-fernando.blogspot.com

3. Sub Procedures A procedure is an assignment you ask Microsoft Visual Basic to perform besides, or to complete, the normal flow of the program. A procedure is created to work in conjunction with the controls' events of a database. Structurally, a procedure appears similar to an event. Sub ProcedureName() End Sub Sub CreateName() Dim strFullName As String strFullName = "Jacques Fame Ndongo" End Sub harshana-fernando.blogspot.com

4. Inserting a Procedure On the main menu, click Insert -> Procedure On the Standard toolbar, click the arrow of the Insert button and click Procedure harshana-fernando.blogspot.com

5. Calling a Procedure Private Sub Detail_Click() ChangeColor End Sub Private Sub cmdSqCalculate_Click() SquareSolution End Sub Private Sub cmdRCalculate_Click() SolveRectangle End Sub harshana-fernando.blogspot.com

6. Functions • A function is a procedure that takes care of an assignment and returns a result. Function GetFullName() End Function Function GetFullName() As String End Function Function GetFullName() As String Dim strFirstName, strLastName As String strFirstName = txtFirstName strLastName = txtLastName GetFullName = strFirstName & " " & strLastName End Function harshana-fernando.blogspot.com

7. Calling a Function Function GetFullName() As String Dim strFirstName, strLastName As String strFirstName = txtFirstName strLastName = txtLastName GetFullName = strFirstName & " " & strLastName End Function Private Sub Detail_DblClick(Cancel As Integer) txtFullName = GetFullName End Sub harshana-fernando.blogspot.com

8. Introduction to Built-In Objects Microsoft Access heavily relies on built-in objects and already created collections. These collections are made in entities we call classes. Besides the collections and objects, there are also many enumerations. • To display the Object Browser: • On the main menu, click View -> Object Browser • On the Standard toolbar, click the Object Browser button harshana-fernando.blogspot.com

9. harshana-fernando.blogspot.com

10. The Current Project Private Sub Detail_Click() Dim CurrentDatabase As Object Set CurrentDatabase = Application.CurrentProject txtFilename = CurrentDatabase.FullName End Sub harshana-fernando.blogspot.com

11. The DoCmd Object harshana-fernando.blogspot.com

12. Microsoft Access Object Library and VBA on the main menu of Microsoft Visual Basic, you can click Tools -> References... harshana-fernando.blogspot.com

13. When you start a database in Microsoft Access, you are said to have started a session. If one database is not enough for what you are currently doing, you can open another existing database or you can create a new one. harshana-fernando.blogspot.com

14. Microsoft Office 2007 ships with a library that can be used to perform all types of operations on a database and used throughout all applications of the Microsoft Office family. This library is called Microsoft Office Access Database Engine Object. Like every library, it has a version. In Microsoft Office 2007, it is the Microsoft Office 12.0 Access Database Engine Object Library. harshana-fernando.blogspot.com

15. Using DAO If you decide to use the DAO library, you must remove the Microsoft Office 12.0 Access Database Engine Object Library because both libraries cannot coexist in the same database harshana-fernando.blogspot.com

16. Microsoft ActiveX Data Objects- ADO harshana-fernando.blogspot.com

17. Connection to a Database Private Sub cmdConnector_Click() Dim conConnector As ADODB.Connection Set conConnector = New ADODB.Connection Set conConnector = Nothing End Sub After using the Connection variable, to release the resources it using, assign Nothing to it. harshana-fernando.blogspot.com

18. Opening a Connection Connection.Open ConnectionString, UserID, Password, Options The most common provider we will use is the Microsoft JET database engine. It is represented as Microsoft.JET.OLEDB.4.0 (case insensitive). You can use it to specify the provider as follows Private Sub cmdConnector_Click() Dim conConnector As ADODB.Connection Set conConnector = New ADODB.Connection conConnector.Open "Provider=Microsoft.Jet.OLEDB.4.0" Set conConnector = Nothing End Sub harshana-fernando.blogspot.com

19. Private Sub cmdConnector_Click() Dim conConnector As ADODB.Connection Set conConnector = New ADODB.Connection conConnector.Open "Provider='Microsoft.Jet.OLEDB.4.0';" & _ "Data Source=C:\Exercises\Example.accdb" Set conConnector = Nothing End Sub harshana-fernando.blogspot.com

20. Connection to the Current Database Private Sub cmdCurrentConnection_Click() Dim conCurrent As ADODB.Connection Set conCurrent = Application.CurrentProject.Connection Set conCurrent = Nothing End Sub When this code executes, it identifies the connection to the current database and stores it in a declared variable named conCurrent harshana-fernando.blogspot.com

21. Closing a Connection Private Sub cmdDataSource_Click() Dim conConnector As ADODB.Connection Set conConnector = New ADODB.Connection conConnector.Open "DSN=Exercise;UID=;PWD=;" conConnector.Close Set conConnector = Nothing End Sub When using a connection, it consumes resources that other applications may need. Therefore, after using it, you should close it and free the resources it was using so they can be made available to the other parts of the computer harshana-fernando.blogspot.com

22. Table Creation With the Microsoft Access Object Library Private Sub cmdCreateTable_Click() Dim curDatabase As Object Dim tblStudents As Object ‘ Get a reference to the current database Set curDatabase = CurrentDb ' Create a new table named Students Set tblStudents = curDatabase.CreateTableDef("Students") . . . ' Add the Students table to the current database curDatabase.TableDefs.Append tblStudents End Sub After creating the table, you must add it to the current database. To support this, the CurrentDb object is equipped with the TableDefs property. TableDefs is in fact a collection. The TableDefs collection is equipped with the Append() method that is used to add a new table to the current database. harshana-fernando.blogspot.com

23. Table Creation With DAO Private Sub cmdCreateTable_Click() Dim dbDeja As DAO.Database Dim tblEmployees As DAO.TableDef ' Open the database Set dbDeja = DBEngine.OpenDatabase("C:\My Documents\Exercise.accdb") ' Create a new TableDef object. Set tblEmployees = dbDeja.CreateTableDef("Employees") . . . ' Add the new table to the database. dbDeja.TableDefs.Append tblEmployees dbDeja.Close End Sub harshana-fernando.blogspot.com