How It Works

The Performance Analyzer is a wizard you can use to let Access provide some suggestions on how to improve the performance of the application. In the example, you ran the wizard against the Northwind database and discovered that some additional improvements can still be made to make the application run even faster. Tables (Ml Queries El Forms Modules L l Current Database

Access Projects Using Access as a Front End to SQL server

As a quick recap, you are already familiar with standard Access database files the MDB files. The MDB file is typically used in a standalone manner, meaning that all the tables, forms, modules, macros, and reports for the application are stored within the single file. In Chapter 7, you learned that it is also possible to use a standard Access MDB file in a client server environment by linking to external database tables such as SQL Server. An example of a client server architecture is shown in...

Building the Database

In this section, you will build the database in an Access file separate from the user interface. You learned in Chapter 10 that you can realize performance improvements by separating the user interface from the underlying data. The database containing the data tables can be placed on the same or a different computer than the one containing the user interface. If you are the only user, then having both files on the same computer is fine. If you have others with whom you must share the...

Building the Class Modules for the Objects

Throughout this book you learned that VBA code can be written in various places, such as class modules that are independent or associated with a form, as well as in standard modules. Next, you turn your attention to writing the code that will implement the desired features of the Project Tracker application. You will be creating the custom class modules first, then the standard modules, and finally the code for the forms to call the other modules. An example of how the Project Explorer will...

Connecting the User Interface to the Code

We are now ready to tie everything you have done so far together by adding the VBA code to the Customer Search and View Manage Customer Accounts forms. Most of this code will be event procedures that fire when different buttons are clicked. Some of the code will also be local procedures that deal with user-interface specific features. It just did not make sense to put these in a standard or class module. An example of the Customer Search form, called frmSearch, is shown in Figure 13.27 with...

JJ titleview View

SELtCT dbo. titles, fitie, dbo. titieauthor. au_ord. dbo.auihors.aujnarine. dbo. titles,price, dbo. tit FROM dbo. authors INNER JOIN dbo. titieauthor ON dbo. authors, auja dbo. titieauthor. au_id INNER JOIN dbo.titles ON dbo.titieauthor.title id dbo. titles, titie id To view all three portions of the display shown in Figure 9.21, you must first enable the Diagram, Grid, and SQL statement icons on the toolbar. After a view has been created, you can reference it in all the places where you could...

Introduction to Access VBA

This chapter will provide an introduction to the world of Access 2003 VBA and programming in general. More specifically, this chapter will cover Introduction to Access 2003 VBA and new features Explanation of the stages in the Systems Development Life Cycle of software development Techniques for designing applications Writing and testing VBA code using the Visual Basic Editor VBA is an acronym that stands for Visual Basic for Applications. VBA is included as part of several Microsoft products,...

Using Public Sub Procedures and Functions

Custom methods can be created just as you created methods for your own objects, only you add a public sub procedure or function to the existing object. Now add a new method for the frmCarDetails form to see how this works. Try It Out Adding New Method for the frmCarDetails Form 1. From the Visual Basic Editor, select the frmCarDetails class module. Add the following method to the class. MsgBox This is a test method added to an existing object End Sub 2. From the frmCarDetails class module, add...

Touring the Finished Customer Service Application

Now that you have written all the code for the application, let's walk through how it works in a bit more detail. Let's start with the frmSearch form, which builds a SQL statement dynamically based on the search criteria input by the user. To see how this SQL Statement is dynamically built, start by adding a breakpoint to the cmdSearch_Click event of frmSearch, as shown in Figure 13.29. Next, open the frmSearch form to run the form. Enter some criteria into the form. It is okay at this point if...

SQL Server Functions

SQL Server comes with many built-in functions such as RTrim, GetDate, and many others. You can also create user-defined functions and call those functions as if they were built-in functions of SQL Server. User-defined functions can return a single value, such as the result of a calculation, or they can return an entire table of results. You may be wondering how functions differ from stored procedures. User-defined functions have a lot in common with stored procedures because both are just SQL...

Using the Microsoft Office Object Library to Create a Custom Command

Let's look at an example of creating custom command bars for your application using the Microsoft Office 11.0 Object Library. The CommandBars property of the Application object allows you to access the CommandBars collection. The CommandBars collection enables you to manage and create toolbars, menu bars, and shortcut menus. 1. Create a new database. Select File C> New C> Blank Database. Name the database Ch10CodeExamples and click the Create button. 2. Set a reference to Microsoft Office...

The Contact Class

An object diagram for the Contacts class is shown in Figure 12.25. The Contacts class has properties that correspond to those data elements, such as those shown on the Contacts form, as well as some methods that can be executed upon it. +RetrieveContacts() +PopulatePropertiesFromRecordset() +PopulatePropertiesFromForm() +ClearObject() Try It Out Building the clsContacts Class Let's get started and build the clsContacts class module that will implement the object illustrated in Figure 12.25. 1....

Try It Out Creating a New SQL Server Table

It's your turn to try your hand at creating a new SQL Server table. 1. Create a new table, named tblProducts. To do so, select the Tables node of the Database Window and click the Create Table In Design View option. Fill in the table elements as shown in Figure 9.13. 2. After adding all the table columns and their respective data types, set the ProductId to be the primary key. To set the primary key, select the ProductId field, right-click, and select the Primary Key option in the pop-up list....

Try It Out Building the modBusiness Logic and modDatabase Logic Modules

The modBusinessLogic module is one of two standard modules you will be creating. The other one is the modDatabaseLogic that will contain calls that are specific to the database. The modBusinessLogic module will not contain any database access calls because you want to keep the data access code in a separate module to make maintenance and future growth easier. You will now turn to the task of creating these modules. 1. Insert a new standard module called modBusinessLogic. Add the following code...

Touring the Finished Project Tracker Application

You get to see the benefit of that hard work. Let's quickly look at each screen just to point out the most interesting features. You have already seen the screens in the design process, but it is always fun to see them working with real data in them. For starters, Figure 12.28 illustrates a sample project record on the Project Tracker screen. It shows details about this sample Project Tracking project and also comments that have been entered into the list on the first...

Try It Out Writing Code for the frmProjects Form

Access Form Projects

As previously mentioned, you are now ready to write the VBA code that will finish up the application. You will start with the frmProjects form and will finish with the frmContacts form. 1. Open the frmProjects form and select the Form_Load event for the form to bring up the Visual Basic editor. Add the following code to the form Set objProjects New clsProjects Set rsProjects New ADODB.Recordset 'load non-closed projects as default (open, blnAllRecords False 'make sure unclosed is enabled by...

Try It Out Controlling an Excel Spreadsheet from Access

In this example, you will create a new Excel workbook and populate some of the cells with data. 1. Add the following code to your module. Dim objExcel As New Excel.Application Dim objWorksheet As New Excel.Worksheet 'add a new workbook to the spreadsheet objExcel.Workbooks.Add 'point the worksheet variable at the active sheet Set objWorksheet objExcel.ActiveSheet 'show Excel to the user objExcel.Visible True 2. Execute the ControlExcel procedure from the Immediate Window by typing ControlExcel...

The Project Class

An object diagram is shown in Figure 12.24 for the Project class. The Contact class will be illustrated later in this chapter. -ProjectId -ProjectTitle -ProjectDescription -Priority The properties are represented in the top portion of the diagram, and the methods are shown in the bottom section. These correspond to the data elements on the form for the most part, except that the tabs with multiple records are not listed here. The methods represent various actions that you must take on the...

Using Data from Web Services

So far in this chapter, you have learned how to interact with the most common external data sources in your application. A common topic of discussion in the high-tech sector these days is Web services. Web services are reusable components that are based on standard Internet protocols. They enable systems on different platforms to talk to each other. In the simplest terms, I like to describe a Web service as some procedure or function that someone has made available over Internet protocols so...

The Basics of Writing and Testing VBA Code

Chapter 1 introduced the Access VBA programming environment and some general software development concepts. At this point, you are ready to learn the basics of writing and testing code using VBA. This chapter will cover Creating and calling procedures Using variables to store values Controlling the flow of programs Debugging and handling errors This chapter will serve as a building block for the more advanced VBA concepts covered in later chapters. The techniques covered in this chapter and in...

Access and Other Databases

You can use the TransferDatabase method of the DoCmd object to import from, link to, and export data to Access and several other databases, including SQL Server and Oracle. The basic syntax of the TransferDatabase method is shown in the following code. DoCmd.TransferDatabase TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin Various parameters are used to specify how the method should execute. The following table explains the use of each...

Building the Database and Database Objects

Tate Modern

In this section, you will build the database in an Access project, which stores the data tables in SQL Server, and the user interface in the Access project itself. You should have a version of Microsoft SQL Server installed in order to create this application. Please refer to Chapter 9 for more information on obtaining and setting up SQL Server. Try It Out Building the CustomerServiceSQL Database Now, let's build the Access Project and SQL Server database that will store the data tables. You...

Can You Use Vba To Connect In Place Of

To add a reference to a Web service, you first selected the Tools C gt Web Services References option. Because you're planning to use a service located at http terraserver-usa.com TerraService2.asmx, you selected the Web Service URL option and specified the Terra Server path in the URL field of Figure 7.7. You also could have searched for available Web services using certain keywords or specified the location of another Web service. You then selected the Terra Server Web service and clicked the...

Using ADOX to Manipulate Data Objects

The ADOX library has an object model that allows you to create database objects such as tables, indexes, and keys, as well as to control security, establish referential integrity in a database, and perform cascade updates and deletions. The Catalog object is at the top of the ADOX object model, with Tables, Groups, Users, Procedures, and Views collections. Please consult the online help for the complete ADOX object model. Just as with the ADODB library, if you want to make use of the ADOX...

Vba Code Access2003 Samples

Microsoft Access 2003 is a powerful database application that allows you to build standalone and client-server database applications. Access applications are used in many enterprises for storing inventory and maintaining customer records, sales data, or nearly any other type of data that needs tracking. Access has powerful wizards and tools that make it relatively easy for users to build a database application. However, such databases must often be expanded to include additional features not...

Working with Reports from VBA

Reports can be manipulated in numerous ways from VBA code. For example, you can change the data source on which the report is based from within VBA code. You can also create reports from scratch or modify existing reports from within VBA code. Let's look at a few examples to further illustrate these concepts. Many of the examples in this chapter that deal with manipulating existing reports use the Northwind database. If you want to create those particular examples yourself, open the Northwind...

Form Navigation and Flow

Switchboard And Forms

You can control the order and manner in which forms can be opened within your Access applications in various ways. For example, you can design switchboard forms that allow the user to select which form to open. Another option is to open a main form and allow a user to open separate forms from the main form. The style of navigation you choose depends on the type of system and the manner in which users will interact with the various forms. During the design phase, you should consider the flow of...

Using Breakpoints to Step through Code

Breakpoints can be set on one or more lines of code. When a line of code that has an associated breakpoint is reached, code execution stops and you can then choose to Step Into, Step Over, or Step Out of the code. Selecting the Step Into option from the Debug menu will run the current line of code at the breakpoint. The Step Over option will skip the current line of code at the breakpoint and move on to the next line of code. Try It Out Setting and Using Breakpoints It's your turn to set a...

Adding Controls to the Report

The CreateReportControl method allows you to add new controls to a report. The CreateReport Control method has the following basic syntax. CreateReportControl ReportName, ControlType, Section, Parent, ColumnName, Left, Top, Width, Height Now, let's modify the prior code to add four fields to the report CompanyName, ContactName, Title, and Phone. Each field needs a corresponding text box and label. The following modified procedure is one way to accomplish this. Dim rptCustomers As Access.Report...

Using Property Let Property Get and Property

Property Let, Property Get, and Property Set procedures enable you to create properties in a more flexible way than by using public variables. Property Let procedures determine what happens when you assign a value to the property. Here is a simple example showing the general syntax for a Property Let procedure. Public Property Let TestProperty ByVal strMessage As String 'code for assigning the property goes here strTestPropertyVal strMessage End Property In many cases, you assign the value of...

Class Diagrams

Before jumping into the techniques for coding custom objects, you must understand how custom objects fit into the Systems Development Life Cycle and how to identify and document what custom objects you should create. You learned in Chapter 1 that during the design phase of your application you generate a written specification for how your application should be coded. Activity Diagrams, Use Case Diagrams, and Screen Prototypes are examples of some techniques that can be used to document your...

Adding Editing and Deleting Records in a Recordset

Source or the new record will be lost. This is called working with a disconnected recordset and is explained in detail in an upcoming example. Updating an Existing Record with the Update Method The Update method of the Recordset object updates the current record. If the Update method is used in conjunction with the AddNew method, the information from the empty record is moved to the end of the recordset. If the recordset is connected to a data source, the changes are saved in the underlying...

Spreadsheets

The TransferSpreadsheet method is very similar to the TransferDatabase method in that it enables you to import, link, and export, only in this case it deals with spreadsheets. The syntax is shown in the following code. SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA Various parameters are used to specify how the method should execute. The following table explains the use of each parameter. Type of transfer to be performed. Valid choices are acImport default , acLink, and...

Building the User Interface

Access Rename Control Page

Now that the tables have been created, it's time to move on to creating the empty forms for the user interface. You can then write the code in class modules and standard modules, and finish off by adding code to each form to call the class modules and standard modules. The example in this chapter uses ADO to connect to the ProjectTrackerDb database, but you can alternatively or additionally add an explicit link to the separate database so you can see the tables from within your user interface...