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

Numeric Parameters

As with VBA, there are many types of numeric data you might come across when working with APIs. The following table outlines the numeric types defined in C C++ along with their VBA equivalents. Also included are the data type prefixes often found in Windows API declarations on MSDN. 8-bit or 16-bit signed (depending on whether you're using ANSI or Unicode) 8-bit or 16-bit signed (depending on whether you're using ANSI or Unicode) 32-bit unsigned integer (also referred to as a double-word) Let's...

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

How ACE and Jet Interact with ODBC Data Sources

Because ACE and Jet send information across the network to the data source, it's important to consider when and how much data is being transferred. The bandwidth of the network connections, load on the SQL Server, and amount of data being transferred across the network directly affect the performance of a client-server application. Before delving into the intricacies of how ACE and Jet deal with sending and retrieving data from SQL Server, consider employing the SQL Server Profiler to help...

Converting a Replicated Database

Replication is not supported in the ACCDB file format. Instead, a more powerful and versatile alternative is offered using SharePoint services. Because Access 2007 will work with replicated Access 2000 and 2002-2003 .mdb files, there's no need to convert the files to essentially maintain a status quo. In some cases, however, the benefits of converting to the ACCDB file format will outweigh the benefits derived from replication. The following outlines the process for essentially creating a new...

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

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

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

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

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

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

The DoCmd Object

In many regards, the DoCmd object is the workhorse of the Access object model. It carries out tasks such as exporting objects to different formats, opening objects, and manipulating the size of the Access window. It has no properties, but it does have a variety of methods you can utilize within your Access application. All of the methods carry out an action with the same name as the method, so the following table provides descriptions of those actions, rather than of the methods which merely...

Setting Encryption Options

When you encrypt a database, the encryption is performed by calling into a Cryptographic Service Provider CSP that is registered by Windows. The CSP uses a specified encryption algorithm and key length to encrypt the specified data. For additional information about database encryption, please refer to Chapter 18. By default, Access uses Microsoft Base Cryptographic Provider v1.0 for database encryption. The default encryption algorithm is RC4. These options can be changed in DAO by executing...

Creating Tables

Tables are the backbone of any database. Because they store all of the data, designing them correctly the first time is crucial. The type of data you need to store in any given table is dictated by its purpose in the application. For example, if you need to store the date on which some event occurred, you would use a Date Time field data type. You could use a Text field type to store a date and there may be cases where that makes sense, but most of the time, the Date Time type will be more...

COM Addins

The Office Developer Edition for Office 2000 and Office XP allowed you to create COM add-ins using the Visual Basic Editor. This functionality remains in Visual Studio 2005. The Express Editions of Visual Studio do not include the required project type to create COM add-ins. COM add-ins offer several additional features over a class library They enable you to run code when Access starts. They enable you to run code when Access shuts down. A COM add-in is given an instance of the Access...

More Ribbon Tips

Here are a few tips to keep for writing ribbon customizations. They can help you in creating your applications, as well as in providing additional polish to your solutions. Prevent the Ribbon from loading You want to prevent the Ribbon from loading when testing or developing. For instance, when you are developing a Ribbon from scratch but you are also developing forms and reports. With a custom ribbon, the form design tools are not available. You can hold down the Shift key as you would to...

Using the ADE Tools

The Access Developer Extensions ADE is a combination of tools designed to aid in the development of Access applications. If you've used the ADE tools in the past, you'll be glad to hear that there are some new features for Access 2007. This chapter examines the ADE tools, shows you how to use them, and discusses how using the ADE can improve Access applications. The ADE package contains several tools for application development and deployment, including the following The Access Redistributable...

The Replace OrderByClause and Replace WhereClause Functions

It's often necessary to cut and replace the Where and Order By clauses of a SQL string using VBA. Throughout this chapter, the ReplaceWhereClause and ReplaceOrderByClause functions are used to do that. This section shows you the code that's been doing all that hard work This first procedure ParseSQL does the heavy lifting of the SQL handling functions. It breaks up the original SQL string into components, so that individual pieces can be replaced. Although ParseSQL is Public, it's rarely called...

A

AbsolutePage property, 209-210 AbsolutePosition property, 168, 209-210 ACCDB. See Access Database file format ACCDE. See Access Compiled Database file format ACCDR. See Access Database Runtime file format ACCDT. See Access Database Template file ACE, 52-55 data types, 9-10 database objects, 8 deprecations in, 65-66, 788-789 DAPs, 65 Import RunCommand, 65 legacy export and import formats, 66 Snapshot Viewer, 66 external data sources, 59-60 Excel, 59 Outlook, 59-60 SQL Server, 35-36, 60 features...

New Attachment Controls

In addition to being a new field data type, the attachment is also one of the new controls that can be found in the Toolbox. The Attachment control enables you to attach a file or collection of files to any record. When you right-click the control, a toolbar provides forward and backward buttons to cycle through the files and a paperclip button that opens the Attachment dialog box. That sounds fairly straightforward, but as a developer, you will likely want to provide a more elegant solution by...

Reg Editexe

Prior to Windows NT, regedit.exe was a 16-bit application for editing the Registry on 16-bit Windows platforms. It was originally included in Windows NT and 2000 for backward compatibility, but because of its limited functionality in the 32-bit environment, Microsoft recommends that you use regedit.exe only for its search capabilities on Windows NT 4.0 and Windows 2000. There is no limited functionality in regedit.exe on Windows XP, Windows Server 2003, or Windows Vista. On x64-based versions...

Data Type Description

Attachment A field type to store a collection of files for a given record. Stored as a complex data field, the complex scalar fields expose three pieces of data File Name, File Data the file itself , and the File Type. Stored as a complex lookup into a hidden complex scalar table. Stored as 4-byte integer that is assigned automatically when the record is created. Can be assigned as consecutive or random values. If the AutoNumber is a Replication ID, it is stored as a 16-byte GUID, instead of an...

Working with Share Point

The fastest growing business software product in the Microsoft family, Windows SharePoint Services is one of the hottest technologies for digital team site management available today. Flexible and easy to use, SharePoint provides users with simple site creation and design, robust content management, and powerful security for business data. Microsoft Office 2007 contains a myriad of new features that integrate with SharePoint to help users communicate information, and one of the pillars of the...

Registry API Constant and User Defined Type Declarations

To help you with this book, and so that you won't have to go searching for them, the following is a list of all the Constant and User-Defined Type declarations you'll need when using the Registry APIs. Const HKEY_CLASSES_ROOT As Long amp H80000000 Const HKEY_CURRENT_CONFIG 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 HKEY_LOCAL_MACHINE Const...

Combo Boxes

The combo box is a potent control that can combine the processes to search, select, and populate data into fields or other objects , as well as limit the values that a user may enter. Many nuances affect how combo boxes work this section addresses some of the more common ones. It also covers some new features, including two new properties Allow Value List Edits and List Items Edit Form. The combo box and list box have a lot of the same properties and functionality. The primary difference is...

Importing from Share Point into an MDB

While the Access MDB file formats do support importing SharePoint 3.0 lists, there are a few more field types that will be converted when imported. For example, complex data and attachments are not supported in the MDB file format and therefore cannot be imported into an MDB as those data types. The following table compares the SharePoint 3.0 data types to the Access data types after conversion when importing into an MDB file. Access Data Type After Import into an MDB Memo Rich Text setting is...

Testing for an Empty Recordset

As mentioned in the previous section, if you attempt to move beyond a recordset's boundaries, an error occurs. Similarly, if you attempt to execute any other recordset method on an empty recordset one that has not returned any records , an error occurs. Whenever you open a recordset, you usually want to do something with the data it returns, so the first thing you need to know is whether it returned any records. If the data is there, you can confidently take whatever actions you had planned....

The Source Code Control Options

To work with the features supplied by the SCC provider and the SCC add-in for Access, go to the Source Control Ribbon tab. Because the database is already registered in an SCC project, all of the options on this tab should be enabled, except for the Add Database to SourceSafe for obvious reasons. When working with a database object, for actions such as checking out and checking in changes, click the object name in the Navigation pane and then click the desired action in the Ribbon. The...

Sending Access Objects to Word

In addition to using VBA to create a mail merge, you can export data in an Access database to Word using VBA and the Word Object Model. The following code, for example, allows a user to export an Access report to a specific Word document. This feature is extremely useful when users need to modify the report or need to perform other operations on the document, such as including external documentation or e-mailing the report to others in an editable format. Dim objWordApp As Word.Application Dim...

Executing Queries

Queries that insert, update, or delete queries are known as action queries. While these types of queries do not return records, it is common to run them using code. There are three ways to programmatically execute a query using the DoCmd.RunSQL method, the object.Execute method, and the OpenRecordset method. The query argument for any of the following methods can either be the name of a permanent or temporary QueryDef, or a string expression that equates to a query. Although not part of the DAO...

Generate a Unique Temp Filename

The GetTempFileName function generates a unique temporary filename with a .tmp extension Private Declare Function GetTempFileName _ Lib kernel32 Alias GetTempFileNameA _ ByVal lpTempFileName As String As Long Public Function GetTemporaryFile _ Optional strDirectory As String, _ Optional strPrefix As String As String Dim strPath As String Dim lngReturn As Long ' Default to the folder where the database resides If Len strDirectory 0 Then strDirectory CurrentProject.Path End If lngReturn...

Automate Word to Start Your Merge

If your users are fairly technically savvy with Microsoft Word, they may want to create their own mail merge document. This example uses the Word Object Model to initiate the mail merge using a preexisting Word document. Any project automating Word requires a reference to the Microsoft Word 12.0 Object Model for the VBA project. The sample database already has this reference set, and the code samples for this section can be found in the Form module for the Customer Information form in the...

Save as MDB

Once again, Microsoft has made it remarkably easy to share files with older versions of Access. With Access 2007, the File O Save As command allows files to be saved directly in either the Access 2000 or the Access 2002-2003 MDB file format. Of course, new features are either ignored or handled in their earlier method. For example, when a file is converted to an MDB format, the user has to leave the Ribbon and go back to menus and toolbars, and rich text formatting is ignored so users will see...

Creating Tables and Fields

For the invoicing system, you have two tables to create. The basic procedure for creating a table in code is as follows 1. Check if the table already exists, and if so, rename it. You could also choose to delete the table instead of renaming it. 2. Create the table object using the Database's CreateTableDef method. 3. Create the Field objects in memory, using the TableDef's CreateField method, setting each field's attributes as appropriate. 4. Append each Field object to the TableDef's Fields...

Creating Multi Value Lookup Fields

There are new data types in DAO that are used to define a multi-value lookup field. The names of these types begin with dbComplex and contain the name of a type that can be used for the lookup field. In other words, if the related field for the lookup is an Integer, you can use dbComplexInteger for a multi-value lookup field. The valid field types are Let's say that you have a database that tracks students and classes, with respective tables tblStudents and tblClasses. The tblClasses table...

Using VBA to Encode a Database

While encoding and decoding a database is fast and easy to do through the Access UI, there are times when you want to provide the user with the capability to encode and decode the database through, say, a button click in the application. If, for example, you are developing an application that's going to be used to manage other database files, you probably want to provide an option for encoding decoding known databases. Unfortunately, there is no direct EncodeDecodeDatabase method available that...

Report Object Properties

The properties of the Report object are listed in the following table. Used with the Screen object to determine the control that has the focus. Determines whether a report can be viewed in Layout view. Determines whether a report can be viewed in report view. Returns the currently active application object. Determines whether the report will be automatically centered within the Application window. Determines whether the report will be automatically resized to display complete records. Specifies...

Creating an MDW File

The first step in setting up user-level security is to select or create the MDW file that will contain the user groups and users to be defined. As mentioned earlier, this can be a new file or the default file provided when Access is first used. The recommended approach is to always create a new MDW file. To deter users from accidentally locking themselves out of Access, the User-Level Security Wizard will not permit you to use the default SYSTEM.MDW file. If you corrupt the default MDW file,...

The Accdt File Format

The structure of the Access 2007 ACCDT file format is different from any Access database file formats. The new Office Open XML file format schema defines the ACCDT structure. ACCDT files are collections of text, XML, and image files packaged into a single file that is consumable by Access. When a user invokes a template, Access retrieves the database objects and settings from the ACCDT file, creates a new database, and then builds the database objects that are specified in the ACCDT file. This...

Printer Object Properties

The properties of the Printer object are explained in the following table. Whether the printer should output in color or monochrome mode. Vertical space between detail sections in twips . True if Access prints only the data and not the labels, borders, gridlines, and graphics. True when the size of the detail section in design view is used for printing. False if the ItemSizeHeight and ItemSizeWidth properties are used. Name of the driver used by the specified printer. How the printer handles...

Building the Flight Group

The Flight group contains controls to book or search for flights as well as other flight-related data such as airport information, flight delays, or weather. For this project, each of these controls will be implemented as a button. While they do not include the onAction or tag attributes, you could add those to open a form defined in the application as demonstrated earlier. Here's the XML for the Flight group lt group id grpTravelFlight label Flight gt lt button id btnBookFlights label Book...

Pointers to Functions

FARPROC and DLGPROC are examples of pointers to functions. These pointers are supplied so the API can execute a function as part of its own functionality. Such functions are referred to as callback functions. You specify the memory address of a callback function using the VBA AddressOf operator, which has certain limitations It can only be specified in a standard module you can't use it in a class module. It must precede an argument in an argument list, and the argument it precedes must be the...

Application SetOption Method

The SetOption method of the Application object allows you to control all of the options in the Access Options dialog box which is available from the Office button. The following tables detail the string arguments for the options available on each group. For example, to control the visibility of the Status Bar within your Access application, you'd use the following code. Application.SetOption Show Status Bar, True The arguments you need to manipulate the SetOption method are broken down by the...

Security for the MDB File Format

To provide legacy support for the MDB file format, Access 2007 and the Access Connectivity Engine continue to maintain the security features available in previous versions of Access and the Jet Database Engine. With so many types of security features in Access, it is easy to become overwhelmed when deciding which method to choose. Following is a synopsis of MDB security features. There are generally two common scenarios used when designing a database application Standalone databases that...

Sending Data to Power Point

There are often times when Office users want to convey statistical data during a presentation via the use of graphs and charts. Most of the time the users probably want to build a presentation themselves, but someone who gives many lectures might want to update an existing presentation with the latest data from her Access database. It is worth noting that PowerPoint also provides a rich object model for building presentations on-the-fly. The following example illustrates how to update a...

On Click Open a Form Based on a Value on the Current Form

Opening a form based on a value stored on another form is a common practice. The functionality applies to a myriad of tasks, from listing all of the orders for one customer to gaining a bigger picture by comparing the total purchases of one customer to other customers in the region. It also works well for drilling into details, such as searching a contact list and then clicking a record to see the detail information about that person. For this example, you drill into data to get information...

Find the Position of a Form

The Form object in Access includes four properties that would appear to give an indication as to the location of a form onscreen WindowHeight, WindowLeft, WindowTop, and WindowWidth. These properties actually return values based on the client workspace within the Access application window. Because these values can change based on the location of the Access window itself, there is no built-in mechanism to expose the current xy position of a form. The following example demonstrates how to use the...

Pulling Data from Access

There are unlimited opportunities to use VBA in an application to manipulate an instance of Access to utilize data in a database solution. The Access Object Model can be managed from other applications that support VBA to enhance those applications with Access functionality. This example gathers data from an Access database into an Excel spreadsheet, which is included with the sample files for this chapter. As with all other Office applications, adding a reference to the Microsoft Access 12.0...

More on Absorbing Expected Errors

As stated earlier in this chapter, sometimes a normal activity in your application results in Access encountering an error. For example, if the code behind a report cancels the On Open event, Access displays an error message. Because this is a normal event, your user shouldn't see an error message. Your application should continue as though nothing happened. The code in the Open event of the report looks something like this Private Sub Report_Open Cancel As Integer On Error GoTo Error_Handler...

Concatenating Long SQL Strings

To keep your VBA readable, break your long statements onto multiple lines. While this is true any time, it's especially helpful when building long SQL strings. If you do not break them into multiple VBA lines, you have to scroll far to the right to read it all. There are two ways to break up those long statements by building up the string variable in multiple steps, or by using the VBA line continuation character. Many programmers still use the build-up method for storing long SQL strings into...

Open Args

What if you want to check for conditions or set certain properties when a form is opening You can use the OpenArgs property of the form to pass parameters to the form when you open it. The OpenForm method's seventh last parameter supplies the value for OpenArgs, as shown in the following statement DoCmd.OpenForm FormName, , , , , , OpenArgs value In this case, the value of the OpenArgs property would be OpenArgs value. Because the Open event triggers whenever a form opens, you can include the...

Shortcut Menu Bars

The shortcut menu bars that you created for forms in previous versions of Access will continue to work in Access 2007. They will appear on the context menu, not in the Ribbon. However, the tools for creating toolbars and menu bars have been removed. Unfortunately this means there is no way to create a new shortcut menu bar unless you write code or use a previous version of Access. The following code which requires a reference to the Microsoft Office 12.0 Type Library creates a shortcut menu...

Table Def Object

The TableDef object is the stored definition of a table, linked or otherwise Microsoft Access workspaces only . Creates a new Field object Microsoft Access workspaces only . Creates a new Index object Microsoft Access workspaces only . Creates a new user-defined Property object Microsoft Access workspaces only . Refreshes the connection for a linked table Microsoft Access workspaces only . Sets returns a value that defines the TableDef's characteristics. Returns the name of the conflict table...