Brief History of Access

Microsoft Access has been around for nearly 15 years. The first version of Access, Microsoft Access 1.0, was released in November of 1992. Built on top of the Jet Database Engine, Access was designed to enable users to create and manipulate Jet-compatible database applications through a variety of visual designers and a scripting language called Access Basic. Access quickly became one of the most popular database development systems for Windows and the user base started growing rapidly. With...

Brief Word on Naming the Class

All things have names, and class modules are no different. The name you give a class module, however, is the name that is shown in both the Project Explorer and the Object Browser, so it should be something relevant and meaningful. A more in-depth discussion on naming objects comes later in this chapter. Open the Object Browser by selecting View O Object Browser, or by pressing F2. To name your class, display the Properties window by selecting it from the View menu, or by pressing F4. Then...

Access Database Objects

With the Navigation pane grouped on Object Type, you can see that there are four different types of database objects included in the Issues database Tables, Queries, Forms, and Reports. The Access 2007 File Format (ACCDB) actually supports eight types of database objects Tables, Queries, Forms, Reports, Macros, Modules, Class Modules, and Data Access Pages (DAPs). All of these objects except DAPs can be created through code or through the Access user interface (DAPs are deprecated in Access...

Access Wizards Builders and Managers

Access provides many tools that do a lot of work for developers. These tools not only save you time and prevent or minimize errors, but they are also a great resource for teaching yourself how to do things. But many developers may not even know that some of the wizards exist, and if you don't know about them, you aren't likely to be using them to your advantage. This appendix briefly describes the wizards, builders, and managers in Access 2007. Because you might be looking for a familiar...

Action Enum Members

The Action option specifies how the permission should be applied to the specified object. Grant the specified permissions. The permissions specified are added to those that have already been granted. Set the specified permissions. Only the specified permissions will be in force.

Adding a Certificate to Your Database

To digitally sign your database, you add a certificate to it using the Visual Basic Editor. In the Visual Basic Editor, select Tools O Digital Signature. The dialog box shown in Figure 22-15 opens. To pick a digital signature to sign your database, click Choose. The Select Certificate dialog box (see Figure 22-16) opens, showing all the code signing certificates on this computer. Select the certificate you want to use to sign this database and click OK. The name of the selected certificate...

ADO Object Tags

The following table lists the common DAO object tags. Again, these include the plural forms although most developers stick to singular forms. The list is in order by DAO object. The following table lists the common VB Object tags. Again, these include the plural forms although most developers stick to singular forms. The list is in order by VB object. The following table lists the common Data Type tags The following table lists the common scope prefixes. A scope prefix typically precedes the...

Advertising Access Views in the Share Point View Menu

Creating an Access view from the SharePoint View menu is extremely easy, although it is somewhat cumbersome if the user wants to create multiple views for the list. In most cases, it is far more likely that the database creator wants to design the views for the application long before the SharePoint list has even been created. Fortunately, Access provides developers with the capability to create these views programmatically. When a database application is migrated to a SharePoint site using the...

All About Me

Me is a very special word in Access VBA. It is a reference to the form or report that your code is running in. For example, if you have some code behind the form frmBusiness, anytime you use Me in that code, you get a reference to the form object of frmBusiness. This is a beautiful thing because there are many times that you need a reference to your own form or report, such as when you need to make it visible. You could refer to it directly, like this Or, you can use the Me reference instead...

Building the Cruise Group

The Cruise group contains three controls. A button to book a cruise, a gallery that displays the cruise destinations, and a dropDown that lists the months of the year (although you could use it to display a list of cruises that are sailing in a particular month). You'll fill both the gallery and dropDown controls using callbacks. You might wonder why you'd use code to fill the list of months instead of a static list of months in the XML. The reason is so that you can fill the list using the...

Collections of the Recordset Object

The Recordset object contains two collections Fields and Properties. The Fields collection is useful for retrieving information about the fields contained in the Recordset object. Contains all the Field objects for the current data contained in the Recordset object. Contains all the Property information for the Recordset object.

Constants

In general, a constant is just what it says, something that doesn't change. It can be a string or numeric value. Constants can be grouped as literal, symbolic, and built-in. Literal constants are numbers, strings, and dates that are hard-coded in the procedure. They may be used for clarity, by adding a definition (name) to a number that will be used. The following line of code shows how a literal constant makes it easy to recognize that October 23, 2007 is the start date for something. Public...

Convert with Confidence

You can convert to the new ACCDB file format as easily as selecting File O Save As. There's 100 percent compatibility with Access 2000 and 2002 file formats and you can even have an MDB and ACCDB version of the same app open at the same time. One hundred percent compatible Okay, there is a qualifier. Just because the file formats are compatible doesn't mean that the code converts smoothly or is totally supported, to say nothing about little bugs that have thus far escaped detection. Appendix A...

Creating a Recordset

You can create a recordset by using the OpenRecordset method of the Database, TableDef, or QueryDef objects Set rst dbs.OpenRecordset( Source, Type, Options, LockEdits ) Set rst object.OpenRecordset( Type, Options, LockEdits ) The Source argument specifies the name of a table or query, or a string expression that equates to an SQL query. For recordsets opened using the dbOpenTable type argument, the Source argument can only be the name of a table. The default recordset type that is opened if...

Creating and Deleting Users

Using DAO, you can create a new user account that can then be added to one or more groups. The following code shows how to create a new user. The basic procedure is to create the user with the CreateUser method, and then append it to the Users collection Public Function CreateUserAccount(strUserName As String, _ strPID As String, _ strPassword As String) Dim wrk As DAO.Workspace Dim usr As DAO.User Set usr wrk.CreateUser(strUserName, strPID, strPassword) wrk.Users.Append usr Set usr Nothing Set...

Creating and Using a Data Link

Instead of using CurrentProject.Connection to set your connection string, or writing your own, you can use a Data Link. You can specify the Data Link filename as a parameter of the Connection object, but you have to create a UDL file first. To create a custom data link, do the following 1. Open Windows Explorer and select File O New O Text Document. 2. Rename the file to something meaningful, and change its file extension to .UDL. 3. Click Yes in the message box that Windows subsequently...

Creating Forms

Forms are vital for allowing users to add, modify, and delete data within your database applications. Access 2007 provides an extensive forms package and several designers to afford you robust functionality for your forms. While it is quite easy to create forms via Access automation, often times, it is much more practical to build forms using the Access Form designers. The Access Ribbon's Create tab has several options for forms (see Figure 1-9) to enable you to reduce development time by...

Creating Reports

Reports are probably the most common way that users will view their data, and that's why one of the more robust features in Access is reporting. If you've used previous versions of Access, you'll notice that reporting has had a complete overhaul. Access 2007 has a slew of new reporting features that simplify common tasks to help decrease database development cost. There are two new view modes an interactive report mode to allow users to use controls on the report and a layout mode to enable the...

Creating Security Objects

When you create a new user or group account, either through the user interface or via code, you must supply a Personal IDentifier (PID). The PID is a case-sensitive 4-20 character string that Access combines with the user or group name to create a unique Security IDentifier (SID). The SID is a unique identifier, which is similar to a public security key. Once you create the account, you can never view or change the SID. But (and this is why the SID is notable) if you ever delete the user or...

Creating Setting and Retrieving Properties

Without even thinking about it, you've been setting and retrieving properties for as long as you've been programming. Whenever you check the value of a TextBox, or set the Enabled state of a command button, you are working with object properties. This section explores how to manipulate Access properties, object properties, and user-defined properties. You can refer to built-in properties either directly through the object to which they belong or through the object's Properties collection....

Creating Your Own Parameters

It's quite simple to create your own parameters on-the-fly in code using ADO. To create the parameter for a given query, call the CreateParameter method from the Command object. The benefit in this case is that you can specify a SQL statement in code and create the parameters for that statement when the code is run. Here's an example of creating parameters using the Command object Public Function GetPriceByCustomParameter(strName As String) As .ActiveConnection CurrentProject.Connection...

Customizing the Quick Access Toolbar

The Quick Access Toolbar, or QAT, is the small group of controls that appears next to the Office button. You can also use this space to add buttons to your application. The QAT provides fast access to those controls that are used most frequently. Add controls to the QAT for your application by adding the qat node as a child of the Ribbon node. The following XML adds a button to the QAT. The documentControls node adds controls only for the current database. < button id btnHello onAction...

Data Access Objects

DAO is the programmatic interface between VBA and Access database engine databases, ODBC (Open Database Connectivity) data stores, and installable ISAM (Indexed Sequential Access Method) data sources, such as Excel, Paradox, dBase, and Lotus 1-2-3.DAO was first released as a part of Visual Basic 2.0 and later released with Access 1.0 in November 1992. Over the years, many changes have been made to both DAO and to the Microsoft Jet database engine to reflect technologies at the time. Support for...

Database Object

The Database object is an open database. Using installable indexed sequential access methods (ISAM) such as Text or Excel, it is possible to open a Database object that points to data sources other than Microsoft Access. Many properties or methods listed here apply only to a Database object that has been opened against a Microsoft Access database. These members are noted as such. Creates a new user-defined Property object (Microsoft Access workspaces only). Creates a new Relation object...

Defining the Tabs and Groups

Start the Ribbon by defining the tabs and groups that you want to build. That provides some organization to the Ribbon and helps you fill in the controls in the coming sections. The application will have the following tabs Home Contains the most common functionality in the application such as travel information. Destinations Contains galleries of the different destination packages offered by the agency. Customers The travel agency wouldn't be what it is without customers This tab provides entry...

Determining If a User Belongs to a Specific Group

When determining if a user should have access to a particular object or function, you may need to determine whether the user belongs to a specific group. As with most other functions, this is also fairly easy simply check if the name of the user exists in the group's Users collection, or if the group exists in the user's Groups collection. Public Function IsUserInGroup (strUser As String, strGroup As String) As Boolean Dim wrk As DAO.Workspace Set wrk DBEngine(0) 'Check in the Users -->...

Determining Whether to Preselect a Checkbox

Let's say that you want to determine whether a checkbox is pressed when the Ribbon is loaded. To do this, you can handle the getPressed callback. The signature for that callback is the same as the onAction callback for a checkbox Sub OnGetPressed(ctl As IRibbonControl, ByRef pressed As Boolean) ByRef is the default modifier for a parameter in Visual Basic and VBA, meaning that even if you don't specify ByRef, this modifier is implicitly defined. In effect these two signatures are identical. To...

Disabled Mode

If you have opened an existing database in Access 2007, you have probably already seen Disabled mode. It is a new security feature in Access 2007 that prevents certain content in the database from executing. It's designed to let users securely inspect the content of a database without the possibility of running potentially malicious objects. What is a potentially malicious object Generally speaking, it is any object that can be used to alter database content, the file system, Registry, or...

Disabling Buttons if No Records Are Displayed

When you give your users the capability to narrow a list of records, they might figure out a way to omit all of them The subroutine EnableDisableControls is called just in case no records meet the criteria. Otherwise, users would get an error if they clicked the Detail button because there wouldn't be a key with which to open the detail record. To prevent that, the Detail button is disabled, as shown in Figure 15-5. The code to disable or enable the appropriate buttons looks like this Public...

E

E-mail addresses, AfterUpdate event, Access and SharePoint, 57-58 error handling, 289-291 programmatic creation of, 538 edit boxes, for Ribbon customization, 388 Edit in Datasheet, SharePoint, 564 Edit method, Recordset, 179-180 embedded macros debugging, 51 disabled mode, 51 error handling, 51 new features, 28, 50-51 temporary variables, 51 encapsulation, OOP, 476-477 encode database, MDB file format, 612-614 Access user interface, 612-613, 628 decoding, 614 VBA, 613-614 encryption. See data...

Encoding a User Level Secured Database

Securing a database with user-level security does not prevent anyone from exploring the database using a tool other than Access. As mentioned previously, much of the physical data contained in the MDB file is in plain text and readable to many different tools. For instance, Windows Notepad could be used to look at the data contained in the database simply by opening an un-encoded database file. If there is a need to secure a database with user-level security, it is likely that the data in the...

Encryption with Database Password

After years of humbly conceding that the security features in Access were more about permissions than protection, developers can finally claim that Access can preserve and protect the data. With the ACCDB file format, Access 2007 uses the Windows System Cryptographic APIs to encrypt data. And like other security measures, 2007 encryption uses a stronger algorithm to scramble the data than prior versions. If you use data encryption, be careful to store the password in a safe location because it...

Execute OptionEnum Members

The ExecuteOption enumeration specifies how the provider is to execute the Connection.CommandText argument. Hidden. The command is not specified. The command executes asynchronously. This option cannot be combined with adCmdTableDirect. The rows that remain to be retrieved after those specified by the CacheSize property are to be retrieved asynchronously. The main thread never blocks while retrieving data, so if the requested row has not been retrieved, the current row automatically moves to...

Find the Computer Name

The GetComputerName function returns the name of the computer. It does not return the fully qualified domain name (FQDN) for computers that are joined to a domain. Private Declare Function GetComputerName _ Lib kernel32.dll Alias GetComputerNameA _ (ByVal lpBuffer As String, _ nSize As Long) As Long Private Const MAX_COMPUTERNAME_LENGTH 31 Function GetMachineName() As String Dim strComputerName As String Dim lngSize As Long strComputerName Space(MAX_COMPUTERNAME_LENGTH) & Chr(0) lngSize...

Form Object Methods

The Form object also has a number of methods you can use within your code. They're described in the following table. Moves the focus to the first control on a specified page in the current form. Moves the form to the specified coordinates. Immediately updates the calculated controls on a form. Immediately updates the records in the underlying record source for a form. Completes any pending screen updates for the current form. Updates the data in the form from the underlying recordset. Resets...

Handling Unexpected Errors

Unexpected errors are ones that you have no way of predicting, and that under normal circumstances should not occur. When your application encounters an unexpected error (for example, divide by zero or a missing object), and no error handling is in effect, Access displays an error message like the one shown earlier and abruptly ends the procedure. The goal of error handling in this case is not to solve the problem the error is indicating there's nothing you can do about that now. Your code has...

Hkeycurrentuser

The HKEY_CURRENT_USER branch is built during logon and is an alias for the current user's subkey in the HKEY_USERS branch it contains user-specific information. Twelve major subkeys are in this branch, but depending on your system setup and what's installed, you might find some extra ones. expsrv.dll extmgr.dH H extrac32.exe Jf3ahvoas,dt fiT fastop-en.exe Si Faultrep.dll fdeploy.dll SjfdPHost.dll 1 fdProxy.dll S) FDResPubrdlf fdSSDP.dll fdWCN.dll fdWNet.dll fdWSD.dll fectient.dN Run as...

How Reports Are Structured

Several different components make up a report. Perhaps the most important are the various sections and group levels that are defined in a report. Sections are used to display data and other controls such as labels or combo boxes. The following table describes the types of sections in order. Typically displays report titles or similar information. Prints once at the top of the report. Typically displays unique data for a group or aggregate data such as a Sum or Average. Prints at the top of each...

If It Aint Broke

Everyone seems happy with what they have, so why rock the boat Users weren't asking for more because it wasn't available. But, give them the opportunity to customize their reports and drill into the underlying data, and suddenly you have empowered them to become true knowledge workers. If they are already using the new Office Ribbon in Word and other programs, then they will appreciate the consistency of having it available in Access as well. Of course, there are those limited situations in...

If Then

Nearly, every programming language has some way of asking If, and VBA is no exception. The lf Then structure is one of the most commonly used in VBA. Its usage is straightforward, but there are a couple of issues that warrant extra attention. First, the expression you are using needs to be formed correctly and completely. One common mistake is to use an expression like this The problem here is that a complete Boolean (true or false) expression needs to be on both sides of the Or. The literal...

Implementing a Daily Reminders Screen

A typical example might be the humble Daily Reminders screen implemented in many applications. This screen usually pops up when the application starts up, to remind users of overdue accounts, tasks they need to perform that day, or any number of things of which they need to be made aware. Having such a facility can be of great benefit to both the organization and the user, but some users prefer to display this screen when they want to. Having the screen pop up every time they start the...

Inherit TypeEnum Members

The Inherit option specifies how an object inherits permissions. The objects and containers contained by the primary object inherit the permissions. Other objects contained by the primary object also inherit the permissions. Default. Do not inherit permissions. The adInheritObjects and adInheritContainers flags are not inherited. Objects outside the container inherit the permissions.

Installation Options

The installation options enable you to create the settings for how the database solution will be deployed by the MSI file on the target system. The following table describes the options available in this section of the wizard Name and path to the database solution that will be packaged into the final MSI file. You can use the Browse button to specify the location. Root folder where the application file will be installed. This combo contains common options for the installation location on the...

Loadlmage Callback

Using images that are included with Office is an easy way to use images in your applications. But if you are developing an application, there are likely many more times where you want to use your own images. To do so, the Ribbon provides a callback called loadlmage, which is called whenever an image is requested via the image attribute. To provide a single function for this task, the attribute is defined on the custom UI node as demonstrated in the following XML < customUI < ribbon < tab...

Locking the VBA Project

As with an ACCDB file, locking the VBA project is a good way to protect the code within the Access database solution, while allowing the developer to retain the code in the actual distributed database. Locking the VBA project will deter unauthorized users from making modifications to the code, without the explicit permission of the developer. This operation is the same in either the ACCDB or the MDB file formats. For more information about locking the VBA project in Access 2007, please consult...

Methods of the Connection Object

The Connection object also provides a number of methods to use when working with a connection object. Those methods are described in the following table. Cancels the execution of a pending Open or Execute method. Closes the connection to the data source. Saves any changes and ends the current transaction. Executes a command on the connection. This method can pass a query string to the execute method to use without a Command object or can use the Command object to persist the command text and...

Methods of the Fields Collection

Methods for the Fields collection are described in the following table. Creates and adds a field object to the Fields collection. Finalizes any additions or deletions to the Fields collection. Cancels any pending changes for a record. Deletes a Field from the collection.

New Features in DAO

As mentioned earlier, Microsoft has introduced several new features in DAO for Access 2007. These features are multi-value lookup fields, attachment fields, append-only memo fields, and database encryption. Each of these features is only available in the ACCDB file format so as not to break backward compatibility with the MDB file format. All of these features, with the exception of database encryption, have been available on Windows SharePoint Services, and were added to Access for feature...

New in Access

Reports are among the most improved areas in Access 2007. Several new features have been added to make reports both easier to create and more powerful. For starters, Layout view allows you to design reports while you are viewing your actual data. Many of the tasks of creating a report, such as formatting, sorting and grouping, and adding totals handled in Layout view while your data is onscreen. As a result, you can accomplish many design tasks without having to switch between Design view and...

NoData Event

If the report is not cancelled in the Open event, Access runs a query specified in the report's RecordSource property to fetch the data needed by the report. If that query returns no records, either because data has not been entered or because the WHERE clause in the query resulted in no rows being returned, Access fires the NoData event. If the query does return data, the NoData event is not fired. You can create an event handler that tells Access what to do if there is no data for your...

Opening a Template Using the Access UI

Probably the easiest way to create a database from a Access template is to have the user open the template file in Getting Started and then click the Create button on the Preview pane. The user can open a template file in the Getting Started Preview pane in three ways. Double-click the ACCDT file Browse to the ACCDT from the Open File menu. Select a template from Getting Started's Template Categories. Users can open a template by navigating to the template file using Windows Explorer, and then...

Options Argument

Options arguments can be one or more of the following enumeration members. Hidden. No command type is specified. The Source argument is a command or the name of a stored procedure. The Source argument is the name of a table. The Source argument is the name of a stored procedure. Default. The type of command in the Source argument is unknown. The Source argument is the name of a stored Recordset. The Source argument is the name of a table. This option cannot be combined with adAsyncExecute....

Other AccessVBA Sources

You've heard the saying that there are as many ways to build a solution as there are programmers. Well, there is a lot of history underlying that statement. So, although this book is an excellent reference for all of your Access 2007 programming needs, there just isn't enough time and ink to cover everything to say nothing about fixes, updates, and add-ons. That's where networking, newsgroups, and other information sites come in. Here are some of the authors' favorites for you to check out...

Overview

At its highest level description, a SharePoint site is nothing more than a Web site designed to help users collaborate and communication information. In reality, a SharePoint site is much more than that. A SharePoint site is more like the framework for a living digital library that allows users to create many different Web interfaces to share information such as new sites, pages, lists, data views, charts, document libraries, blogs, applications, and much, much more. Users can store and retain...

Persisting the Data to XML

Creating a persisted Recordset can be done very easily. To create an ADO Recordset (using ADO 2.6 or later) and call the Save method to persist the data to an XML file. The Save method for the ADO Recordset object enables you to save the current structure and data in the recordset to two different file formats, defined by the PersistFormatEnum. The following code is an example of saving a recordset for the Invoices table as XML Dim rsRecordSet As New ADODB.Recordset ' Create the recordset. A...

Properties of the Error Object

Each Error object in an Errors collection provides information about a specific error that has occurred through its properties. The following table describes each of the properties of the Error object. The default property value. Contains the text of the error. Context ID of a topic in the Help file. Returns a long value used to retrieve the database-specific error information for an Error object. Long integer value of the error constant. Identifies the object that raised the error. Returns a...

Properties of the Property Object

Every Property object has four properties of its own, which are detailed in the following table. Long value that indicates characteristics of the property that are provider specific. String that identifies the property. Integer that specifies the property data type. The default property value. A variant that contains the property setting. No methods are associated with the Property object.

Properties of the Record Object

The following table describes the properties associated with a Record object. The Connection object used to retrieve the data for the Record object. Specifies the permissions for modifying the Record object. The Parent URL for the Record object. Specifies the type of the Record object. Specifies the source of the data contained in the Record object. Indicates the state of the Record object.

Report with Access

The Report with Access link in the Web Datasheet Task pane provides the user with an entry point for quickly creating Access views. As with the preceding two links in the task pane, the user has the option to create a new or choose an existing database. Once the database file has been selected, a new linked table is created in the database for the SharePoint list. In addition, a new report is created and opened in Layout view mode for the user to work with. Clicking the Report with Access link...

Restrictions Argument Members

The optional Restrictions parameter allows the output to be filtered by specifying a member of the SchemaEnum enumeration. For example, a Recordset object can be filtered to return only a single table or view. The available members and their constraints as described in the following table. SchemaEnum Member Restrictions (continued) SchemaEnum Member Restrictions (continued) TABLE_CATALOG TABLE_SCHEMA INDEX_NAME TYPE DIMENSION_UNIQUE_NAME HIERARCHY_UNIQUE_NAME LEVEL_NAME LEVEL_UNIQUE_NAME Tree...

Rich Text Fields

Access 2007 supports rich text fields better and with more flexibility than was previously available with add-ins. In addition to displaying multiple formats in one text box, the field also supports page breaks, line feeds, and carriage returns Chr(12), Chr(10), and Chr(13) in both forms and reports. Figure 3-3 illustrates the versatility of the rich text field. S-aLLrppiVr Mrip-Jtt sUit l> p oris Cruise o Irony i> Mch s

Rules for Creating Names Adding the Personal Touch

Developers tend to have an independent streak, which often means that we like to do things our own way. Thankfully, development is a creative process so typically there are multiple ways to achieve the desired results. That's also the case with naming conventions. Even if you choose to adopt existing standards, there are plenty of opportunities to incorporate your own preferences and come up with a system that is easy for you to remember, implement, and share. But before you start customizing...

Section Object Events

There are nine events you can use in your code for the Section object. They're described in the following table. The user presses and releases the mouse button. The user presses and releases the mouse button twice in rapid succession. A section is formatted. Does not fire in report view. A section is redrawn on the screen. Does not fire in Print Preview. A section is printed. Does not fire in report view. Retreat Access must move back to a section that has already been formatted. Allows you to...

Selecting the Access Default File Format

For Access 2007, the default file format is ACCDB. But, if most of the files will be used by prior versions of Access, it might be best to specify a 2000 or 2002-2003 MDB as the default file format. It is easy to set the default file format. And, if you need to specify a different file type, it takes only a couple of extra clicks to override the default selection. Setting the default file format is accomplished in a few easy steps. 1. Open Access 2007, but don't open a file. 2. On the Ribbon,...

Setting Unique Object Keys

The Collection object requires that each Key value is unique in the collection. Setting unique Collection object keys is not always easy. You can't easily use incrementing numbers because the Key parameter requires a String data type, and once you set it, it can't be changed without removing the object and reinserting it. The best method is to use a property of the object being added (if it has one), but isn't hard to implement. This is the approach you used in your Add method, where you used...

Shared Data Files

A 2007 .accdb application can open and work with multiple data files and file formats, including those with ACCDB and MDB file formats. But that is fairly standard for backwards compatibility. When linking to tables, it is important to remember that the data file must have the file format of the oldest version of Access that will open it. For .mdb files that could be 2000, 2002-2003 or 2003 file formats 95 and 97 are special cases. Access 2007 allows users to open previous files and save them...

Shortcut Options

The shortcut options specify which shortcuts should be created automatically when the database solution is installed, as well as any functionality for the shortcuts, such as custom icons, startup macros, and command options. The following table describes each of these options. Provides the option of creating Windows shortcuts to the database solution on the Start menu, the Desktop, or both. Name that will be used for the shortcut that is created. Icon that will be used for the application as...

Signed Database Package

Access 2007 has a new way to package and digitally sign a database Package-and-Sign. It creates a signed Access Deployment file (ACCDC) for ACCDB and ACCDE files. The process starts by compressing the database into a package file (.accdc), which can help reduce transfer times. Then the package file is signed using a code-signing certificate, which indicates that the code has not been tampered with. As with digital signatures, if you trust the publisher, you can extract the contents of the...

Specifying Command Parameters

Instead of specifying the Command object's parameters in the SQL statement, you can also set them using the Command object's Parameter object. For example, the following function retrieves the price of a specified Item by calling a Select query in the current database. Public Function GetPrice(strName As String) As Double .ActiveConnection CurrentProject.Connection ' Set other properties .CommandText qryGetPrice .CommandType adCmdTable ' To be able to refer to parameters by name, ' you must...

Steps for Converting or Enabling

For the most part, this section will focus on migrating to Access 2007. However, if an Access 2007 application will be used by prior versions of Access, it will also be important to know how to create a compatible file. Access 2007 makes this relatively easy and straightforward. Note the qualification, relatively, which based on the inclination to include a caveat about references and VBA. These steps do not check or fix broken references, they don't test and debug code, they don't replace...

Summary

In most business environments, it is good practice even essential to institute some level of security for an Access database application. There are several factors that help determine what is appropriate for the situation, but typically the result is a combination of methods that will provide the most cost-effective solution for the business needs. Simply implementing Access security alone is not always sufficient. Environmental factors play a large part in the security puzzle. Adding...

T

Tab, for Ribbon customization, 381-383 from scratch, 408-410 tabbed windows, as new feature, 39 TableDef object, 862-864 TableDefs collection creating indexes, 136-138 creating MVF, 141-143 creating tables, 133-136 DAO, 862 table(s) creation of ADO, 226-228 DAO, 133-136 without VBA, 8-11 new features of alternating row color, 44 filtering and sorting, 45 Recordset based on, 162 templates for, 40-41 tabular layout, forms, 296-297 TempVar object, 28-29 embedded macros, 51 Terminate event, class...

Testing the State Property

If you choose not to rely on events, you can always test the object's State property. It returns a value that indicates the status of the operation currently being carried out. For example, the following code segment tests the current state of a Connection object's Open operation. cn.Open CurrentProject.Connection, , Not what you'd call a professional approach, but every programmer is different, and you might like to do such things. Of course, the previous code could get into an endless loop if...

The Appendixes

As a developer, you can often spend hours going from source to source looking for reference material. The authors have applied the principles of relational databases (doing the work once so it can be used many times in multiple ways) to the appendixes, providing a compilation of data from a myriad of sources. Appendix A addresses the issues and processes of upgrading, converting, and compatibility. The other 12 appendixes provide lists and tables that complement specific chapters in the book....

The Default Access Database

Unless you're working with an Access Data Project, when you create a database in Access, it is automatically added to the Databases collection. Among its properties and methods, the Database object contains five collections TableDefs, Containers, QueryDefs, Recordsets, and Relations. Each of these collections and their respective objects and properties are discussed in later sections. In most cases, you will be working with the default Microsoft Access database, which you can refer to using any...

The Object Browser

After you have added a reference to a library, the classes contained in that library are available for viewing in the Object Browser. To see the Object Browser in the Visual Basic Editor, select View O Object Browser or press F2. The Visual Basic Editor displays the Object Browser as shown in Figure B-2. jgf Object Browser In 11131 IS jgf Object Browser In 11131 IS S3 Recordset Recordset2 Si Recordsets Members ofVbMsgBoxStyle 3 vbAbortRetrylgnore Zi vbAp plication Modal a vbCritical j...

The Power User

Out-of-the-box features and some of the new field types in the Access data engine (ACE) offer further opportunities for power users. The new complex data type acts like multi-value select boxes and actually creates tables to manage the underlying many-to-many relationship. Users can add preformatted fields to tables as easily as dragging them from a table or field template. Tracking reference material is easier because the attachment field type automatically compresses the file and stores it in...

The References Collection and Reference Object

In addition to the various Access objects detailed in this appendix, you can use objects from other applications such as Excel, Word, Outlook, and non-Microsoft programs such as AutoCad and Peachtree Accounting to program in Access. To use these other object models, set a reference to their type libraries. The References collection contains a reference for every external type library you add to the References dialog box within your code. The properties of the Reference object are described in...

The Seek Method

The ADO Seek method, although a little different from its DAO cousin, is still the fastest way to find a specific record, but it can only be used with server-side cursors on tables that have been opened as adCmdTableDirect because it specifically relies on the table's indexes (and the indexes reside on the server not on the client). Naturally, the table must have at least one index for it to search on. To use the ADO Seek method, you have to specify three things the name of the index key to...

The Templates Root Folder

The files contained directly in the root of the template's folder are used to describe the metadata about the template file itself. These are the file-level properties for the template and contain information about the template itself. All of the files listed in the root level of the structure are required to be in the template. The following table describes each of these in more detail. Content_Types .xml Describes the types of data files that are stored within this tem plate file. More...

To Convert or To Enable

You have several things to consider when deciding whether to convert an application to the new Access 2007 ACCDB format. The primary reason to convert is to take advantage of the powerful new features that require the ACCDB file format, such as the ability to work with complex data, the ease of collecting data from e-mail forms, and better integration with the web and SharePoint Services. To store complex data, Access 2007 includes a new system table, called MSsysComplexColumns, and a series of...

Upgrading to Access

This appendix is a compilation of data gleaned from a couple dozen papers, hours of testing with several versions of Access, and several years of experience as well as information from other developers. It highlights some of the key considerations in making informed decisions and plans for converting to and working in mixed environments with Access 2007. It also provides some steps for embarking on a conversion process, and deals with concerns for special circumstances such as work group...

Use Consistent Button Placement

Be consistent with button placement, size, and color. Close and Exit buttons should always be in the same places on the screen. The drill-down form the user opens by double-clicking on a row should also be available by clicking the left-most button (Detail) at the bottom of the screen (see Figure M-1). Also, provide Alt keys for your buttons. It's easy In the Caption property of each button, add an ampersand (& ) before the letter you want to use for the Alt key combination. Try to make them...

Users Collection

The Users collection contains all stored User objects of a Workspace or Group object (Microsoft Access workspaces only). This collection is hidden in Access 2007, but can be used when working with MDB files. Appends a new User object to the collection. Returns a count of User objects in the collection.

Using SQL When Opening Forms and Reports

Whenever you use the Access Wizard to build a command button to open a form or report with a filter to limit the records that are displayed, you are actually using SQL in VBA. The wizard builds VBA code to open the form with a WhereCondition, like this On Error GoTo Err_cmdCityBusinesses_Click ' & Me txtCity & ' The WhereCondition on the OpenForm command (it's the fourth parameter, using a variable named stLinkCriteria) is used to filter the form being opened to a set of records that meet...

Using the Source Code Control

Once Visual SourceSafe or other SCC control program is installed, a database for the code project has been configured, and users have been added, you're ready to begin using the SCC tools included in the ADE. The SCC add-in has a new, custom Ribbon tab, Source Control, which is added to the Access client when a database is open. This tab is the user interface for working with the SCC features. In this section, you'll see how each of the Source Control options works with the VSS source control...

What Happens When a MDB Is Opened by

Access 2007 has a multitude of new features for both the MDB and ACCDB file formats. When working with multiple versions of Access, trying to keep track of what will work can get rather confusing. The following table lists the new features and how they will behave in prior versions of Access. New features for Access 2007 .mdb filess are also available for .accdb files, but the reverse is not always true features that are available for 2007 .accdb files but not for 2007 .mdb files are denoted by...

Why Use Classes

From a coding perspective, the only real difference between using the built-in Access or VBA objects and the ones you write yourself, is that you have to instantiate your custom objects. Other than that, there's no difference at all. There is a learning curve associated with creating your own class objects, but once learned, the major benefit is much simpler and more manageable code. Let's say you are using API functions in your application. You can create your own interface that hides the...

Why Use DAO

Visual Basic programmers highly recommend ADO as their preferred object model for accessing databases. Although ADO is an excellent model with its own unique benefits, in the context of Access databases, it doesn't have the benefit of native database connectivity, which is where DAO has the distinct advantage. Applications written in other programming languages, such as Visual Basic, Delphi, and the like, must explicitly connect to the data source they intend to manipulate, and they must do so...

Working with Office Applications

Designing complete, fully functioning database solutions in Microsoft Access is done quite often without the need for working with any another Microsoft Office application. After all, you can use Access forms to enter data, Access reports to view and print data, and the SendObject method to send Access information via e-mail. On the other hand, it is not only possible, but extremely useful to leverage the features of other Office applications to enhance an Access solution with very few lines of...

Working with Outlooks Security Features

If you've previously implemented code similar to the preceding example, you've encountered the security dialog box that pops up when the Send method is called, as described in the preceding section. This is actually the second of two dialog boxes Microsoft added to Outlook 2003 in an attempt to prevent potentially malicious e-mails from spreading without user knowledge or intervention. The first dialog box appears when code tries to manipulate addresses in the Contacts folder. Sending an e-mail...

Writing the Callbacks

To provide the data for the drop-downs, you implement the getItemCount, getItemLabel, and getItemID callbacks you saw earlier. For this, you need a recordset. Start by declaring a Recordset object in the declarations section of the module Now, implement the callbacks. Because the tag attribute stores the name of the field, you can use that in your callback code Public Sub OnGetItemCount(ctl As IRibbonControl, ByRef Count) Dim strSQL As String ' get the unique data from tblContacts strSQL SELECT...

Excel

When it comes to working with external data, Excel files are among the most common files of external data that you work with in Access, so the new Access capability to specify the data types as you are importing the data is a great benefit and time saver. Importing and exporting to Excel follows the standard process that you are familiar with for text files. The wizard walks through the steps and provides options for specifying field types and even saving the specifications. This creates an...

Opening a Shaped Recordset

A useful feature of ADO is that you can create shaped recordsets. Data shaping enables you to define the columns of a recordset, the relationships between them, and the manner in which the recordset is populated with data. The columns can contain data from a provider such as Access or the SQL Server, references to another recordset, values derived from a calculation on a row, and so on. Here's a simple example from the NorthwindCS sample database (in the Chapter 19 code download), which has two...

User Level Security

The most robust form of security provided for the MDB file format is user-level security. It enables you to grant permissions to groups of users and or to specific users for each object in a database. Objects include tables, queries, forms, reports, and macros, as well as the database itself. Because user-level security provides such granular permissions to database objects, implementing this security can be quite complex. Thorough planning and documentation will be invaluable to set up and...

User Level Security Using ADO

In addition to DAO code, the Microsoft ACE Database Engine can employ ADO code to set up groups and users and to set up permissions for user-level security. Groups and users are set up using the SQL statements CREATE, ALTER, and DROP, which, as their names suggest, create, alter (or modify) and drop (or delete) user groups and users. The GROUP and USER keywords indicate whether the action is for a user group or user. The ADD and DROP statements add users to a group or remove them from a group....

How Access Projects Link to External Data Sources

ADP files are specifically designed to work with SQL Server and cannot be bound directly to any other data source. They are tightly bound with and optimized for use with SQL Server, so that all data processing is done on the server, which can greatly improve performance. Fortunately, SQL Server has strong capabilities that allow linking to many other data sources. Access projects rely on the linked server capability of SQL Server. Although architecturally different than linked ODBC tables in an...

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