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

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

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

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

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

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

Property Scanner

The ADE package includes an add-in for Access called the Property Scanner. As an add-in it is accessed from within Access while you have your database open. As the name suggests, the ADE Property Scanner scans collections, objects, and properties of a database for the occurrence of a particular term or value. This includes pretty much every type of object your application can have the project, the current DB, documents, containers, tables, references, queries, forms, reports, macros, modules,...

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

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

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

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

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

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

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

Tables

The following tables are a compilation of terms and tags from a multitude of sources. They are by no means a comprehensive listing of all the tags currently in use or that have been used, as new tags are generated as programs evolve and as developers create their own objects. That being said, the hope is that having a comprehensive list will save research time while trying to select the right tag or interpret an existing tag. The following table is a compilation of tags and the objects that...

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