Using the Access Object Model to Pull Data from Access to Other Applications

The following sections don't use any VBA within Access, but if you're going to use Access to control other applications, chances are you'll eventually need to use VBA or VBScript within other applications to manipulate Microsoft Access. We'll only spend a very brief couple of pages on these concepts. You can utilize the Access object model from Visual Basic to enhance your Visual Basic applications with Access reports. Before you can write code utilizing the Access object model from your Visual...

Schema Argument Values

The Schema argument specifies the type of information to return and its values are defined in the following table. We have included only those values that have meaning in Access. The four remaining values, adSchemaActions, adSchemaCommands, adSchemaFunctions, and adSchemaSets relate specifically to the Microsoft OLE DB Provider for OLAP Services library. For more information about these values, refer to the Microsoft KnowledgeBase. Returns the constraints defined in the catalog. Unsupported by...

Referencing a Library

When you set a reference to an external library in Access 2003, you can use its functions as though they were built-in to Access. You can reference type libraries, object libraries, and control libraries. For example, because the Microsoft Excel Object Library is itself a library of functions, you can reference (link to) it in Access and use its functions as though they were part of Access. To reference a library, launch the References dialog box, as shown in Figure 13-3, by selecting...

Application SetOption Method

The SetOption method of the Application object allows you to control all of the options you can see by selecting Tools Options from the main Access window. The following tables detail the string arguments for the options available on each tab. 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'll need to manipulate the SetOption method are broken down by the tab of...

Creating Tables and Fields

For our invoicing system, we 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, delete 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 collection. 5. Append the TableDef object to the Database's...

NotInList Adding a Value to a Combo Box at Runtime

The NotInList event property is triggered when you enter a value into a combo box that is not contained within the list. This event property and procedure works in conjunction with the LimitToList property, which is a Boolean property of a combo box that instructs Access to restrict selections to values in the list. This example comes from the need to add a new value to the underlying table used to populate a combo box list. Because a combo box was designed to display values from a table, it...

On Close Save a Setting to the Registry

In case you are unfamiliar with Windows, there is a very important database called the registry. In Windows 3.1, the registration database stored OLE registration information and file associations, while program settings were stored in initialization .ini files. With the introduction of Windows 95 and later versions, the registry became the central repository for all this information. As you have read, the registry database is vital to the operation of all applications installed on your...

Properties of the Report Object

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 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 the type of border and border elements for the report Specifies the caption in the title bar for the report Specifies...

OnChange Late Bind a Subform on a Tab Control

The degradation of performance is a never-ending battle for the Access developer as users want more and more information, on the same form no less. But, no matter how accommodating you are to satiate their every need and desire, the smallest of performance hits causes your phone to ring and e-mail inbox to overflow with complaints. Even when you tell them ahead of time The reduction of Access performance sometimes sneaks up on you slowly, other times it's instantaneous. In case you're new, the...

Setting Macro Security Level

If you've worked with Word or Excel documents that have macros VBA code in them, you've probably seen the Security Warning message like the one showing in Figure 20-2. If you have seen that warning, you probably already have an idea about how to adjust the security settings in Access so that you can open your database. The instructions are pretty basic but have some implications that you should consider. To set up Access so that you can open your database, select Tools Macro Security. You will...

Access Wizards

As you can tell from the extensive list of enhanced wizards, Access is getting more user-friendly all the time. Adds an AutoDialer control to a form, datasheet, or toolbar. The wizard incorporates modem information and dials the number Creates a form automatically based on the selected table or query. Well, the end result is enhanced to the extent that forms themselves are enhanced Applies a predefined style and format to a form or report, and allows creation of custom styles Creates a data...

Using the ADE Tools

Access Developer Extensions ADE is a set of great tools for the serious, and possibly not-so-serious, Access application developer. The ADE package includes tools that enhance your ability to design, develop, and deploy Access-based applications. The ADE package includes a tool designed to improve your application development capabilities the Property Scanner. The Property Scanner makes it easy to search collections, objects, and properties of a database for the occurrence of a particular term...

Create a Switchboard through the Switchboard Manager

How Use Switchboard Manager 2003

There's another easy way to create a switchboard for your application. You can use the Switchboard Manager to create a new switchboard or edit an existing switchboard. Choose Database Utilities from the Tools menu and select Switchboard Manager from the popout menu see Figure 1-11 . To create a new switchboard, click the New button. Enter a name for your new switchboard. Once you've created your switchboard, click Edit to control the items displayed on your switchboard. For a new switchboard,...

Creating an MDW

Your first step in creating user-level security is to select or create the MDW file that will contain the user groups and users you will define. As mentioned earlier, this can be a new file or the default file provided when Access is first used. The recommended approach is to create a new MDW file. The User-level Security Wizard will not permit you to use the default SYSTEM.MDW file. Also, if you corrupt the default MDW file, you will have to manually recover that file from another source. Many...

Regedtexe

Prior to Windows XP and Windows Server 2003, Regedt32.exe was the preferred 32-bit Registry Editor for Windows NT and 2000. But, of course, nothing is perfect, and Regedt32.exe had limitations, for example, it could not import or export Registry entries .reg files. Now, under Windows XP and Windows Server 2003, Regedt32.exe is a simple wrapper program that runs Regedit.exe. On Windows NT and 2000, you should use Regedt32.exe whereas on Windows XP and Windows Server 2003, you can use either...

Sharing Information Is a Two Way Street

When sharing information between multiple Microsoft Office programs, you can write code two ways. The first way is to write the code within Access to push the data into the other Office programs. The second way is to write code within those other programs to pull code from Access into the other program. This two-way street works for Word, Excel, Outlook, and PowerPoint. Because this is an Access 2003 VBA book, we'll spend more time covering the push direction, but don't worry, we'll provide...

Creating Other Types of Outlook Items from Access

Creating e-mail messages in Outlook isn't the only way you can use VBA and Outlook to enhance your application. You can create meetings, appointments, tasks, and journal items within Outlook using VBA. The Planning department can create an Outlook task directly from the Access application. This task will remind them to check with the receiving department on the appointed day to ensure the frame material has arrived. If the material hasn't arrived within several days, the Planning department...

Forms as Objects

By now you should have a fair grasp on how to create classes and class objects in Access 2003. Something you might not be aware of is the fact that since 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...

Begin at the Beginning

To create a switchboard, you'll first need to create a new form. Choose Forms from the Access bar and click the New button to launch the New Form dialog box. For a switchboard form you don't need to choose a table or query for the form's data source, just choose Design View from the list box and click OK. You can give switchboard forms a nice title and maybe a couple of lines of text to serve as a brief explanation to users. After you've added these elements to the form, it might look similar...

Associating Code to an Event Property

Property Access Event

When you are ready to begin associating VBA code to an event property, you can start from either the Design View of the Form or the VBA Editor window itself. Both methods are useful, and when you utilize them will depend on your current location form or editor at the time that you need to start a new procedure. From the Form Design window, click to highlight the desired control, then choose View Properties from the main menu. You can also press F4 or just double-click the control to open the...

The CurrentDb Function

Access only ever maintains a single permanent reference to the current database. The first member of the Databases collection is populated with a reference to the current database at startup. This reference, pointed to by DBEngine 0 0 , is fine under most circumstances, but when, for example, you are working on wizards, it is not always up-to-date. In these circumstances it is possible for the first database collection member to point to something other than the default database. The chance of...

Using Jet and ADO to Set a Database Password

You can use Visual Basic code to set or change a database password. To change the password, open the database in Exclusive mode and use the SQL statement ALTER DATABASE PASSWORD newpassword oldpassword. The password is case sensitive and must be specified within square brackets . When setting a password on a database that does not currently have a password, use NULL without the brackets as the old password. When removing a password from a database use, NULL without the brackets as the new...

Pivot Charts

Pivot charts were introduced in Access 2002. Amazingly, they are like golden nuggets hiding under a rock they have not been publicized and exploited. Anyone who is struggling with cross tab queries should take a look at PivotTable and PivotChart views for forms. These incredibly powerful views allow users to look at data in countless ways. It can be drilled into, grouped by criteria, limited to the top X, expanded, sorted, totaled, subtotaled, charted, diagramed, and the list of criteria is...

An Advanced Example Creating a Graph in Access and Inserting It into Power Point

There aren't many applications for using VBA to send data from Access to PowerPoint. However, one application that's often requested but a little tricky to accomplish is exporting a graph from Access to PowerPoint. You could take advantage of the following example to update your presentation with the most current graph from your Access report. This procedure is a bit complicated so we'll cover it in several steps. First of all, there's no easy method of copying a chart from within Access. Some...

Open or Print Any File

The following procedure allows you to open or print any file, without needing to know what its executable program is. For example, this same procedure can be used to open or print a Word or PDF document, an Excel spreadsheet, or an ASCII text file. Public Const SW_HIDE 0 Public Const SW_MINIMIZE 6 Public Declare Function ShellExecute Lib shell32.dll Alias ShellExecuteA _ ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As...

Maintaining Permissions with DAO

With your understanding of the detachment between the MDW file and the file that is being secured see the first section under User-Level Security you recognize that unlike users and groups that are maintained through the DB Engine Workspace, permissions are set against the current database. The following procedures demonstrate setting permissions for Containers SetPermissionsOnContainerDAO and Objects SetPermissionsOnObjectDAO . Setting permissions for Containers Tables, Queries, Forms, Macros...

Responding to Errors

The main reason you'll need to debug your code is because Access has displayed an error message. Hopefully you've put error handling in your code, which can make this activity easier. This topic is covered extensively in Chapter 9. Let's say you've coded a cool copy routine like the one shown earlier in this chapter. However, when you try it, Access displays an error. If you don't have error handing, a message box will be displayed, as shown in Figure 8-2. If you do have error handling, good...

Introducing the Win API

API stands for application programming interface. It is simply a group of standard functions that are packaged together and made available to application programmers. There are quite a few APIs, but the one that you've probably heard most about is the Windows API. The Windows API consists of many DLLs that make up the Windows operating system and ensure that every application that runs under Windows behaves in a consistent manner. What this actually means is that standard Windows operations,...

Handling Rounding Issues

Some of the more difficult to understand and debug errors in Access are rounding problems. These 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...

Figure

Closed Bmp

Select the desired control from the ActiveX Control dialog box, and it will be added to the form. Depending on the type of control selected, it may need to be moved, resized, or have its properties modified. It is important to note that an ActiveX control has properties that are displayed in the standard properties window, but a control can also possess another set of custom properties that were added by the control creator. In Figure 10-9, both the standard and custom property windows are...

Access Security Model Overview

With so many methods to set up security in Access, you can easily become overwhelmed deciding which method to choose. This section provides a synopsis of Access security methods, with suggestions for when to use one method over another and when to combine methods. This section also covers the relative difficulty of using these methods. There are two common scenarios used when setting up Access databases Standalone databases which contain all of the objects necessary to maintain the desired...

Using the Win Registry APIs

This section describes the Win32 Registry API functions you can use to access and manipulate a wider range of Registry keys than you can with the inbuilt VBA functions. Before attempting this section, however, we strongly advise that you read Chapter 14, Extending VBA with APIs. The Win32 API provides all the functions you'll need to access the Registry. Of course, the scope of some functions is restricted for purely common-sense reasons after all, there is little point in making changes to the...

Before Update Performing Data Validation

The BeforeUpdate property is triggered before a change or entry to a form or control is committed. This step allows for intervention between the time the user sets the value and when the value is actually saved. In the following example, a sample of performing data validation is demonstrated. Data validation is the process of ensuring that the value entered by the user is logical and or does not validate other business rule logic. Private Sub txtEndDate_BeforeUpdate Cancel As Integer 'Len...

Date Handling

The way Access stores and manipulates dates can be a source of confusion to developers, especially those who remember the older database methods of storing days, months, and years in date fields. Access handles dates in an elegant, easy-to-use way. Access stores a particular date as the number of days that have elapsed since an arbitrary starting zero date which happens to be December 30, 1899 . You can prove this to yourself by typing the following in the Immediate Window you can bring up the...

Using Expression Builder

Where Access 2003 Expression Builder

The Expression Builder does just that allows you to build an expression with the various fields and controls in your database. For example, in response to the click event of a command button, you can populate a text box on your form with the name of a particular product from the Products table. To build your expression, double-click the Tables branch of the tree in the bottom-left pane of the Expression Builder. Choose the Products table and all of the fields from that table appear in the...

Cascading Combo Boxes

Sometimes, you would like your user to choose a value of a combo box, and then use that value to limit the selections in another combo box. Since the upper combo box affects the lower, this is sometimes called cascading the combo boxes. To accomplish this, you need the SQL statement building techniques described earlier in this chapter. Let's say we have two combo boxes one for County and one for City. Each County can have many Cities and each City is in one County. The table design would look...

Macros in Access

This section provides an introduction to using macros in Access 2003. Not much has changed in the last several versions of Access with respect to macro recording, but if you're just picking up Access 2003 for the first time, this section is for you. You can use macros for a variety of tasks in Access. Even though it might sound a bit crazy, we usually prefer to write code rather than create a macro. However, that's not always the easiest or most logical method of automation. Access 2003...

Responding to Events and the With Events Keyword

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 are class modules too, so you can also use the WithEvents keyword in forms. The following...

Using SQL for Report Selection Criteria

Many developers build Access reports so that their users can quickly view and print out their data. Consider a report to list businesses from the database, as shown in Figure 14-6. 1 y Microsoft Access My Application O ur C ity 's B usiness Co mitiuitity However, your user will often want to print only some of the records, based on selection criteria. You can create a different report for each selection criteria, but that approach will result in duplication of report code and difficulty in...

Properties of the Application Object

The following table lists the various properties of the Application object. Please note that this table contains not only string and Boolean properties, but also properties that refer to other objects within the Access object model. Those objects are discussed later in this appendix. Used to reference the current AnswerWizard object Used to reference the current Application object for example, Me.Application on a form References the Office Assistant object Returns an AutoCorrect object that...

Creating Tables and Columns

Let's replicate the design of the two invoicing system tables we created in Chapter 6. The basic procedure for creating a table in ADO is as follows 1. Create a Catalog object and define its ActiveConnection property. 3. Check if the table already exists, and if so, delete it. 4. Create the table object in memory using the New keyword. 5. Create the Column objects in memory, using the table's Append method, setting each column's attributes as appropriate. 6. Append the Table object to the...

Using the User Level Security Wizard

User Level Security Wizard Acces 2013

This wizard is a great way to start setting up security on a database. By using the wizard you will secure the database so that Your new administrator user will own the database and each object in the database. Your new administrator user will have full access to the database. The Admins group will have full access to the database. The default Admin user will have no access to the database. The Users group will have no access to the database. The database itself is encoded see the section...

The Errors Collection

The first thing to remember about the DAO Errors collection is that it is not the same as the VBA.Err object. The VBA.Err object is a single object that stores information about the last VBA error. The DAO Errors collection stores information about the last DAO error. Any operation performed on any DAO object can generate an error. The DBEngine.Errors collection stores all the error objects that are added as the result of an error that occurs during a single DAO operation. Each Error object in...

Display Informative Form Captions

If you don't set your own form Captions, Access will just display the form name there, as shown in Figure I-6. This is a sure sign of a novice developer. You need to at least replace the Caption with the name of your application. One nice additional feature for the Caption is to indicate which back-end database you are currently using. That way, your user knows instantly whether they are in the Production or Test database, for example. You'll need a table in the back-end database to store...

Open Recordset Constants

There are a variety of constants you'll use when writing VBA code. The following tables list just a few of the constants you might use when opening a DAO recordset. A runtime error occurs if you attempt to use dbOpenTable in the following Microsoft Jet workspace situations When the recordset is based on a QueryDef. When the Type argument is set to dbOpenSnapshot. When the Source argument refers to an SQL statement or TableDef that refers to a linked-table. The following table lists the...

C

16-bit numeric parameters, 406 32-bit numeric parameters, 406-407 8-bit numeric parameters, 405-406 Any datatype, 413 boolean parameters, 407-408 currency parameters, 407 floating-point parameters, 407 handle parameters, 408-409 object parameters, 409 pointers to arrays, 411-412 pointers to C structures, 411 pointers to functions, 412-413 pointers to numeric values, 411 signed and unsigned integers, 405 string parameters, 409-410 variant parameters, 410-411 cabinet file CAB , 71 Call statement,...

Methods of the DoCmd Object

The methods of the DoCmd object are listed in the following table. All of the methods here actually carry out an action with the same name as the method. We've included the description of the action, rather than the description of the method which merely calls the action . Creates a custom menu bar, shortcut bar, or shortcut menu Used to apply a filter, a query, or a SQL WHERE clause to a table, form, or report Used to cancel the event that caused Access to run the macro or module containing...

The Package Wizard

The Package Wizard is like the extra point after a touchdown. It produces incredibly professional-looking setup routines. Now you can deploy Access solutions with the same panache as Microsoft and all the other big guys. The Package Wizard builds the cabinet file CAB that includes Windows Registry Keys and even the digital certificate, if appropriate, that need to be included when deploying an Access solution. The Package Wizard can automatically include the Access Runtime and any other files...

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 dbOpenTable recordsets, the Source argument can only be the name of a table. The default recordset type that is opened if you omit the Type argument, depends on...

Creating Schema Recordsets

You're no doubt familiar with using recordsets to access and manipulate data. But ADO also allows you to open recordsets that contain information about your database's tables. Of course, you can get at this information using ADOX, but some details are more readily accessed using ADO schema recordsets. To open a schema recordset, you issue the OpenSchema method against the Connection object. The OpenSchema method has three parameters you can use to specify more options. connection.OpenSchema...

Linking to a User Level Secured Database

Unlike shared-level security, linking to tables in a user-level secured database will not store security information. In fact, you must be joined to the MDW file associated with the database you are linking to before opening a database that links to it. Recall that the username and password prompt only appear once per Access session. This happens when you take certain actions such as opening a database or attempting to maintain security. This establishes your identity to the MDW file you are...

Properties of the Form Object

The Form object contains a variety of properties, methods, and events that are listed in the following tables. Used with the Screen object to determine the control that has the focus Indicates which macro, event procedure, or user-defined function runs when the AfterDelConfirm event occurs Indicates which macro, event procedure, or user-defined function runs when the AfterFinalRender event occurs Indicates which macro, event procedure, or user-defined function runs when the AfterInsert event...

Using Access Projects

Since Access projects don't store tables in the ADP file, you just need to reconnect the ADP to the SQL Server database used by the application. You can use a similar startup form as suggested for an MDB to collect a username and password or you can just run code automatically if you only allow integrated security. Whether or not you use a login form, code to reconnect the ADP would be similar to the following Dim strServerName As String Dim strDatabase As String Dim strUN As String Dim strPW...

PopUp Memo Workspace Form with Spell Check

Sometimes you want to give your user more room to enter long text into a memo field. Instead of using the built-in Access zoom feature, you can include a workspace feature to zoom into a memo field, allow the user to OK or Cancel their changes, and even invoke the Word spell-checking feature if Word is installed on their PC . This feature is shown in Figure I-14. Telephone. Email. Website. Business Typ.i Member Since Payments Telephone. Email. Website. Business Typ.i Member Since Payments These...

Linking to External Data

To create a link to an external data source from an Access MDB file, select Get External Data from the File menu, and click Link Tables. A Link dialog box that looks similar to the one shown in Figure 17-1 should then appear. Although the dialog box defaults to Access databases, you can click the Files of Type drop-down box and select from various indexed sequential access method ISAM and Open Database Connectivity ODBC data sources. ISAM drivers are typically, but not always, used for...

Opening an External Database

Sometimes you need to work with data in another Access database, a dBase IV database, or Excel spreadsheet, but you don't want a permanent link. You can do so by opening a temporary connection to it with the Workspace object's OpenDatabase method. Although this method belongs to the Workspace object, I am describing it in this section because the end result is that a new albeit temporary Database object is added to the Databases collection. The OpenDatabase method is fairly straightforward. Set...

Execute OptionEnum Values

The ExecuteOptionEnum values specify 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 the...

Sample Set Up Using ADO

The following procedure ShowSampleADO shows various ways to call the previous ADO procedures. Embedded comments explain how things work. Sub ShowSampleADO Debug.Print Start Samples 1 add a user with a Personal ID and Password Call AddUserADO DerfFred, 34434ox94f21, DerfFred ' if you do not know the old password, 1 specifying the NULL keyword will work. ' See the AlterUserPasswordADO procedure for more information Call NULL, NULL ' To embed a space in the user name, ' enclose the username in...

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 . Notice that when a user is added, you can specify the personal ID. Also notice that the AddUserADO procedure automatically adds the user to the group Users with a call to AddUserToGroup. This is done to keep the MDW file consistent with the Access rule that requires that all users belong to the Users group. If you wish to add a usemame...

Opening a Recordset Containing More Than One Select Query

As in DAO, you can create a recordset containing more than one SELECT query. The following example demonstrates how to create and use such a recordset. Start by creating a stored procedure to do the job CREATE PROCEDURE dbo.MultiSelect AS SELECT FROM Invoices SELECT FROM Customers strSQL SELECT FROM Invoices SELECT FROM Customers In the example that follows, we have used a stored procedure. You might recall from the same section in Chapter 6 that each SQL statement is separated by a semicolon....

Methods DAOprivDBEngine

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

L

Last used list, storing, 587 LastMember property, 378 late bind subform on tab control, 293-297 less than lt , 63 Lib keyword, 403 library, referencing, 398-401 LimitToList property, 284, 297 line continuation, 91-93 Linked Table Manager, 49 linked tables, 115, 552-555 linking libraries in Access 2003 APIs, declaring, 401-404 declaring, 398 library referencing, 398-401 referencing, 398 linking to external data, 532-538 literal constants dates, 93 numbers, 93 strings, 93 declaring, 93...

Initialize and Terminate Events

Every class module has two built-in events that fire automatically Initialize and Terminate. The Initialize event fires when the class instance is first created. You can use the Initialize event to set default property values and create references to other objects. The Terminate event fires before the object is destroyed, and is normally used to clean up local object references. To define code for these events, select Class from the Object drop-down list and then select the event from the...

Managing Multiple Form Instances

Handling multiple instances of the same form is not as straightforward as it might seem. The reason is that the default opening behavior of a form is all the same instance of the form. Understanding the concept of an instance is as simple as counting to 1. There is only one form, and every time that form is opened, it is the same form. The form opens and displays the data specified in the Recordsource property. This limitation is not typically a problem for the average application, as a user is...

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

How Microsoft Access Resolves VBA References

When Access needs to use the file you've referenced, it does so in the following sequence. 1. The location indicated in the References dialog box is checked. 2. Access checks to see if the file is already loaded. 3. Access 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 Application folder where msaccess.exe is located System...

Executing Queries

There are three ways to programmatically execute a query using the DoCmd.RunSQL method, the object .Execute method, and the OpenRecordset method. Not all of these methods return records. 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 object model, you can execute the RunSQL method of the DoCmd object to run an action query DoCmd.RunSQL UPDATE Table1 SET...

Using Multiple Recordsets

You can easily keep track of multiple open recordsets at once. Each one needs to be defined with a Dim statement and opened using OpenRecordset, and they are kept completely separate by Access. Each recordset has its own current record pointer often called a cursor , End of File EOF , and Beginning of File BOF values, and so on. This technique is necessary to perform the following trick Copy a parent record and all of its child records into the same tables. Copying Trees of Parent and Child...

Creating Forms and Controls with VBA

Access has built-in functions for creating new form and control objects programmatically. These are the same functions that the wizards use to create new objects and work the same regardless of whether you are using an MDB or ADP file. The CreateForm method creates a form and returns a Form object. CreateForm database , formtemplate The CreateForm method has the following arguments. The CreateForm method has the following arguments. A string expression identifying the name of the database that...

The Replace OrderByClause and Replace WhereClause Functions

It is often necessary to cut and replace the Where and Order By clauses of a SQL string using VBA. Throughout this chapter, we used the ReplaceWhereClause and ReplaceOrderByClause functions to do this. Finally, here is the code that was 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 will rarely be called...

Sorting on Columns

Vba Sort Userform

Users often expect the ability to sort on columns, similar to other Windows applications like Outlook. 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 14-1. In this example, the user can click either the Business Name column heading or the Contact Name column heading. The two toggle buttons are in an option group called optSort. This control has an After Update event that contains the...

Run Command Method Arguments

One of the easiest ways to perform a variety of functions in Microsoft Access is through use of the RunCommand method. You can use this anywhere within your code. The RunCommand method takes a single argument, the acCommand constant. All of the available acCommand constants are listed in the following table. For convenience, they are listed in two columns. Each of these arguments represents some command in Access that is accessible from a menu or toolbar. acCmdAboutMicrosoftAccess...

Creating a Workspace

When you first refer to a Workspace object, or one of its collections, objects, methods, or properties, you automatically create the default workspace, which can be referenced using the following syntaxes DBEngine.Workspaces 0 , DBEngine 0 , or simply Workspaces 0 . The default workspace is given the name Default Workspace . In the absence of user- and group-level security, the default workspace's UserName property is set to Admin. If security is implemented, the UserName property is set to the...

Methods of the Application Object

The following table lists all of the methods available from the Application object. If any arguments for the methods are present, those are also listed in the following table. For example, you can use the CompactRepair method of the Application object with the following code. c DB SamplesSm.mdb, c DB log.txt c DB SamplesSm.mdb, c DB log.txt Return a string associated with an Access or DAO error Adds a hyperlink address to the Favorites folder name of the current database Returns a parsed...