Creating Forms the Way
When you are ready to create a new form, you'll probably have a good idea about its general look and function based on the record source and purpose. Access 2007 gives developers a huge jump-start on form design by providing a good selection of form options on the Ribbon see Figure 10-1 . You can select an item from the Navigation pane, which will become the Record Source of the new form, and then click on one of the Ribbon's form buttons to initiate the process. Thanks to a form wizard, the...
Sending Information from Access to Excel
Access 2007 provides the capability to create forms and reports, which include graphs and tables. However, you may want to leverage some of the powerful Excel features, such as the new Charting and Conditional Formatting features new to Office 2007. Also, users may find it useful to be able to export their data in an Excel spreadsheet. The code samples for working with Excel can be found in the code behind the Export Report Manager form in the sample database. As with Outlook, using Excel...
Using Arrays with Recordsets
Sometimes you may choose to populate an array with data from a recordset. Perhaps you're intending to pass the array to a Windows API, and because APIs do not accept recordsets as parameters, this is the only way you can do it. Typically, you would define the array and then loop through the rows, appending data to the array as you went, as the following code illustrates Dim varMyArray As Variant Dim varField As Variant Set rst dbs.OpenRecordset Table1, dbOpenSnapshot rst.MoveLast ReDim...
The Access Object Model
By now you've probably read at least a few of the chapters in this book and have realized that there are a lot of tasks you can accomplish by programming in VBA. One concept that should be abundantly clear is that to use VBA to manipulate Access, you need some knowledge about the Access object model. It was discussed briefly in Chapter 4, but as a refresher, an object model is a set of objects and collections that programmers use to manipulate a program. Microsoft Access has a rich object...
The Fundamentals of the Hungarian Convention
The Hungarian convention has a very straightforward design. It dictates that a name may contain up to five parts, and that the parts are combined in the order of prefix es , tag, base name, qualifier, and suffix A prefix precedes a tag to provide clarification. It can describe one or more important properties with one or two lowercase characters. A tag is considered by some to be the critical, non-optional element. A tag should be unique and easily differentiated from similarly named tags. A...
Adding Editing and Deleting Rows
Not all recordsets are editable, and the same can be said about some rows. Snapshot recordsets are never editable, and user permissions and record locks can result in recordsets or individual rows that you cannot edit. In addition, joins in some recordsets that are based on multiple tables can render the entire recordset uneditable. The procedure for adding rows to a recordset is quite simple Open the recordset, issue the recordset's AddNew method, make the additions, and then issue the Update...
Opening Published Databases Via Code
Access provides the OpenCurrentDatabase method to open a database, including databases that have been published to SharePoint or other types of websites. OpenCurrentDatabase is a member of the Application object and takes three parameters, two of which are optional. The following table describes those parameters. The full name and path or URL string to the database to be opened. Required. The Boolean value which determines whether the database should be opened in exclusive mode. Optional, and...
Data Source Task Pane
The Data Source task pane is a fast, easy way to manage record sources. It's better than the field list because the task pane shows all of the source fields, and you have the option of viewing source, related, and other tables. That means the pane displays the tables and queries so that you can easily select a field. If you have a form and you want a combo box for a look-up field, for example, use the source, related, and other tables to select a field and drag it onto the form. Voil , a combo...
Control Object Methods
The methods you can use with a control object are explained in the following table. For all of these methods, the specified object is the control whose method is being called. Forces the list in the specified combo box to drop down. Moves the specified object to the coordinates specified. Updates the data behind a control by requerying the source data for the control. Moves the focus to the specified control. Sizes the control to fit the text or image it contains. Resets a control whose value...
DictionaryStyle Headings
Phone books and dictionaries typically add the first entry and last entry at the top of the page to make it easier to find information. You can accomplish this on your reports by adding some code in several different sections. The code uses the Format event to create listings at the top of each page. Because this event does not fire for reports open in Report view, you'll need to open the report in Print Preview to see the effect. The report you'll create is a listing of the attendees for the...
Other Considerations When Converting
Keep in mind that saving to a different file format is only a small part of the process. As already discussed, there may be issues involving code, references, macros, security, and integrating with other applications. Moving to newer versions is certainly easier than moving backward. Newer features may be lost or have only part of their functionality. Custom features may not be recognized or implemented as expected. Despite those concerns, it is certainly handy to have the ability to save a...
Creating Class Methods
Class modules have subs and functions, but to give the impression that they're somewhat special, they're called methods. It makes some sense when you consider that a class's procedures carry out actions on its properties, and therefore, constitute the method by which those actions are executed. In the same way that methods are executed against objects in the Access object model, class methods are executed against class objects. For example, to move a DAO recordset cursor to the next record, you...
The Function to Set a Key Value
The next procedure, SetKeyValue , wraps both RegOpenKeyEx and RegSetValueEx functions to open the subkey and set its value, respectively. After each function call, it checks that the call completed successfully, and if not, raises a custom error and returns a Boolean False. If the call completes successfully, it returns a Boolean True. Public Function SetKeyValue lngRootKey As w32Key, _ strSubKey As String, _ strValueName As String, _ strNewValue As String _ As Boolean lngReturn RegOpenKeyEx...
Filtering and Ordering Recordsets
Whenever you work on records in a database, it is rare that you want to carry out an action on the entire table. If you did, you would be best served by using an action query because queries operate much faster on large numbers of rows than do row processing methods recordsets . However, it is more likely that you'll want to do something with a subset of records, and that means you would need to filter your query to select only those records that you wanted to work on. With recordsets, you have...
Sharing Application Files
For Access applications designed to support multi-user scenarios, it is usually recommended that a front-end database file be installed on each user's local machine and that data be linked to a back-end ACCDB MDB file stored on the network location. Access does support opening ACCDB MDB files over the network, and applications are often shared in this manner from a central network location. Sharing ACCDB MDB files over the network usually works well when only a small number of users are working...
Starting New or Choosing an Existing Template
The first step in the Package Solution Wizard is to identify whether to create a new template or use an existing template. If an existing template is selected, it can be run without modification. If there is an existing template available, you can apply the template by selecting Load Wizard Settings From Saved Template File. Choosing an existing template sets all of the options that are defined by that particular template. Still, most people prefer to make a custom template for their particular...
Reserved Words and Special Characters
There are numerous words that should not be used to name fields, objects, and variables. For the most part, these are called reserved words. Reserved words have a specific meaning to Microsoft Access, the Jet database engine, and the new Access database engine ACE . We also list reserved words that have specific meaning to SQL Server or ODBC drivers. Depending on how your application interfaces with other programs, it may be prudent to avoid using words that have specific programmatic meanings...
Step Database Solution Installation Options
The second page of the wizard enables you to specify the details about the installation options for your database solution, including the path to the database solution that will be packaged and the location where the solution will be installed on the target system. You also can specify whether the Redistributable Access Runtime package is to be included or required with this installation. The last preference on this page enables you to specify is the shortcut installation options for the...
RegNotifyChangeKeyValue
Description Provides the mechanism to be notified when a Registry key or any of its Declaration Declare Function RegNotifyChangeKeyValue _ Lib advapi32.dll _ ByVal hKey As Long, _ ByVal bWatchSubtree As Long, _ ByVal dwNotifyFilter As Long, _ ByVal hEvent As Long, _ ByVal fAsynchronus As Long As Long Parameters hKey Long Integer The handle of the key to watch, or one of the lpWatchSubTree Long Integer Boolean flag that indicates whether to watch the subkeys for change. Zero Do not watch...
Event Properties Where Does the Code Go
The power of a form is often derived from responding to the user. Typically, a response is based on intentional input. But VBA can also respond to ancillary or inadvertent actions. The most common place to use code is in the event properties of the form or control. This is frequently referred to as the code behind the form. It isn't enough to have the code right the code also has to be behind the right event or it may not be triggered. Time and again, the problem of code that just doesn't work...
Late Binding
Another timing issue that can have significant impact on performance is whether the form is using early or late binding. Basically, this refers to when the record or recordset is created for a form or other object. Late binding typically involves filtering, which reduces the size of the recordset and also allows the developer greater control of the record source. When you add fields to your form, the text boxes that are created become bound text boxes. That is, the table or query fields are...
Calling the Record Finder Code
The On Click event of the First button shown in Figure M-4 includes this code Private Sub cmdFirst_Click On Error GoTo Error_Handler DisplayUnexpectedError Err.Number, Err.Description The code really has only one operative line The code behind cmdNext_Click is almost identical. Instead of using first as the parameter for FindRecordLike, it sends in next Now here's the code for the subroutine FindRecordLike. It also resides in the index form that contains the record finder controls shown in...
The Function to Get a Key Value
The GetKeyValue procedure wraps both RegOpenKeyEx and RegQueryValueEx functions, which open the subkey and retrieve its value, respectively. Again, after each function call, it checks if the call completed successfully, and if not, raises a custom error and returns a Null value. If the call does complete successfully, it returns the current value. Public Function GetKeyValue lngRootKey As w32Key, _ strSubKey As String, _ strValueName As String _ As Variant lngReturn RegOpenKeyEx lngRootKey,...
Methods of the Parameters Collection
There are exactly three methods for the Parameters collection. They are described in the following table. Used to add a Parameter object to the collection. Updates all of the Parameter objects in the collection with the latest information from the provider. Deletes a Parameter object from the collection. The Index value is either the name or ordinal position of the Parameter in the collection.
Basic Error Handling with an Extra Resume
One of the problems with basic error handling is that when an error does occur, you have no easy way of knowing the exact line that caused the error. After all, your procedure may have dozens or hundreds of lines of code. When you see the error message, the execution of your code has already jumped to your error handler routine and displayed the message box you may not be able to tell which line caused the problem. Many programmers rerun the code, using debug mode, to step through the code to...
Setting Permissions on Database Objects
After the workgroup information has been established and all of the groups have been created, the database object for the particular MDB file needs to have permissions applied. The User and Group Permissions dialog box can be used to add permissions to any database currently opened in Access, based on the workgroup information loaded for that session. Of course, the person modifying the permissions is required to have the proper permissions to modify those objects. If the database were created...
RegQueryReflectionKey
Determines whether Registry reflection is enabled for the specified key. Requires Windows XP Professional x64 Edition or Windows Vista or later. Declare Function RegQueryReflectionKey _ Lib advapi32.dll _ ByVal hBase As Long, _ bIsReflectionDisabled As Long As Long hBase - Long Integer Handle to the Registry key to query. bIsReflectionDisabled - Long Integer A value that determines whether Registry reflection is enabled or disabled for the specified key. Long Integer Zero ERROR_SUCCESS on...
The SubReport Object
Much like forms and subforms, reports can also contain subreports. The SubReport object has no methods, and its events are the same as the events for the SubForm object. The SubReport object has four properties, described in the following table. The SubReport object has four properties, described in the following table. Returns the currently active application object. Refers to the form associated with a SubReport object. Refers to the parent of the selected subform. Refers to the report...
Creating the Signed Package
Open any ACCDB database file, click the Office button, and select Publish O Sign and Package. The Select Certificate dialog box opens. After you select the certificate you wish to use to sign the package and click OK, you will be asked to provide a location for the package file as shown in Figure 22-21. Remember the location for the signed package you'll use it in the next section. Click the Create button to save the package file. Access takes the database file and compresses it into a package...
Using SQL for Report Selection Criteria
Many developers build Access reports so that their users can quickly view and print out their data. Consider a report to list businesses from the database, as shown in Figure 15-6. Some reports are designed to show all the records in a table or query. However, your user will often want to print only some of the records, based on selection criteria. You can create a different report for each selection criteria, but that approach will result in duplication of report code and difficulty in...
Anatomy of the VBA Editor
You can access the VBA Editor in several ways. From anywhere in Microsoft Access, press Alt F11 or choose from several places on Access's new Ribbon Create, Macros, Modules, Database Tools, or Visual Basic. You can also open the VBA Editor by double-clicking a module name in the navigation pane or from any form or report. From the Properties dialog box, click the Events tab, select the event that you're interested in, click the Ellipses button , and choose Code Builder. When you first view the...
Advanced Options
Use the Advanced Options section to provide three last options for the MSI file Background Image The file path to a custom image to be displayed in the MSI installation wizard pages, instead of the default image. The standard size for this image should be 500x400 pixels. Product Code A GUID that uniquely defines the installation package. A default GUID is generated for a new database solution, but you can add a custom value by typing it into this field of the wizard. Upgrade Code A GUID that...
Open with Access for NonTemplate Linked Lists
Using Open with Access for any SharePoint list types other than the Issue Tracking, Tasks, and Contacts lists and choosing linked tables will simply create new linked tables in a new database or, if chosen, an existing database. When completing this operation, the user always gets two lists the primary list from which the Open with Access option was invoked and the User Information List, which describes information about the users of the SharePoint site. These linked tables are standard...
The ConnectionExecute Method
The Connection object's Execute method takes three parameters and returns a recordset object containing any records the command may have returned. The parameters for this method on the Connection object are different than the parameters for the Command object's Execute method. They are described in the following table Required. A String data type containing the command statement to be executed. RecordsAffected Optional. A Long data type out parameter that contains the number of Optional. A Long...
ACCDR Runtime Files
Access 2007 will also support runtime mode. This locked down version limits users' access to commands and to make design changes. In the past, a command line switch would tell Access to open in runtime mode. With the new Jet and the ACCDB file format, switching to a runtime format is even easier, because all it requires is changing the file extension to .accdr. To return to the full-feature environment, merely change the file extension back to .accdb. Of course, this still requires either a...
Why Do We Have Disabled Mode
Cognizant that we live in a time when computer security is becoming more and more critical. Microsoft added the capability to inspect a database without the risk of malicious code running. As a result, if you used Access 2003, you'll immediately see that all of the prompts you received when opening a database have been removed. Even in Access 2003, opening a database is all or nothing there is no way to open the database in a secure manner such that code does not execute. Of course, you could...
Understanding Query Options in SQL Server
The three types of query objects in SQL Server that can be used from an ADP are views, stored procedures, and functions. Each of these types has its own unique strengths that can be leveraged in an Access application. Views can be thought of as virtual tables. Views can be used in the same manner as regular tables in query objects. The benefit of views is that they can be based on more than one source table and can be limited to include only the fields needed for a particular action. In...
Registry Root Key Hives
In the Registry, a hive is a node that often contains other keys and values. There are several root keys that you can use for functions that require that a root key be supplied for one of the parameters. These keys are defined as follows Const HKEY_CLASSES_ROOT As Long amp H80000000 Const HKEY_CURRENT_C0NFIG As Long amp H80000005 Const HKEY_CURRENT_USER As Long amp H80000001 Const HKEY_DYN_DATA As Long amp H80000006 Const HKEY_LOCAL_MACHINE As Long amp H80000002 Const HKEY_PERF_ROOT As Long...
Using DAO to Set the Database Password
Creating VBA code to use DAO to add, modify, or remove a database password is easy and it can be done with just a few lines of code. DAO provides the NewPassword method, which can be called from the Database object to set the database password. As with the Access UI, the database must be opened in exclusive mode to complete this operation. Notice that the DAO code used in Access 2007 is exactly the same as setting the database passwords for the MDB file format. The following...
The Errors Collection
The first thing to remember about the DAO Errors collection is that it is not the same as the VBA.Err object. The VBA.Err object is a single object that stores information about the last VBA error. The DAO Errors collection stores information about the last DAO error. Any operation performed on any DAO object can generate one or more errors. The DBEngine.Errors collection stores all the error objects that are added as the result of an error that occurs during a single DAO operation. Each Error...
File Conversion Using Access A OneStop Shop
Access 2007 has essentially one process to manage database conversion. With just a few clicks, a copy of a database can be created to be compatible with and used by multiple versions of Access. This is definitely a time to appreciate simplicity because the process is as easy as changing a picture from a 5MB BMP to a 300KB JPG. In addition to converting to and from the .ACCDB file format, Access 2007 will convert to and from .mdb files with 2000 and 2002-2003 formats. There are extra steps and...
RegSetKeyValue
Description Sets a value in the specified Registry key and subkey. Requires Windows Declaration ' String declaration of lpData Private Declare Function RegSetKeyValueString _ Lib advapi32.dll _ Alias RegSetKeyValueA _ ByVal hKey As Long, _ ByVal lpSubKey As String, _ ByVal lpValueName As String, _ ByVal dwType As Long, _ ByVal lpData As String, _ ByVal cbData As Long As Long ' Numeric declaration of lpData Private Declare Function RegSetKeyValueLong Lib advapi32.dll _ Alias RegSetKeyValueA _...
Track this List in Access
The Track this List in Access feature is a quick link that enables the user to open the list in a new or existing database. Choosing this option always creates a SharePoint linked table to the list in the database. This feature is designed as an easy way to create a SharePoint linked table in an Access database, directly from the SharePoint list. Introduced in Access 2003, the Track this List in Access link, was originally called Create Linked Table In Access. Access 2003 users will see the...
Creating Unbound Forms
It is usually easier to create a normal form and then convert it to an unbound form than it is to create an unbound form from scratch. When the form is bound, you can use built-in form design tools to drag-and-drop fields to build the forms to the desired layout. This technique minimizes the chance for misspelling a field name and decreases creation time. Once the controls for displaying the data have been added to the form, you can easily convert it to an unbound form by setting the Record...
Cleaning Up
Although Access VBA is supposed to automatically clean up local objects when a procedure ends, there is a history of errors and exceptions to this. So, programmers have learned that the safest practice is to clean up everything themselves. It's boring, but it shows an attention to detail that is missing in many novice applications. To clean up recordsets, make sure that you Close the recordset using the .Close method. Release the recordset object by setting it to Nothing. These two easy steps...
Enabling a Database
When Access opens a database, it gives certain information, known as evidence, to the Trust Center. For Access, the evidence includes the location of the file and the digital signature of the database if it has one. The Trust Center takes the evidence and makes a trust decision based on certain logic. The decision is then given to Access, which opens the database in either Disabled or Enabled mode as needed. Figure 22-9 illustrates the logic for determining whether a database will open in...
Containers Collection
The Containers collection contains all of the Container objects that are defined in a database Microsoft Access databases only . Returns a count of Container objects in the collection
Referencing a Library
When you set a reference to an external library in Access 2007, you can use its functions as though they were built-in to Access. You can reference type libraries, object libraries, and control libraries. For example, because the Microsoft Excel Object Library is itself a library of functions, you can reference link to it in Access and use its functions as though they were part of Access. To reference a library, select Tools O References in the Visual Basic Editor. The References dialog box...
Getting Started
Upon opening Access, users are greeted by an intuitive graphical interface that displays links to a new database, recent files, and the template groups. Clicking on a template group personal, business, or educational displays related templates. When a template is chosen, the pane on the right prompts and guides the user through downloading the file or creating the database and linking it to a SharePoint site. There'll be more about SharePoint shortly. For now, suffice it to say that the program...
Stepping Through Code
In most cases, you design code to run with little or no user intervention. However, when you're testing code, sometimes it is helpful to do more than insert a couple of breakpoints or include some Debug.Print statements. If you're running code with several variable changes or some intricate looping, it can sometimes be helpful to step through the code line by line. Doing this allows you to watch the value of variables after each line of code is executed. This can help you pinpoint any errors or...