1 / 16

Introduction to Access Database Programming

Learn about the basics of database programming using MS Access, including macros, VBA, and data access languages.

juliecwilliams
Download Presentation

Introduction to Access Database Programming

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CVEV 118/698Database Programming Lecture 1 Prof. Mounir Mabsout Elsa Sulukdjian Walid El Asmar

  2. Introduction • MS Access is a very particular commercial application for it is provided with two programming tools: Macros & VBA. • Macros can be quickly and easily developed even with a primitive programming knowledge. • However, their functionality is limited. For complex database projects, VBA should be used.

  3. VBA/Macros • Both Macros and VBA use the same set of predefined instructions for common actions, I.e. opening, closing, importing, etc. objects. • “Actions” are to Macros what “Methods” are to VBA. • The two automating tools are not totally independent, you can run macros from a VBA application and vice-versa. • Yet, each has a different way of acting on database object.

  4. VBA/Macros (Cont’d) • Macros act directly at the level of the object; except for the SetValue action that allows modifying the value of some object properties. • The DoCmd can be used in VBA to call a Macro, and vice versa. Macros Object Actions ‘SetValue’ Limitedapproach Properties Methods ‘DoCmd’Command Object enabled approach VBA

  5. VBA/Macros (Cont’d) • There are approximately 50 macro actions in Access. • The actions that can be performed are predefined, and limited in number. • Examples: • Open Report • Open Form

  6. Data Access Components • When installing Access, you actually install two major components: the Access Application Layer and the Jet Database Engine. • The Access Application is the part you interact with to display the database objects. • The Jet Database Engine is the part where you manage the data in the database.

  7. Data Access Components (Cont’d) • The Access Application Layer & the Jet Database Engine communicate with each other using Data Access Languages. • Data Access Languages • In Access, there are two Data Access Languages: Structured Query Language (SQL) and ActiveX Data Objects (ADO). • Data Access Languages work as links that access, retrieve, manipulate, etc. data.

  8. The Object Model • Remember that VBA is Object oriented programming. • Objects have Properties and Methods to modify those properties. • The object model is a hierarchical organization of objects, groups of similar objects and relations. • Access is organized into 2 separate hierarchies, one stemming from the Access Application Layer and the other from the Jet Database Engine. • Thus, Data Access Languages will function as links between those 2 trees.

  9. Access Application Hierarchy Application DoCmd Screen Forms Modules Reports References Module Module Controls Controls

  10. Jet Data Base Engine Hierarchy DBEngine Workspaces Errors Users Groups Databases TableDefs Relations Containers QueryDefs Recordsets Indexes Fields Fields Fields Fields Documents

  11. Referring to Objects by Name • One way to refer to an object is to trace its position in the object model, traversing the hierarchy from the top downward. • The exclamation point operator (!), or ‘bang’, is used to step from a collection to one of its members. • The dot operator (.) to step from an object to one of its collections.

  12. Example • Accession a form in the Access Application model: Application.Forms!NameOfForm • Don’t forget to enclose in square brackets if the name contains spaces: Application.Forms![Name Of Form] • Note that you can decrease the length of a reference by using defaults: I.e. Access assumes that you are in Access when you refeer to objects: Forms!NameOfForm Reports!NameOfReport • A dot is appended at the end of this reference protocol to access an object property.

  13. Recordset Object • A Recordset object represents the entire set of records from a base table or the results of an executed command. • At any time, the Recordset object refers to only a single record within the set as the current record. • The Recordset Object represents the entire set of records from a Table or Query

  14. Recordset Object (Cont’d) • Examples: Dim rst As New ADODB.Recordset set rst.ActiveConnection = conn rst.Open "Engineer" - OR - rst.Open "SELECT ENFName, ENLName, ENDOB FROM Engineer" • With a recordset you can access the whole set of rows in the table or query. • However, at a given moment the recordset will have a single row as the current row, and data can be read from and written only to this row.

  15. Recordset Object (Cont’d) • Navigation between rows is performed through a set of functions: • MoveFirst • MoveLast • MoveNext • MovePrevious • Data is read and written through the Fields collection. It acts on the current record. • Example: rst.Fields("ENFName") = "Karim“ rst.MoveNext Debug.Print rst.Fields("ENDOB")

  16. What’s Next • Less concepts, more methods!

More Related