Chances are that if you've heard about the recent Office security holes, you might have thought you weren't greatly impacted. The initial warnings may have misled you into thinking the problem effected only ODBC and Excel. In reality, the security issues are related to the Jet engine--which, of course, makes this a serious concern for Access users.

Early press releases indicated that the vulnerability only involved Jet 3.51, the version that ships with Office 97. However, while fixing the 3.51 problem, Microsoft also uncovered a different set of vulnerabilities in the version included with Office 2000.

Microsoft is being purposefully vague as to how an attack exploiting these vulnerabilities would work, so as not to educate anyone with a desire to do harm. Although there haven't been any reports of these weaknesses being exploited, Microsoft perceives a genuine risk and has created a patch that solves the problem. The patch also includes a tool that updates your system to require confirmation before Internet Explorer opens any Office document. Additional information and links to the patch can be found at:

http://www.microsoft.com/security/bulletins/ms99-030.asp

UP

If space on a form is limited, a combobox is definitely more economical than a listbox. Still, you may want users to be able
to see the contents of the combobox as soon as the control has focus. That way, they can quickly pick their choice and move on. Fortunately, as with most things in Access, you can manipulate just about anything with code. You can easily use VBA to automatically open the dropdown list as soon as a combobox has focus by using the control's Dropdown method. For instance, the code

Private Sub cboStock_GotFocus()

Me!cboCode.Dropdown

End Sub

can be used to display a short list of codes when the combobox receives focus.

UP

If you're working with a replicated database, always compact it twice before you synchronize. The first time you compact, Access marks objects that need to be deleted, but it doesn't actually remove them. However, the flagged objects are removed the second time you compact the database. Although there's no harm in additional compacting, there's no added benefit.

This is particularly important to do when working with the Design Master. Each time you make design changes to the Design Master, a copy of the original object is kept in the database. So, if you edit and save a form 10 times, your database actually has 9 older versions of the form in it. If you synchronize before compacting, all of the versions are sequentially sent to the other replicas until everything is in synch, and you can't reclaim the space taken up by the older versions of the form. However, if you compact the database first, only the most recent form version is sent to the replica.

UP

Sometimes you may want to provide feedback to users to let them know how many records will be effected by some code. If you've previously used the RecordCount property to do this with a recordset, you may have received unexpected results. That's because it won't return the correct number until the last record in the recordset has been accessed. To force the RecordCount property to return the correct number of undeleted records, use the MoveLast method first. Keep in mind that this may cause a performance hit on your application, so make sure it's necessary to work with the accurate record count before changing your code.

UP

In the summer of 1999, Microsoft was notified of a bug in the Jet 3.5 engine that left users vulnerable to attacks through
malicious Web sites. Microsoft initially played down the vulnerability by making it seem geared only toward Excel 97,
calling the issue the Excel 97 "ODBC Driver" Vulnerability.

However, the intertwined nature of Access and Jet make this an important security issue to fix. Microsoft released a patch for the problem in August, which also covered subsequent vulnerabilities found in Office 2000. Don't feel bad if you didn't apply the previous security patch-those of you that did will have to install an up-date anyway. In early October, Microsoft discretely released an update to their previous update, addressing additional security issues that were discovered by programmer Juan Carlos Garcia Cuar-tango of Spain. Curiously, although the security issue is now known as the Office ODBC Driver Vulnerability, the patch download pages (hosted on Excel's Office Update site) still claim to apply only to Excel. Oh, well. You can find links to the most up-to-date patches for Office 97 and 2000 at:

http://officeupdate.microsoft.com/articles/mdac_typ.htm

You'll now also find links to international flavors of the patch. For more details on the vulnerability, read the security bulletin at http://www.microsoft.com/security/bulletins/ms99-030.asp

UP

Anyone that's used Excel extensively knows how helpful the ability to freeze panes can be when working with large worksheets. This feature lets you keep specific data continuously displayed on the screen as you scroll through the worksheet. Access provides similar functionality for working with tables. You can freeze columns in Datasheet view and continue to horizontally scroll through records. To freeze a column, click on the field's name in Datasheet view to select the column. Then, choose Format/Freeze Columns from the menu bar. Access moves the column all the way to the left of the view and freezes it. If you freeze additional columns, they are moved to the right of the last frozen column. To return the view to normal, select Format/Unfreeze All Columns from the menu bar. Here's where you'll find a catch. Although the columns are no longer frozen on the screen, Access doesn't return them to their original positions within the Datasheet view.

UP

Normally, Access displays a splash screen when you launch it. You can easily replace the splash screen with a graphic that's more relevant to a particular database. To do so, simply save a bitmap graphic in the same folder as the database. Give the graphic the same name as your database and make sure it has a .BMP extension. From now on, when you launch Access by double-clicking on the database icon (or a shortcut to it), you'll see your own splash screen instead of Access's default image.

UP

When you use multicolumn combo boxes, you probably set the column widths for some columns to 0. This hides the data from the user, and is especially useful when the data source is from a table and you want to display lookup data without displaying primary keys. When you hide columns, Access displays the first visible column in the text portion of the combo box. Occasionally, this may not display the data you want. For example, let's say you have a cramped form and you want to use a combo box to select user names from your Employees table. When the combo box is open, you want to display data from the FirstName and LastName fields. However, once a selection is made, you want to display the user's initials. To do this, you won't really hide the Initials column, but for all practical purposes it will be invisible to the user. Assuming that your combo box's RowSource statement is:

SELECT Employees.EmployeeID, Employees.Initials,
Employees.FirstName,

Employees.LastName

FROM Employees;

you would simply set the following properties

ColumnCount     4

ColumnWidths    0";0.0098";1";1"

ListWidth       2"

UP

Access's MSysObjects system table contains information about all the tables in a database. You can use this information to delete tables or queries that meet specific criteria-and you don't need to unhide the system table to do so. For instance, you can use the code shown below to delete all tables with a name that begins with a "TEMP_" tag. If you want to change the code to delete queries, simply set [Type] equal to 5 and change the acTable constant to acQuery.

Public Function DelTable()

On Error GoTo DelTable_Err

    Do While DCount("[Name]", "MSysObjects", _

            "[Type]=1 And [Name] Like ""TEMP_*""") > 0

        DoCmd.DeleteObject acTable, DFirst("[Name]", _

            "MSysObjects", "[Type]=1 And [Name]
Like ""TEMP_*""")

    Loop

DelTable_Exit:

    Exit Function

DelTable_Err:

    MsgBox Error$

    Resume DelTable_Exit

End Function

Submitted by: Poul Erik Bjødstrup [bongbjornbak@get2net.dk]

UP

There are often times when it's helpful to view non-adjacent code in a module. However, continually scrolling through your code can be a real headache. Fortunately, you can easily split the module window into two scrollable panes. Simply drag the split box (the small rectangle that's directly above the vertical scroll bar) to where you want the split made. To remove the split, just double-click on the split line.

UP

A few weeks ago we provided you with a code sample to delete all data from an Access database while keeping the table structure intact. A few readers offered modifications to enhance the technique.

Henri Kover (henri_korver@wcj.com) pointed out that you might not want to delete data from linked tables. This can easily be
accommodated by checking the Connect property for each TableDef document. The modified code reads:

Dim ctr As Container, doc As Document, db As Database

Set db = CurrentDb()

Set ctr = db.Containers!tables

For Each doc In ctr.Documents

   If Left$(doc.Name, 4) <> "MSys" And _

     db.TableDefs(doc.Name).Connect = "" Then

   'Table is not a system table or a linked table

     db.Execute "DELETE [" & doc.Name & "].*" & _

         "From [" & doc.Name & "];"

   End If

Next doc

Several other readers noted that you must also have cascading updates/deletes enabled for this procedure to clear the data
from all tables. As an alternative, Stephen Bond (bond@clear.net.nz) suggests running the code multiple times. On the first pass through the database, the tables on one side of the relationship are cleared, allowing the remaining tables to be cleared on the next pass.

UP

Suppose you want to reference Access data in an email you're sending. If you can't take advantage of the File/Send feature,
or if you don't want to send the information as an attachment, you'll probably want to include the data as text within your
message. A logical way to accomplish this is to run a query, use the Publish It With MS Word feature to create an RTF file, and then copy and paste the results into the message. However, you may find that your email client loses too much of the formatting when you paste the information into your email, making it difficult to read. The alternative is to generate a no-frills, text version of the data. When you do so, Access inserts simulated row and column lines that help better organize your data. Ordinarily, you might go through several steps to do this. However, you can quickly create a plain text file as easily as you create an RTF--after a little setup.

First, right-click on the Database toolbar and choose Customize from the shortcut menu. Click on the Commands tab and you'll now see all of the available commands that can be added to your menus and toolbars. In the Categories list box, select Tools. Now, locate the Output To Notepad button in the Commands list box and drag it to the desired position on the Database toolbar. Then click Close.

You can now create quick text output of any object in the database window by selecting the object and clicking Output To Notepad. Note that if you want to open the objects first, you'll need to add the Output To Notepad button to the appropriate toolbars (Query Datasheet, Form View, Print Preview, and so on).

UP

If you've been developing in Access for a while, you've no doubt become quite intimate with the Northwind database. The sample database provides a sample of data and database objects that allow you to quickly test techniques without requiring a lot of setup or risking damage to your own data. The only problem is, despite how frequently the database gets used, Microsoft consistently stores it in an inconvenient location. Although the Access 2002 versions of the sample files are still kept in an out-of-the-way location, you finally have an easy way to get to them. Simply choose Help | Sample Databases from the menu bar and you can select one of the sample files installed on your computer.

UP

To open an Access object, such as a form or report, directly from the Windows Desktop, simply create a shortcut to the object. To do so, in the Database Window, right-click the object you want to create the shortcut to. Then, select Create Shortcut from the shortcut menu. Click OK in the Create Shortcut dialog box. When you do, Windows places the shortcut on the desktop. To test it, close Access and double-click the shortcut icon. Windows not only launches Access, but also opens the appropriate database and the object as well.

UP

When you add fields to a table, Access assumes you want to use a 50-character Text field by default. However, you may typically use a smaller field size or you may personally use Number fields more often than Text ones. You can avoid having to change the size and data types for new fields by setting defaults that are appropriate to your own design habits. To do so, choose Tools | Options from the menu bar and switch to the Tables/Queries sheet. Then, select the data type you use most from the Default Field Type dropdown list. Finally, set the Text and Number sizes you usually want to use in the Default Field Sizes panel and click OK.

UP