Adding a Database Password
One way to add security to your database is to require a user to input a password before the database can be opened. This is called file-level security. After the database file has been opened by specifying the correct password, the user can do anything to the database unless the other security features are implemented. You have at least two ways to set a database password. One way is to select the Tools C gt Security C gt Set Database Password option. You will be prompted to enter a password...
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...
Creating a Generic Error Handler
One way of handling errors is to create a generic error handler that gets called from every sub procedure or function. Try It Out Creating a Generic Error Handler 1. Add the GeneralErrorHandler procedure that follows to the modBusinessLogic standard module. Public Sub GeneralErrorHandler lngErrNumber As Long, strErrDesc As String, strModuleSource As String, strProcedureSource As String 'build the error message string from the parameters passed in strMessage An error has occurred in the...
How It Works Fqk
You created the modBusinessLogic module for processing the business logic for the application, and the modDatabaseLogic module for communicating with the database. In the General Declarations section of the modBusinessLogic module, you added some declarations to external functions. The GetOpenFileName external function is used to display the File Open dialog box that allows you to browse the file system and select a file. This function is called later in the code to open the dialog box for...
Using Public Variables 1
As described earlier, when you create properties for your custom objects, you can also create properties for existing objects using public variables. To do so, just add the public variable declaration to the class module of the existing object, such as an existing class for a form. Again, this is not the recommended way to create properties. As discussed previously, I do not recommend this approach for three primary reasons. First, you cannot manipulate the value because the property is set...
Using Public Variables
One way to create properties for a class is to use a public variable declaration in a class module. In Chapter 3, you learned how to write a public variable declaration. When you place a public variable declaration in the Declarations section of a class module, it becomes a property for that class. Here is an example I do not recommend creating properties using public variables for several reasons. First, you cannot manipulate the value because the property is set. Second, you cannot create...
Adding Users Groups and Permissions
In addition to file-level security, Access also provides the capability to implement user-level security. This feature allows you to add various users, each of whom has her own set of permissions. For example, you may have some users who are administrators and need access to everything, whereas other users may need to view or modify only certain parts of the application. One way to add users, groups, and permissions to your database is to use the Security Wizard. You can access this wizard by...
Try It Out Creating and Using a Code Library
In this example, you will create a new Access database containing a sample error-handling procedure that you will make available to other Access databases. This error-handling procedure was described in Chapter 2. You will add the new database as a Library and then use the Library from another database. 1. Create another new database, by selecting File C gt New C gt Blank Database. Name the new database ErrorHandlerLibrary and click the Create button. 2. Insert a new module into the...
Counting Records in a Recordset
The RecordCount property of the Recordset object returns the number of records in the recordset. Suppose you have the following procedure Dim cnCh5 As ADODB.Connection Dim rsContacts As ADODB.Recordset Dim strConnection As String 'specify the connection string for connecting to the database strConnection amp _ Data Source amp CurrentProject.Path amp Ch5CodeExamples.mdb 'create a new connection instance and open it using the connection string Set cnCh5 New ADODB.Connection cnCh5.Open...
ActiveX Controls
ActiveX controls are additional controls that you can use in your Access forms. You have already worked with controls such as text boxes and command buttons. What if you need a control that is not listed in the standard toolbox Numerous additional controls are available, already installed on your computer, from Microsoft and other vendors. You can take advantage of them by adding a reference to the control and using the control in your application. For example to use a Calendar control, create...
Inserting Results of a Select into a table
Sometimes you must insert the results from a Select statement into an existing table. As long as the data types correspond with each other, data from one or more tables can easily be inserted into another table. The basic syntax is shown here INSERT INTO destination fieldname1, fieldname2, . SELECT fieldname1, fieldname2, fieldnameN If you wanted to insert the entire contents of the tblContacts table into a tblContactsArchive table, you could use the following statement INSERT INTO...
Summary Csx
In this chapter, you learned various ways to retrieve data programmatically from Access. You looked at several examples, such as creating new reports programmatically and exporting data to various Web formats. I hope you are beginning to see that there is much more to Access than just tables, forms, and reports. You have seen in this and prior chapters how to work with data in various sources, and to perform many advanced operations using VBA code. I have talked a lot so far about SQL Server,...
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
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...
Using the Call Stack
A call stack can be used to trace through the process of nested procedures. You can use the call stack window when VBA is in break mode by selecting View C gt Call Stack. However, you have to create your own call stack if you are not in Debug Mode. You have various ways to create your own call stack, such as to write various messages to the Immediate Window as you enter certain procedures to see the order in which they were called.
The Reports Collection and Report Object
The Reports collection contains all the open reports in the application, and a Report object refers to a specific report. Look at another example. The modBusinessLogic standard module can be added to the database to store the sub procedures used in the remaining examples. Suppose you have the following TestReports procedure in the modBusinessLogic standard module in your database. 'loop through each open report in the reports collection 'and display the name in the Immediate window For Each...
Select Case
Another way to implement decision making in your VBA code is to use a Select Case statement. Select Case statements can be used to easily evaluate the same variable multiple times and then take a particular action depending on the evaluation. The syntax for a Select Case statement is 'code to run if VARIABLE equals Value1 Case VALUE2 'code to run if VARIABLE equals Value2 Case Else 'code to run for remaining cases End Select Try It Out Create the TestCaseStatement Procedure Let's create a new...
Figure Fte
3. Add a new module to your database. 4. Add the following procedures to your module 'delete the command bar and if it does not exist, continue On Error Resume Next CommandBars Wrox Report .Delete On Error GoTo 0 'add the new command bar to the collection Set cbReport CommandBars.Add Wrox Report, msoBarFloating 'add Zoom button to the toolbar Set btnButton cbReport.Controls.Add msoControlButton, _ CommandBars Print Preview _ .Controls Zoom .Id Set btnButton cbReport.Controls.Add...
Optimizing VBA Code
You have various ways to accomplish the same task using VBA code, some of which are faster than others. Here are some examples Use With End With when dealing with multiple settings for the same object. Otherwise, the process slows because the object has to be located each time. It is better to use .Text Hello .Visible True .Enabled True End With txtName.Text Hello txtName.Visible True txtName.Enabled True Use the correct data type and make it only as large as you really need. For example, if...
How It Works Bws
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...
Declaring and Using Arrays
Arrays are indexed elements that have the same data type. Each array element has a unique index number. Arrays can be static or dynamic. Static arrays have a fixed number of elements, whereas dynamic arrays have the option to grow in size. The lowest index of an array is 0 by default. Here is an example of how to declare and use an array. 'declare an array with 5 elements Dim arstrPictureFile 4 As String 'populate each array element with a value arstrPictureFile O Christmas.jpg arstrPictureFile...
Viewing Objects Using the Object Browser
Now that you understand what objects and their respective properties, methods, and events are, let's look at another way to view objects. The Object Browser is a tool that allows you to view and manage the objects in your application. You can use the Object Browser to learn about the objects available in Access 2003 as well as objects available from any Windows program. The Object Browser can be opened from the Visual Basic Editor in one of three ways by choosing the Object Browser button on...
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...
Record Locking on Unbound Forms
With unbound forms, the type of locking used depends on locking the source where the data comes from. For example, if your form is based on an ADO recordset, the lock type of the recordset is what determines locking. As you may recall from Chapter 5, the LockType property of the Recordset object can be one of four settings The recordset is read-only and no changes are allowed. The record in the recordset will be locked as soon as editing begins. The record in the recordset will be locked when...
Inserting Data Using SQL Insert Statements
The Insert statement is used to insert data into a table. The syntax for an Insert statement is INSERT INTO tablename fieldname1, fieldname2, fieldnameN VALUES value1, value2, valueN The Insert clause can be used to insert all or part of a record into the database. For example, suppose the tblContacts table only has txtLastName, txtFirstName, txtWorkPhone, and txtHome Phone fields. To insert all of the record into the database in such a scenario, you could use the following INSERT INTO...
Case Study Customer Service Application
This chapter is the second of two case studies that will illustrate all the concepts covered to this point in a comprehensive real-world example. With the first case study under your belts, you now build a client-server customer service application with SQL Server storing the database and Access storing the user interface and business logic. This process includes Using Access data projects with SQL Server databases Creating and executing stored procedures with and without parameters Creating...
Introduction
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...
Raising an Error
The Err object has a Raise method that allows runtime errors to be raised when necessary. Here is the syntax Err.Raise number, source, description, helpfile, helpcontext The number argument is required for raising an error, but the other arguments are optional. Here's an example to illustrate how you can raise an error in your code. Sub TestErrRaise intValue1 As Integer, intValue2 As Integer 'declare variable to store result Dim intResult As Integer 'calculate result by dividing first value by...
Form Navigation and Flow
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...
Building User Interaction
After you have chosen the right controls, you must program ways for users to interact with those controls. You can program the application to interact with a user in various ways. For example, you have already learned how to write event procedure code that executes when the user takes a particular action on the form. In this section, you will look at a couple of additional examples of how you can improve user interaction in your applications. You will look at using the MsgBox function for more...
Using BuiltIn Functions
You have looked at some examples of creating your own functions. You should also know that VBA has an extensive list of built-in functions that you can use instead of writing the code yourself for certain common actions. One example of a built-in function that you have already used is the msgbox function. You can obtain a complete list of available functions in the VBA Help documentation, as in the help topic shown in Figure 2.21. .Microsoft Visual Basic Documentation 1 Visual Basic User...
Creating the Event Sub Procedure or Function
You must write code that executes when an event is raised. In Chapter 3, you had to create event procedures to specify what should happen when an event occurs. You now use the same process for custom objects. From the General Declarations section of the object where you wish to handle the event, you declare the object variable for the class that causes the event using the WithEvents keyword. After the object has been declared using WithEvents, the Visual Basic Editor displays the object in the...
Try It Out Sending an Outlook Email from Access
Using the tblContacts table that was created in the prior example, you will now walk through how to send an Outlook email from Access. 1. Revise the data in the tblContacts table so that only one record exists and the record points to your e-mail address. This ensures that you do not send e-mails to people by mistake based on the dummy data that you typed in the table. 2. Add the following ControlOutlook procedure to your module. Sub ControlOutlook Dim objOutlook As New Outlook.Application Dim...
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...
Types of Variables
Various types of variables can be declared and used in your procedures. The most common variables are probably String, Integer, Long, Currency, and Date, although other variables are also commonly used. The following table illustrates the various data types that are available in VBA and offers an explanation of the type of value each can store. Positive and negative currency values with 4 decimal places Date and Time from 1 1 0100 to 12 31 9999 2-byte integers from -32,768 to 32,768 4-byte...
SQL Server Stored Procedures
Stored procedures are procedures that are stored in the SQL Server database. Their purpose is to enable you to take frequently used SQL statements and save them for reuse. You can then execute a stored procedure when you need it. A stored procedure is similar in concept to the VBA procedures and SQL statements you have written so far in this book. However, stored procedures stored in the SQL Server database are more efficient than passing SQL statements to the database on the fly because they...
Navigating through a Recordset
The Recordset object has various methods that allow you to navigate among the records, assuming of course that the recordset is set to a CursorType that can be navigated. The following list contains four methods for navigation that can be used rsRecordset.MoveFirst rsRecordset.MoveLast rsRecordset.MoveNext rsRecordset.MovePrevious Before using the preceding record navigation methods, check the BOF and EOF properties of the Recordset. If the BOF property of the recordset is True, no current...
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...
The Printers Collection and Printer Object
Another useful collection in the Application object is the Printers collection. The Printers collection is a collection of Printer objects and contains all printers available to an application. You can walk through an example to see how this works. Try It Out Using the Printers Collection and Printer Object In this example, you use the Printers collection and Printer object to display the name of each printer device in the Debug window. 1. Add the following procedure to the modBusinessLogic...
Changes to an Existing Database
Before migrating your existing Access application to an Access Project that uses SQL Server, here are some tips that will make the migration go more smoothly. Consider removing any spaces in your table names and, of course, if you change the name, also change all the places that reference the table name. If you do not remove them, you will later have to use brackets to refer to SQL Server tables with spaces in their names for example, Table Name . Make sure that each table has a unique index....
XML Files
XML stands for eXtensible Markup Language. You have likely heard the XML buzzword, but you may not know what XML files really are. XML is a syntax that enables systems to create simple text documents with various tags that identify how the text should be interpreted. At the end of this section you will create an XML document from a table so you can see what one looks like. The idea behind XML is to give various types of operating systems on different platforms a meaningful way of communicating...
Using Property Let Property Get and Property Set
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...
Using Public Sub Procedures and Functions
To implement a method for a custom class, you simply add a public sub procedure or function to the class itself. You are already familiar with creating new procedures, so creating a custom method will be really easy for you. To add a method called TestMethod to a class module, you simply add the following code 'code for the method goes here End Sub Now, create the four methods for the clsCar class Retrieve, Add, Update, and Delete. You may wish to refer to Figure 4.2 to see how the class...
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...
On Error Statement
The On Error statement can be placed on your procedure to specify a section in the procedure to which the execution should jump when an error occurs. Here is an example 'normal code for the procedure goes here Exit Sub 'code for handling the error goes here Exit Sub If you want to make sure your own error handler doesn't kick in when you're debugging your code, select Tools C gt Options C gt General and enable the Break On All Errors option. This will allow you to see the root of the error and...
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...
Transactions
This section details what a transaction is and how to implement a transaction from VBA code. A transaction implements a series of changes in a single batch. The entire batch of changes either succeeds or fails as a group. Here is a simple example. Suppose that you want to make a series of updates to the database. You are updating a person's records because of a name and address change due to marriage. You may need to update two or more tables with the new information. If one or more of the...
Transferring Complete SQL Server Database
The TransferSQLDatabase method allows you to transfer an entire SQL Server database to another database. In effect, this method imports the entire SQL Server database into your Access database. Here is the basic syntax. DoCmd.TransferSQLDatabase Server, Database, UseTrustedConnection, Login, Password, TransferCopyData Various parameters are used to specify how the method should execute. The following table explains the use of each parameter. Name of new database on specified SQL Server. True if...