Overview of the ADO Object Model

The ADO Object Model contains five main objects the Connection, Command, Recordset, Record, and the Stream objects. Although you've already explored some examples that use the Connection object to execute actions on a data source, that's really just the beginning of the ADO functionality. These other ADO objects provide much of the rich and powerful functionality of the ADO library. As already mentioned, the ADO Connection object stores the information that is necessary to describe the...

R

Raise method, class modules, 450-451 RDBMS. See Relational database management system Record object, 200, 886-887 record selector, search from, 42 RecordCount property, 168-169, 209 records find, 1054-1064 code calling, 1055-1057 concatenation, 1062-1063 handling quotes, 1063 record finder code, 1057-1062 RecordsetClone, 1062 setting focus, 1063-1064 preload, 1067-1071 Recordset object ACCDB MDB v. ADf 670 ADO, 200,204-218, 880-883 Open method, 891-892 arrays with, 181-182 client-server...

Registry Organization on xBased Windows

With the introduction of Windows XP Professional x64 Edition, a separate view was added to the Registry for 32-bit applications to prevent data in 64-bit Registry keys from being overwritten by keys and values installed by 32-bit programs. Program settings for 64-bit programs are still stored in HKEY_LOCAL_MACHINE Software, while settings for 32-bit programs run are now stored in a new key called The Microsoft documentation frequently uses the term WOW64 which stands for Windows32-on-Windows...

Working with Data in Excel

A common scenario in any company is to communicate public data via charts, tables, and graphs through periodic reports. By allowing users to export data directly to Excel, you empower them to leverage the rich set of Excel features to create their own charts and tables at a whim, without having to modify the data or design of the architecture of your Access database solution. The first example here utilizes a form with a list box control that exposes Queries within the application. The Row...

Reading Permissions

As mentioned earlier, object permissions are stored in two main places the Permissions property of Document objects, and the Permissions property of Container objects, the latter being where the permissions for future objects are defined. But before you get too carried away with this new found knowledge, you might be interested to know that object permissions are stored in a Long Integer bit field. To get at individual permissions, you need to perform a bitwise operation, which is not very...

Module Object Methods

The methods for the Module object are listed in the following table. Adds the contents of the text file to a module. Adds the contents of the string to a module. Creates an event procedure in a class module. Finds the specified text in a class module. Inserts a line or group of lines of code in a module. Inserts a string of text into a module. ReplaceLine Line, String Replaces the specified line with a string value. ReplaceLine Line, String Replaces the specified line with a string value.

Forms as Objects

By now you should have a fair grasp on how to create classes and class objects in Access 2007. Something you might not be aware of is the fact that because form and report modules are also class modules, you can instantiate and use them in exactly the same way as any other class object. The greatest benefits of this are that you can create and operate on more than one instance of the object at any one time, and you can use its events by declaring their object variables using the WithEvents...

Access Database Templates

Access Database Issue Tracking

New to Access 2007, database templates are a great starting point for a simple database solution. Several different types of business and personal database templates are installed with Access and more are available from Office Online. Some of the different types of database applications you can create include Assets For tracking tangible items. Contacts For tracking people or organizations. Events For tracking important dates. Issues For tracking assignable issues or problems. Tasks For...

Maintaining Groups with DAO

The AddGroupDAO procedure adds groups to the MDW file. The RemoveGroupDAO procedure drops groups from the MDW file. Adding groups to the MDW file does not modify the original database in any way, only the MDW file. To add a group, create an object of type Group, set the properties for it, and then append the group to the Groups collection for the workspace Public Sub AddGroupDAO strGroupName As String, strGID ' Define variables Dim ws As Workspace Dim grp As New Group ' Create a workspace...

Creating Forms the

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...

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...

Reg NotifyChange KeyValue

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...

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...

Reg QueryReflection Key

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 Sub Report 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...

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...

Open with Access for Non Template 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...

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...

Reg SetKey Value

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 _...

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...

The Database To Be Secured

The second component of user-level security is the database that is secured at the user-level by granting permissions to each object in that database. Permissions authorize the actions that can be taken on an object. They are granted to Access users based on the pass codes in the MDW file that are in use when user-level security is set up. An Access user does not use the MDW file until he opens a database. If no MDW file is explicitly specified, the default joined MDW file is used. However, the...

Setting Focus from Afar

To help the user use the Find routine efficiently, control the focus so that keystrokes make sense. For example, if the user clicks the First button and no match is found, you know that he's probably going to want to change the search phrase to something else. To help him, set the focus to the Search Text control MsgBox No matches found., vbOKOnly, Record Finder ctlSearchText.SetFocus Remember that this code is not in the form the user is viewing you are controlling focus from this procedure...

Know the Rules Program with Confidence

There's no need to feel intimidated by the API, despite the fact that it has the reputation of being highly complex. Because the Windows APIs are written in C C , VB programmers must be aware of certain rules, but other than that, the APIs can pretty much be used in the same way as any other function. For example, enter the following example into a standard module and run it For example, enter the following example into a standard module and run it Private Declare Function GetUserName _ Lib...

Get Setting

You can use the GetSetting function to retrieve a string value from a single Registry key that you have previously saved. It returns the value specified by the default argument if the key is empty or doesn't exist. GetSetting has the following syntax GetSetting appname, section, key, default Chapter 20 Working with the Win32 Registry are explained in the following table. A required string expression that contains the name of the application or project whose key is being sought. A required...

Customizing the Office Menu

As mentioned earlier, the Office menu is updated when you create a new Ribbon from scratch. In addition to the changes made by the Ribbon, you can also customize the Office menu to suit your needs. In Office 2007, the Office menu is intended for options that affect an entire document or application. To customize the Office Menu, use the officeMenu node, which is a child of the Ribbon node as shown below. lt customUI lt ribbon startFromScratch true gt lt officeMenu gt lt button idMso...

Sandbox Mode Limitations

Sandbox mode blocks VBA functions or commands that could be harmful to a computer. They're blocked by the Access database engine when they are executed from a SQL query or other expressions in controls, forms, reports, or macros. Here's a list of functions that are blocked when sandbox mode is enabled The Microsoft Knowledge Base has an excellent article that describes the sandbox mode as well as expressions that are blocked when the Sandbox is enabled at http support.microsoft.com kb 294698 ....

Handling Rounding Issues

Rounding problems are among the most difficult to understand and debug. They usually occur when adding up money values, but they can also happen in any math where a series of values is expected to add up correctly. One basic issue is not Access-related at all, but rather an issue whenever you add up a list of rounded numbers. For example, take a list of numbers that each represent one third of a dollar. If you add them up, you'll get 99 cents because the value of each portion .33333333 was...

Reg DisableReflection Key

Description Disables Registry reflection for the specified Registry key. Disabling reflection for a key does not affect reflection for any subkeys. Requires Windows XP Professional x64 Edition or Windows Vista. Declare Function RegDisableReflectionKey _ hBase - Long Integer Handle of the open key, or one of the hive con Long Integer Zero ERROR_SUCCESS on success. All other values are the specific error code.

Splitting a Database

Speaking of shared data files prompts a discussion of splitting the database, or moving the tables to their own file. It's not uncommon to initiate the database design with the tables in the same file as the other objects. And although it works fine to keep it that way for small, single-user applications, it isn't advisable for larger applications or for shared files. Although an application can allow simultaneous use by multiple users, that can lead to significant performance and corruption...

Setting and Retrieving User Defined Properties

You can also create and use user-defined properties for other purposes. A lot of developers often use a custom database property to record the database version. As with the example of a field's Description property, there are two ways to create a user-defined property using the user interface, and through code. To create such a property with the user interface, click the Office button and select Manage O Database Properties. The Properties dialog box displays, as shown in Figure 6-5. Select the...

All Objects Collection

Access contains a hidden collection called the AllObjects collection. Chances are you'll never use this collection directly, but it happens to be the parent object of several collections that are commonly used. These collections are Contains an object for each database diagram in a SQL Server database. Applies to ADP files only. Contains an object for each form in a database or project file. Contains an object for each user-defined function in a SQL Server database. Applies to ADP files only....

Creating Accdt Files

One of my favorite new features in Microsoft Access 2007 is the support for database templates, which allows you to create your own custom templates. The ADE provides a nice tool for creating a template using almost any existing database. Most features in the ACCDB file format are supported by the new ACCDT file format. Because everything in the application is contained in one physical file, deployment is no problem because it's all done through user input from the Getting Started window. You...

The Package Solution Wizard

The Package Solution Wizard is an extremely handy tool for building Access application installation programs. It creates a standard Microsoft Windows Installation package MSI file that can perform a number of useful setup tasks, such as deploying Access Runtime and developer-specified application files, adding Windows System Registry keys, and even including digital certificates for the client machine. Employing this wizard greatly reduces the cost and headache of building a streamlined setup...

Reg QueryValueEx

Description Retrieves both the type and the value for the specified key. This is a more sophisticated function that RegQueryValue, and is recommended for use on Win32. Declaration Declare Function RegQueryValueEx _ Lib advapi32.dll _ Alias RegQueryValueExA _ ByVal hKey As Long, _ ByVal lpValueName As String, _ ByVal lpReserved As Long, _ lpType As Long, _ lpData As Any, _ lpcbData As Long As Long Parameters hKey Long Integer Handle of an open key, or one of the hive con lpValueName String Name...

ACCDE and MDE Files

Access 2007 will create either an .mde or an .accde file, depending on which file type is open. Both files compile and lock down the code, so it cannot be viewed or modified. Any future changes have to be made to the originating .mdb or .accdb file and then a new .accde or .mde file will need to be created. Because the steps are essentially the same, this section will only provide the steps for creating an .accde file. It takes just six steps to create an ACCDE file in Office Access 2007. 1....

Maintaining Users with DAO

The AddUserDAO and RemoveUserDAO procedures add and drop users from the MDW. As with groups, adding users to the MDW does not modify the original database in any way, only the MDW file. To add a user, create an object of type User, set the properties for it, and then append the user to the Users collection of the Workspace Public Sub AddUserDAO strUserName As String, strPassword As String, strPID As String ' Define variables Dim ws As Workspace Dim usr As New User ' Create a workspace object...

Using the Ribbon Schema

Startfromscratch True

The schema file for ribbon customizations is called customui.xsd. Using Visual Web Developer, the first thing to do is create a new XML file. Once the file is created, click the button in the Schemas property to open the Schemas dialog box. Click the Add button at the bottom of the dialog box to browse to the customui.xsd XML schema that is included in the 2007 Office System XML Schema Reference. Figure 12-2 shows the schema file added to the dialog box. Check the box for each schema you would...

Field Column History

Many of you have created a variety of techniques to track the changes to data stored in a field. Now, Access provides a built-in solution Column History. Column History is a new property on the Application object that uses an AppendOnly field property which must be set to True on memo fields to add new data with a date stamp. The data is actually stored as multiple entries in a separate table, so it can be filtered and sorted. The options for retrieving the Column History are limited, but it is...

The ADOX Object Model

The ADOX model contains one top-level object, Catalog, which contains five collections Tables, Groups, Users, Procedures, and Views, as illustrated in Figure 7-3. Each of the Table, Index, and Column objects also has a standard ADO Properties collection, as shown in Figure 7-4.

Now What About Those Clones

As mentioned earlier, a clone is a functional replica of the original. Now let's take a closer look at how to use them. There are two clone methods Clone and RecordsetClone. Clone is a method of the Recordset object, whereas RecordsetClone is a property of the Access Form object. Both are identical in function, except that you can't set the Filter or Sort properties for recordsets created using the RecordSetClone property. Microsoft states in the online help that the recordset returned by the...

Using VBA to Set Up Your Merge Document

Creating the mail merge using the Word Object Model really isn't that difficult, but it does require a little more code behind your form. Consider that automating the creation of the Mail merge document in Word through code may be easier and more practical for users because they won't have to create the merge document manually. The following code sample creates the merge document from a blank document, adds the merge fields, and finally, merges the data. Dim objWordApp As Word.Application Dim...

Windows API Reference Information

So now you probably know enough about using the Win32 API to get yourself into some serious trouble. The trick is to find information about the APIs that are available for use, and learn how to use them with VBA. Unlike programming languages, information about the Windows API is somewhat harder to find. There are literally hundreds of API functions included in the Windows operating system, and the information that is available is mostly incomplete. There are also quite a few API functions that...

The Collection Object

You are familiar with using object collections VBA is full of them. The TableDefs and QueryDefs collections are examples you use almost every day. These collections maintain a list of pointers to the individual objects they control, in fact collection objects are also referred to as Controllers. To access an individual object within an object collection, you refer to its collection name and either the name of one of the objects it contains, or its ordinal position within the collection. For...

Sorting on Columns

Users often expect the capability to sort on columns, similar to other Windows applications such as Outlook and Excel. For example, if you have an index form of businesses, your user may want to sort on either the Business Name or Contact Name column, as shown in Figure 15-1. The two toggle buttons Business Name and Contact Name are in an option group control called optSort, which has an After Update event that contains the following code Private Sub optSort_AfterUpdate On Error GoTo...

Print Event

The Print event is fired after Access has finished formatting a section for printing, but before it is sent to the printer. The Print event handler is passed two parameters Cancel Set to True to cancel sending the section to the printer. PrintCount Reports how many times the Print event handler has been called for the section item. Typically, this is set to 1, meaning this call is the first time the handler has been called by the layout engine. The following code shows you how to accumulate...

Working with Data on Share Point

Working data on a SharePoint server can mean several different things. The options run the gamut from having the entire application on SharePoint, including the front end and forms, to just relying on data backup and revision management features. With the caveat that SharePoint can protect the data and enforce permissions, the following sections focus on two of the most common configurations. Publishing the data file to SharePoint but keeping the application on a local workstation could be...

Using Access Macros

As an alternative to using an expression, you can call an Access macro from the onAction callback. Using macros, you can create portions of the application that are available when the database is disabled. For more information about disabled mode, please see Chapter 22. To specify an Access macro, simply use the name of the macro in the onAction attribute, as shown in the following XML lt button id btnMacro label onAction macro You'll probably want to write most of your callbacks using VBA. As...

DAO Field Types

The following table lists the constants in the DAO DataTypeEnum and their corresponding data types in the Access Table designer. Cannot create in Access, but can be used by linked tables Cannot create in Access designer but can be created in DAO Cannot create in Access, but can be used by linked tables Cannot create in DAO but can create in Access designer Cannot create in DAO but can create in Access designer Cannot create in Access, but can be used by linked tables Cannot create in Access,...

Launching and Using the Registry Editor

You won't find the Registry Editor on the Start menu because it's not something that Microsoft wants the average user to fool around with. The only way to launch it is via the Run dialog box. Here's how 1. Click the Start button and select Run, or press the key combination Windows R. The Run dialog box displays. 2. Type regedit or regedt32, and then click OK. The Registry Editor opens. To launch the 32-bit version of RegEdit.exe on x64-based versions of Windows, type windir SYSWOW64 regedit in...

Reg CopyTree

Description Copies values and subkeys from the specified key to the specified desti nation key. Requires Windows Vista and administrator privileges to copy the key. Private Declare Function RegCopyTree _ Lib advapi32.dll Alias RegCopyTreeA _ ByVal hKeySrc As Long, _ ByVal lpSubKey As String, _ ByVal hKeyDest As Long As Long hKeySrc - Long Integer Handle of the open key, or one of the hive constants listed earlier. lpSubKey - String Name of a subkey to copy under the key specified by hKeySrc....

Creating a Query Def

To create a QueryDef, execute the CreateQueryDef method against the Database object. In Microsoft Access workspaces, if you set a QueryDef's Name property to something other than a zero-length string, it is automatically appended to the QueryDefs collection, and saved to disk. Omitting the Name property, or explicitly setting it to a zero-length string, results in a temporary unsaved QueryDef. The following code demonstrates how to create a QueryDef in a Microsoft Access workspace Public Sub...

How Microsoft Access Resolves VBA References

When Access needs to use the file you've referenced, it does so in the following sequence 1. It checks the location indicated in the References dialog box. 2. It checks to see if the file is already loaded. 3. It checks the RefLibPaths Registry key for a value in the name of the referenced file. 4. If the RefLibPaths key does not exist, or doesn't contain the required value, Access checks the Search Path in the following order a. Application folder where msaccess.exe is located C. System folder...

Share Point Linked Tables in an ACCDB

The ACCDB file format has improved Linked Tables To SharePoint to an unmatched level of support compared to any other database product. Linked tables in Access support all SharePoint data types, including multiple value fields, attachment fields, append-only fields, and rich text fields. Probably the biggest difference between importing and linking tables to SharePoint lists is that all Lookup tables are automatically created as linked tables when the primary linked table is created....

Recordset Object

The Recordset2 object represents the records in a base table, or those that result from executing a query. The Recordset2 object was added in Access 2007 to support multi-valued lookup fields and attachment fields. Begins a recordset editing session that creates a new record for an updatable Recordset object. Cancels any pending updates for a Recordset object. Creates a new Recordset object that is a duplicate of the original Recordset object. Creates a new QueryDef object that is a copy of the...

Creating Custom Class Events

You can, of course, create your own events. Once you've decided on the specific events you want to expose, you declare them in the class's declarations section. Let's say you have a class called clsTest that implements a test given by the teacher. You may want to provide events that notify your code before and after a test is given. Events are declared Public by default, but for clarity, you might want to explicitly declare scope. In any case, nothing outside the class would ever know about an...

Creating Object Properties

You can create user-defined properties for persistent DAO objects, such as tables and queries. You can't create properties for nonpersistent objects, such as recordsets. To create a user-defined property, you must first create the property, using the Database's CreateProperty method. You then append the property using the Properties collection's Append method. That's all there is to it. Using the example of a field's Description property, the following code demonstrates just how easy it is...

Linking to External Data

Select Data Source Access 2007

Access 2007 supports connecting to a wide variety of different types of data sources although some are read-only, many are fully updatable from an Access application. This is because different data source types use separate, but distinct methods to connect to the data. Indexed Sequential Access Method ISAM drivers are generally used for connecting to other desktop or file-based data sources, such as Excel, text, and HTML. On the other hand, Open Database Connectivity ODBC data source vendors...

Setting and Retrieving Summaryinfo Properties

When you select Database Properties from the Manage menu under the Office button, Access opens the Properties dialog box. It displays several built-in properties, some you can change, and some you can't. The General tab displays various information about the database, including its file location and size, creation date, and the dates it was last modified and accessed. The Summary tab enables you to enter your own properties, such as the document Title which is different from the Application...

Security for the Accdb File Format

One of the new features of Microsoft Office Access 2007 is a file format ACCDB. The security features available for it are somewhat different than those provided for the MDB file formats in a number of ways. This section discusses the security available for ACCDB files. The five different forms of database security for the ACCDB file format are as follows Shared-Level Security There is one simple form of shared-level security provided by the Access Connectivity Engine for ACCDB Encrypt Database...

Debugging VBA

No matter how skilled you are there are times when you need help figuring out what the code is actually doing. Fortunately, VBA provides a rich and powerful debugging environment. You can stop the code at various times and for various reasons, view values of variables and even change them , and step through your code line-by-line until you understand what's going on. The main reason you need to debug your code is because Access has displayed an error message....

Creating Tables and Columns

There are many times when you want to create tables from code. ADO fully supports creating tables and columns for those tables. Follow these steps to create a table in ADO 1. Create a Catalog object and define its ActiveConnection property. 3. Check if the table already exists, and if so, delete it. 4. Create the table object in memory using the New keyword. 5. Create the Column objects in memory, using the table's Append method, setting each column's attributes as appropriate. 6. Append the...

Responding to Events

Now that you know how to create custom events in your object, you might want to know how to listen and respond to them in your code. It's actually quite simple. All you need to do is declare the object variable using the WithEvents keyword. Unfortunately, however, you can only use WithEvents for object variables declared at module-level and only in class modules. Remember that the code behind forms and reports are class modules too, so you can also use the WithEvents keyword in forms. The...

Application Object Methods

The following table lists all of the methods available from the Application object. Any arguments for the methods are also listed. For example, you can use the CompactRepair method of the Application object with the following code Application.CompactRepair C DB Samples.mdb, Returns the error message associated with an Access or DAO error number. Adds the name of the current database as a hyperlink address to the Favorites folder. Returns a parsed criteria string as it would appear in the query...

ADO Object Model Reference

When using VBA to access data in ODBC compliant databases, you can choose from DAO or ADO. To use either technique, you'll need to understand the object model for the appropriate technology. The ADO object model isn't overly complicated, but understanding the details of all the various ADO objects will help ensure that you use the proper object for the designated task. This appendix lists all of the major objects needed when using ADO to access data within VBA. For each of the major objects,...

Adding Controls

There is a wide range of controls that you can use in your customizations. Because you've already examined tabs and groups, the XML that follows only describes the controls. To use these controls in the Ribbon you need to include tab and group nodes in addition to the customUl and ribbon nodes. The solutions that you build later in this chapter will use these controls, including two of the more interesting controls galleries and dynamic menus. A menu control is a container for other types of...

Open or Print Any File

The following procedure enables you to open or print any file, without your needing to know what its executable program is. For example, this same procedure can be used to open or print a Word or PDF document, an Excel spreadsheet, or an ASCII text file. It can even be used to generate e-mail with the default e-mail client if you use the mailto protocol followed by an e-mail address, or to open a Web page with the default Internet browser if you specify a Web address that includes the HTTP...

Reg DeleteTree

Description Deletes the subkeys and values from the specified Registry key. Requires Declaration Private Declare Function RegDeleteTree _ Lib advapi32.dll _ Alias RegDeleteTreeA _ ByVal hKey As Long, _ ByVal lpSubKey As String As Long hKey - Long Integer Handle of the open key, or one of the hive con- lpSubKey - String Name of the subkey to delete. Set to vbNullString to delete all subkeys under the specified key. Long Integer Zero ERROR_SUCCESS on success. All other values are the specific...

Creating a Self Certification Certificate

To create a certificate for yourself, simply run the SelfCert.exe program. This is available from Start C All Programs C Microsoft Office C Microsoft Office Tools C Digital Certificate for VBA Projects. You can also run this from the Office12 folder. For example, mine is located in C Program Files Microsoft Office OFFICE12 SELFCERT.EXE. If SelfCert.exe is not installed on your computer, use the Microsoft Office 2007 installation disk to install it. When Selfcert.exe starts, the Create Digital...

Parameters

Although you can't append parameters to a QueryDef using DAO, you can create them by declaring them in the SQL as shown in the following code Dim dbs As DAO.Database Dim qdf As DAO.QueryDef Dim prm As DAO.Parameter Dim strSQL As String Set qdf dbs.CreateQueryDef myQuery Application.RefreshDatabaseWindow strSQL PARAMETERS Param1 TEXT, Param2 INT strSQL strSQL amp SELECT FROM Table1 strSQL strSQL amp WHERE Field1 Param1 AND Field2 Debug.Print qdf.Parameters.Count For Each prm In qdf.Parameters...

Reg GetValue

Description Retrieves the data and type for the specified value in the Registry Requires Windows XP Professional x64 Edition or Windows Vista. Declaration ' String declaration for pvData Private Declare Function RegGetValueString _ Lib advapi32.dll _ Alias RegGetValueA _ ByVal hkey As Long, _ ByVal lpSubKey As String, _ ByVal lpValue As String, _ ByVal dwFlags As Long, _ pdwType As Long, _ ByVal pvData As String, _ pcbData As Long As Long ' Numeric declaration for pvData Private Declare...

Alternating Row Color

Alternating row color is another coding challenge turned property. And yes, it is now that easy to display alternating row colors. You can set the controls for the datasheet as well as for repeating sections. In a table, the control is conveniently located with the font options. Click the control, and select from the array of color swatches displayed in the standard color pallet or choose More Colors and select whatever you want from the gradient scale. Figure 3-6 shows the color pallet with...

Using the Column History Method

The ColumnHistory method is a member of the Application object. It enables you to retrieve the full data history for an Append Only field. The AppendOnly field property is available only for the Memo data type and can be set to True in the table properties. The ColumnHistory method takes three parameters TableName, ColumnName, and QueryString, each of which is required. TableName obviously takes the name of the table that contains the Append Only field and the Column-Name is the name of the...

Compiling the Database into an MDE file

The predecessor of the ACCDE compiled database solution was the MDE file format. The MDE and ACCDE file formats are essentially the same from a compiled code standpoint. The VBA code in the database is compiled to a binary format and the source code is removed from the VBA project in both cases. However, the ACCDE format supports the database features in the ACCDB file format and the MDE file format is used to support the legacy MDB file formats. For example, an ACCDE file supports Attachment...

Reg EnableReflection Key

Description Enables Registry reflection for the specified Registry key. Restoring Registry reflection does not restore reflection on any subkeys. Requires Windows XP Professional x64 Edition or Windows Vista. Declaration Declare Function RegEnableReflectionKey _ Lib advapi32.dll _ ByVal hBase As Long As Long Parameter hBase - Long Integer Handle of the open key, or one of the hive con Return value Long Integer Zero ERROR_SUCCESS on success. All other values are the specific error code....

User Level Security Using ADOX

The previous section showed you how to maintain user-level security programmatically using ADO and either the Access Connectivity Engine or the Jet Database Engine. Another method to maintain security through VBA code is to use Microsoft ADO Extensions ADOX for DDL and Security. ADOX uses an object model that supports the Catalog, Group, and User objects. Using those objects, you can perform most of the tasks previously described in ADO. Because of the object model, developing VBA procedures to...

Maintaining Users and User Passwords with ADO

The next three procedures demonstrate techniques for adding a user AddUserADO , dropping a user DropUserADO , and setting a user password AlterUserPasswordADO . When a user is added, the Personal ID must be specified. The AddUserADO procedure automatically adds the user to the group Users with a call to AddUserToGroup function. This keeps the MDW file consistent with the ACE Jet database engine rule requiring that all users belong to the Users group. As with groups, the ADO code to add users is...

Using Transactions

A transaction is defined as a delimited set of changes that are performed on a database's schema or data. They increase the speed of actions that change data, and enable you to undo changes that have not yet been committed. Transactions offer a great deal of data integrity insurance for situations where an entire series of actions must complete successfully, or not complete at all. This is the all-or-nothing principle that is employed in most financial transactions. For example, when your...

The Accdr File Format

The ACCDR file format is nothing more than a renamed ACCDB file format. If a .accdb database file extension is changed to .accdr, any time the database is opened, the Access Runtime will open the database solution. Although switching the extension for an MDB to ACCDR may work in most cases, it is not guaranteed to work in all cases. When using the ACCDR extension for MDB file format database, it is always best to test the solution to make sure that it is working correctly. Renaming a database...

Methods DAOPrivDBEngine

The unsupported PrivDBEngine object enables you to connect to an external database that uses a different Workgroup Information File to the one currently being used. You can open an Access database without having to create another instance of Access. PrivDBEngine only allows access to DAO objects, such as TableDefs, QueryDefs, Recordsets, Fields, Containers, Documents, Indexes, and Relations. Dim dbX As PrivDBEngine Dim wsX As Workspace Dim dbe As Database 'Return a reference to a new instance...

Using the Excel OM to Create a New Workbook

To create a new worksheet in Excel, you will build the code in a few steps. To start you create an Excel application object. Then, you create a new worksheet object, as shown in the following code 'Create the Excel Application object Set xlApp Set xlWorkbook xlApp.Workbooks.Add After creating the new worksheet, you'll want fill that worksheet with data. The next example uses a Recordset object from DAO, see Chapter 6 to gather the data from an Access query in the database. The Recordset object...

The Database Folder

The Database folder is where most of the database settings and properties are stored. It has five objects four XML files and the objects folder. These XML files describe the database properties, VBA project references, the table relationships, and the Navigation pane settings. The objects folder contains the rest of the information about the template, which will be discussed in depth a little later in this chapter. The following sections describe each of the XML files in detail. The...

Encoding an MDB File

To enhance data security for MDB databases, the Access Connectivity Engine and Jet support data encoding for MDB files. As previously stated, encoding the data in the database obscures the data such that it will be much more difficult to decode the data in the database and the raw file data will not show plain text data in the database. However, encoding does not come without a cost. Aside from the initial cost of time to encode the entire database, performance when reading and writing data may...

The Application Object

All of the Access objects you'll manipulate within code are children of the Application object, which is the parent object for all objects and collections in the Access object model. Every object and collection is related to the Application object through either a direct parent child relationship or multiple parent child relationships. Figure G-1 shows the Access objects and collections that are the direct children of the Application object. The Office 12.0 object library exposes objects from...

But First a Few Terms

You've seen some good cases for using managed code, but before we get into the details of using it with Access, take a look at the following terms, which are commonly used when referring to managed code Assembly A versioned piece of compiled managed code. Code that you write is usually compiled into a single-file assembly such as a DLL or EXE. Assemblies are also self-describing using a technique known as reflection. They are said to be self-describing because they contain metadata that...

Form Object Events

Events are at the core of programming Windows applications. As such, there are a large number of events you'll use within your code behind forms. You probably will only use a handful of these events, but they are all available to you. A Form's events are summarized in the following table. When the form receives focus and becomes the active window. After the user confirms the delete and the records are actually deleted. After all elements in the PivotChart have been rendered. After all charts in...

Using ADO Events

The ADO Connection and Recordset objects support several events for a variety of operations. These events won't interrupt your code, and can be more accurately pictured as notifications, which are actually a call to an event procedure that you define in your code, much like a text box's AfterUpdate event. ADO object events aren't always so important for synchronous operations because your code waits for the operation to complete before proceeding. They can be important for asynchronous...

Creating a Progress Meter Report

Progress meter reports are an interesting way to display status as a percentage of a given value. In the conference application, for example, you could ask the attendees to rate each session on a scale of one to ten, and enter the data in a table called tblSessionRatings. Aggregate the data using the following query SELECT tblSessionRatings.Session, tblSessions.Title, CDate CLng Start Time AS SessionDate, Avg tblSessionRatings.Rating AS AvgOfRating RIGHT JOIN tblSessionRatings ON GROUP BY...

Linked Tables to Share Point

Linked Tables to SharePoint is probably the most commonly used SharePoint feature in Access database solutions. It provides the database with virtual tables, which update data on the specified SharePoint site, directly from within the database solution. The SharePoint ISAM provides the interface for Access to connect directly to SharePoint. SharePoint linked tables can be used just like native Access tables, except that the Schema cannot be modified in Access or through VBA code, and the actual...

Windows Registry Data Types

In the same way that database table fields, variables, and API parameters require data of specific types, the kind of data the Registry can store is also defined in terms of data types. The data types in the table that follows are supported under Windows 2000, Windows XP, and Windows Vista. Specifies raw binary data. Most hardware information is stored with this data type, which can be displayed and entered in binary or hexadecimal format. A 32-bit 4-byte number, which is used to store Boolean...

Class Libraries

When you write a class library using managed code, Visual Studio creates a DLL for you that contains the classes you have defined. However, the DLL is actually an assembly managed by the CLR. Because the DLL contains managed code, referencing it in Access produces an error, as shown in Figure C-1. Instead, the .NET Framework allows you to create a type library that you can reference from VBA. These type libraries are compatible with COM applications such as Access. If you want to extend your...

The Current Project Object

The CurrentProject object represents the Access project for the database or project that is currently open in Access. The Access project consists of the non-data items such as Forms, Reports, Macros, and Modules. The CurrentProject object has several collections and a number of properties you can use within your application. Here are descriptions of those properties Returns a reference to the currently active ADO Connection object. Returns a reference to the AllForms collection and its...

Importing from Share Point into an ACCDB

Fortunately, Access supports most of the data types that SharePoint 3.0 supports, such as Complex Data and Attachment fields. However, there are some differences about how data is imported for certain types of fields, such as Lookup or Person or Group type fields. Additionally, there are a number of fields that are not shown in either the Default View or the Column Settings page that are imported along with the rest of the fields in the list. When importing lists from SharePoint, it is...

The Seek Method

The Seek method is the fastest way to find a specific record, but it can be used only on table-type recordsets because it specifically relies on the table's indexes. Naturally, the table must have at least one index for it to search on. Trying to call Seek against a non-table-type recordset will earn you a runtime error. Seek uses the following syntax rst.Seek comparison, keyl, key2. . .key13 To use Seek, you must specify three things the name of the index to use you can specify only one index...

Managed Code and Access Addins

Changes have been made to the Access 2007 Primary Interop Assembly PIA that enable you to listen for events in managed code and respond to them. You also have the option of enhancing your Access applications with add-ins and smart panes based on managed Microsoft .NET code. This could be as little as adding a custom button to the Ribbon to open a custom browse tool that makes it easier for users to store files on the correct network server. The PIAs for Access, DAO, and Office enable managed...

Access Object Variable Tags

The following table lists the common ADOX tags. This includes the plural forms although most developers stick with singular. The list is ordered by ADOX object. The following table lists the common ADO tags. Again, these include plural forms although most developers stick to singular forms. The list is in order by ADO object.

Multiple Form Instances

There are situations in which the user needs to compare two or more records from the same table. For example, you may need to compare Order 1 to Order 2 or Supplier1 to Supplier 2 or Supplier1 to Supplier2 and to Supplier3. This can be accomplished by opening the same form multiple times. Let's say a database consultant is creating an Access solution for a client. The consultant is in a bind and needs to subcontract parts of the project to one of her fellow developers. She checks her database...

Saving Email Addresses Using Textbox After Update Event

Access 2007 is smarter about the way it stores website and e-mail addresses. Data in a hyperlink field is automatically evaluated and tagged. The value is tagged with http for websites or mailto for e-mail addresses. In previous versions, the Hyperlink data type stored e-mail addresses as Web addresses http customeremail msn.com, for example , so when you clicked the link, it opened Internet Explorer and tried to find the website not at all helpful. One way to fix this is to right-click the...