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

The Implements Keyword

The Implements keyword in VBA allows you to implement interface inheritance, giving programmers access to a form of polymorphism. For example, suppose we have two objects, Object1 and Object2. If Object1 inherits the interface exposed by Object2, we can say that Object1 is a kind of Object2, which is polymorphism in a nutshell. Often interfaces are referred to as a Contract. This is because there is an agreement between the creator of the object and its user that the object will provide all the...

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

Using a Self Certification Certificate on Another Computer

Since self-certified databases won't be trusted on another computer, you need to add your self-certification certificate to other computers that will be accessing your databases. To do this you need to create a file from your certificate, copy the file to the other computer, and add the certificate to that computer. One way to create the Certificate CER file is to view the details of the certificate from the Visual Basic Editor. To get to the details of the certificate, select Tools Digital...

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

Raising Custom Defined Errors

The descriptions for a great many error messages must have been written by programmers whose native language was something other than English. Some of them make for interesting reading, but there are quite a few that don't go too far toward educating you about the reason for the problem or its resolution . Raising your own errors provides the flexibility of displaying more user-friendly or user-specific error messages. The VBA Err object provides a Raise method, which lets you construct and...

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

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

Adding References to Your Projects

As noted above, adding references to libraries from your Access project can allow you to quickly enhance the functionality of your application with less coding effort. The types of type or object libraries that Access can reference include Type libraries OLB, TLB, and DLL You have seen that you can automate Office applications by adding a reference to one of the libraries that comes with Microsoft Office, for example, Microsoft Office 11.0 Object Library. Here are the steps necessary to add a...

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

Writing Code within Modules

You can store various subs, functions, and declarations within a module. To view all modules currently available in your Access database, click the Modules tab of the Access action pane. To view a module, select the module and choose Design from the database window's toolbar see Figure 2-1 . We'll go into details on the various components of the VBA Editor in the Chapter 4, VBA Basics, but you'll notice that by default, the VBA Editor contains a Project...

Smart Tags

Vba Smarttag

Smart Tags are available in Microsoft Office XP. In general, they allow you to provide addition information about a record or specific data point see Figure 3-18 or to perform an action that would typically require opening another program to perform. Access 2002 relied on tags that were in Word and Excel data however, Smart Tags are now directly supported in Access 2003. Smart Tags can be used on tables, queries, forms, reports, and Data Pages. They can also be exported to HTML, and work on...

Step Define the Application Startup Options

In step 3 you will tell the wizard how to build the shortcut to your application and where to put the shortcut. Figure 19-11 shows the various shortcut options. The following explains the command line parameters that can be included in the shortcut. You may have the shortcut to your application added to the Start Menu or to the user's Desktop. Name is the name you want for the shortcut and you can include a special Icon to display for the shortcut. The icon file will be included in the...

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

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

The Seek Method

The Seek method is the fastest way to find a specific record, but it can only be used 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. Syntax rst.Seek comparison, key1, key2. . .key13 To use Seek, you must specify three things the name of the index to use you can only specify one index at a time , a...

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

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

Shared Level Secured Back End Databases

Be careful while linking to tables in a shared-level protected database. When your front end links to tables in the protected back-end database, you will be required to specify its password. After the tables are linked, the link and the password are recorded. This is true whether you link from another Access database or create an ODC linking file through Open Database Connectivity ODBC to access the protected database from Excel or Word. After the link has been established, anyone can then open...

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

Using the User Level Security Wizard

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

Custom Startup Wizard

Access 2003 Developer Extensions

The ADE package includes the Custom Startup Wizard to help you set the startup properties of the database just before you create a compiled version of the database. The startup properties determine how the database will display when the user opens it. For example, it controls whether the database window display or not. So, the Custom Startup Wizard helps adjust the startup properties so that when a user opens your database, the display is presented exactly the way you want the user to see it....

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

Methods of the Form Object

The Form object also has a number of methods you can use within your code. Those methods are listed 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 Used to...

Reg CreateKeyEx

This API function creates a new registry key under the one you specify, but if the key already exists, it opens that key. This is a more sophisticated function thant RegCreateKey, and is Declare Function RegCreateKeyEx Lib advapi32.dll _ Alias RegCreateKeyExA _ lpSecurityAttributes As SECURITY_ATTRIBUTES, _ Hkey Long Integer The handle of the open key, or one of the LpSubKey String The name of the new subkey to create. Reserved Long Integer This is a reserved parameter. Set it to zero LpClass...

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

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

Events of the Form Object

There are a large number of events you'll use within your code behind forms. You probably will only use a handful of these events, but they are all available for you when writing code. A Form's events are summarized in the following table. Occurs when the form receives focus and becomes the active window Occurs after the user confirms the delete and the records are actually deleted Occurs after all elements in the PivotChart have been rendered Occurs after all charts in the PivotChart have been...

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

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

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

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

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

Binding to a Report

Unlike with forms, list boxes, and combo boxes, reports are not nearly as easy to dynamically bind to an active recordset. In addition, it's not possible at all with MDB files. The key difference is that the recordset has to be a shaped recordset, using the Microsoft Data Shaping services for OLEDB MSDataShape provider or the Microsoft Client Data Manager Microsoft.Access.OLEDB.10.0 provider. For example, the Invoice report in the NorthwindCS.adp sample file is based on the invoices view stored...

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