Replies

John,

No really haven’t made any changes since code was originally posted. One thing to note, at that time was using Access 2000 (aka A2K), am now using Access 2003. AFAIK there’s been no changes to the DAO DBEngine.CompactDatabase method used in the sample code. However in Access 2002 (aka “Access XP”), a new method of the Access Application object was introduced, the Application CompactRepair method. Like the DAO method there are parameters where you specify a SourceFile and a DestinationFile. The only new thing I noticed is an optional LogFile argument – according to VBA Help, “True if a log file is created in the destination directory to record any corruption detected in the source file. A log file is only created if corruption is detected in the source file. If LogFile is False or omitted, no log file is created, even if corruption is detected in the source file.” So if you think the log file may be useful, you could try using this method in place of the DAO CompactDatabase method. Note that the DAO method provides other options, such as the option to encrypt or decrypt the compacted db file, not provided by the newer method.

The main issue with the Application CompactRepair method is same as with the older method, it cannot be used with the current database. As noted in Help: “The source file must not be the current database or be open by any other user, since calling this method will open the file exclusively.”

HTH

I sometimes work with Visual FoxPro databases (under duress) but do not know of any way to link a VFP table in Access w/o specifying an ODBC DSN (Data Source Name). One Access app I maintain needs to connect with a VFP 6.0 database on the user’s machine. To address this issue, the app allows the user to configure the DSN and VFP connection (the user only needs to specify path to .DBC file, which is usually installed in a default location). You can create the DSN programatically by using the DAO DBEngine RegisterDatabase method, which writes the necessary settings to the Registry as a new key under HKEY_CURRENT_USERSoftwareODBCODBC.INI (if DSN with same name already exists, it is updated). Once DSN created, tables in the source database can be linked via various methods (DAO or ADOX). When linking VFP tables, the “Select Unique Record Identifier” dialog may appear, requiring user to select one or more fields that act as unique index. Only way I know to avoid this is to use ADOX objects and methods to link table, and the dialog will not appear. Only catch is, the linked table will not be updatable (some VFP tables already have a unique index defined and this will not be an issue).

The attached text file (exported code module) provides some examples of code used to create DSN and link tables programatically. Code was tested in Access 2003 but same code also worked in A2K.

Only other option to avoid DSN would be to establish a connection via ADO using the Visual FoxPro OLEDB provider (vfpoledb.dll) (currently version 9.0). See TestOleDbVFP sub in attached file. However you’d have to work with recordsets, not linked tables, if using this method. The VFP 9.0 provider can be downloaded from Microsoft, see att’d file for current URL. The drawback would be, you’d have to be sure that the user has the provider installed on their system, or the code will fail. If the end user has Visual FoxPro 7.0 or later installed, they probably already have it. Otherwise, probably not. (The provider has to registered on user’s system; the MS download file (vfpoledb.exe) accomplishes this). Note, if the provider is installed, the end user does not have to have VFP itself installed.

HTH

Attached text file is exported code module with example of a procedure (ExportTableDesign) that will loop thru all tables in current database (excluding system tables, linked tables, etc – can modify as desired) & export field names, data type, size, and description to an Excel file. Each table is exported to a separate worksheet. If additional descriptive data required, code can be modified accordingly. I tested this using Access/Excel versions 2000 & 2003 and worked OK. Note – requires reference set to DAO 3.6 & Excel (applicable version) object libraries.

HTH

John,

Thanx for additional info — I tried suggestion to turn off the “Use Windows Themed Controls” setting but still got screen flicker if any “unassociated” labels on form. Not sure if this has any effect if not using Windows XP – at work we are still using Windows 2000 (SP4) — at home on XP systems I also use “Windows Classic” style – not a fan of default XP theme. Also not sure if this setting is per-user on a given machine, or per-database – the Access 2003 “Help” system is NOT an improvement over its undistinguished predecessors.

To avoid the “screen flicker” issue in future plan to simply use locked, unbound textboxes in place of “unassociated” labels.

As usual, a day late & a dollar short, but we just upgraded from Office 2K to Office 2003 at work, and while converting one of my Access front end databases to 2003, noticed for first time the same (very annoying) “screen flicker” – at first not sure of cause, but some testing soon revealed that “unattached” (“unassociated”) labels on tabbed forms were at fault. (These labels are used on form for explanatory text, etc.) To fix, as workaround created “dummy” locked, unenabled textboxes w/transparent borders, backcolor, etc to attach the labels to, w/the dummy textboxes positioned “underneath” label. This fixed problem in most cases though in few instances still observed some residual “flicker”. I never had this problem in earlier versions of Access. Another reason not to “upgrade” to 2003 unless you are a big fan of “XML integration” and so on. Not to mention, if you have a front end w/a lot of complex objects (forms, reports) in 2003 the app will crash at the drop of a hat – just look at it funny on the screen & it will crash on you, with that stupid MS “We regret the inconvenience” dialog box. The only reason I don’t stick with more stable 2000 file format is because in ACC 2003 you cannot “compile” 2000-format front end db as an .MDE.

Could not find anything in MSKB that addresses this issue. Would like to know if MS has any intention to fix. My theory is, it has something to do with their great new “Error Checking” feature for “unassociated labels” — that’s the only thing that computes…

I will admit, the Office 2003 UI looks nicer….

Anyway was glad to locate this post so know it just wasn’t me who experienced the very annoying “flicker” phenomenon in 2003.

Not sure if “Application.Version” works in Access 2K??

? Application.Version
‘ generated error

I usually use the SysCmd function for this purpose:

? SysCmd(acSysCmdAccessVer)
9.0

The SysCmd function can also return the directory (folder) where the running instance of MSACCESS.EXE is installed, which can also be useful:

? SysCmd(acSysCmdAccessDir)
C:Program FilesMicrosoft OfficeOffice

Note: The above tested on “standard” Access 2K installation (vers. 9.0 = A2K).

HTH

On my system (Office 2K, running on WIN XP) the FileSystemObject methods correctly listed files with the .lnk extension. Not sure why your system did not reflect this. Anyway if interested see attached text file, exported VBA code module. The ListFiles sub lists all files in specified folder (using FSO) & related info. If file has “.lnk” extension the FSO File.Type property returns “Shortcut” (based solely on file extension as registered in Windows Registry). However you can rename a file with .lnk extension whether or not a valid shortcut (you can tell if valid or not by right-clicking file & opening “Properties” dialog – the Target Type will read, “This is not a valid shortcut.”) You could use ShellExecute function to open Properties dialog programatically, but that wouldn’t be too useful… Tried using WSH methods to verify if valid shortcut, but that did not work either – there is no “OpenShortcut” method, only the CreateShortcut method (which can be used to open existing shortcut). This proved not very useful, since you can create a new shortcut w/o target path, etc, specified – see IsShortcutWSH function as an exercise in futility. Finally decided most reliable method is that used in IsShortcut function – if valid file spec with .lnk extension, read the first 20 bytes of file into a Byte array. A valid shortcut file will always have the letter “L” (or Hex 4C) (4 bytes) followed by a 16-byte GUID in the first 20 bytes of file header (you can test this by running ListFiles with a folder that has valid shortcut files – you should see the same 20 byte values listed). The bogus “.lnk” file will return False when run function. If running repeatedly you’d probably want to populate the bTest byte array only once. See attached text file for details. This worked correctly on two different Office 2K/WIN XP systems. You may be able to adapt this for your purposes.

HTH

There’s no such thing as an “Option Button” or Option Group for toolbar, the Office CommandBar object model does not provide this functionality. Your choices for toolbar controls are limited to command buttons, menus, edit controls, and combo boxes. You can simulate the functionality of an Option Group by setting the CommandBarButton State property to “Up” (not selected) or “Down” (selected) to indicate which “option” is currently selected.

Attached file (Access 2K format) demonstrates how to do this using the CommandBarButton OnAction property. Custom toolbar “Toolbar 1” should be displayed when open db. When you click one of the card suit buttons, the button will be “selected”. The buttons under “Menu 1” are grouped into “Option Groups” – click buttons to see how works. The text-only menu items will display a checkmark to indicate the “selected” option. In actual use you’d replace the dummy msgbox with actual code to run when button is selected. The code is generic so that you don’t have to specify which group the button is in – just be sure that each button in “option group” has the same Tag property, and that the Tag is unique for each group.

Public Function ButtonOnActionFunction()
SetToolbarButtonState
MsgBox "Toolbar Button " & Application.CommandBars.ActionControl.Caption & " OnAction function.", _
vbInformation, "BUTTON ON ACTION FUNCTION"
End Function

Private Function SetToolbarButtonState()
On Error GoTo Err_Handler

Dim ctl As Office.CommandBarControl
Dim ctlActive As Office.CommandBarControl
Dim strMsg As String
Dim strCaption As String

Set ctlActive = Application.CommandBars.ActionControl
strCaption = ctlActive.Caption

' Loop thru controls in parent object:
For Each ctl In ctlActive.Parent.Controls
' test Tag property to match that of active button control
If ctl.Tag = ctlActive.Tag Then
If ctl.Caption = strCaption Then
' "Select" item:
ctl.State = msoButtonDown
Else
' "De-Select" item:
ctl.State = msoButtonUp
End If
Else
' do nothing - not in same "Option group"
End If
Next ctl

Exit_Proc:
Set ctl = Nothing
Set ctlActive = Nothing
Exit Function
Err_Handler:
Select Case Err.Number
Case 0
Resume Next
Case Else
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "SetToolbarButtonState - Unexpected Error"
Resume Exit_Proc
End Select
End Function

Note – can also define event procedures for command bar controls but in this case was simpler to use OnAction property to run code.

HTH

Attached is example of a VBA procedure that will hide all visible toolbars, with an optional parameter that allows you to specify an “exception” (toolbar or menu to keep visible). There’s a second optional parameter that allows you to hide the main Menu Bar as well (not normally recommended). Sample code:

Public Sub HideAllToolbars(Optional ByRef strToolbar As String = "", _
Optional ByRef HideMenu As Boolean = False)

On Error GoTo Err_Handler

Dim strMsg As String
Dim cbr As Office.CommandBar 'ref required to MS Office Object Library

' strToolbar (optional) = name of Toolbar to keep visible, leave blank to hide all -
' default value if missing is empty string, so should not be possible to match any other visible toolbar
' HideMenu (optional) = set to True to hide app main menu bar (normally would want False)

For Each cbr In Application.CommandBars
If cbr.Type msoBarTypeMenuBar Then
If cbr.Visible = True Then
If cbr.Name strToolbar Then
cbr.Visible = False
End If
End If
Else 'Menu bar
' Exclude built-in Menu Bar (Error No -2147467259: Method 'Visible' of object 'CommandBar' failed)
' Can set Visible to True if hidden, but cannot set to False if visible
If HideMenu = True Then
If cbr.Name strToolbar Then
' the ShowToolbar command allows you to hide Menu:
DoCmd.ShowToolbar cbr.Name, acToolbarNo
End If
End If
End If
Next cbr

Exit_Proc:
Set cbr = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 0
Resume Next
Case Else
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "HideAllToolbars Sub - Unexpected Error"
Resume Exit_Proc
End Select

End Sub

Example of use – hide ALL command bars (both menus and toolbars):

HideAllToolbars ,True

Hide all toolbars, except toolbar named Toolbar1 (keep main menu visible):

HideAllToolbars "Toolbar1"

Hide all toolbars and menus, except toolbar named Toolbar1:

HideAllToolbars "Toolbar1", True

Hide all toolbars and menus, except custom menu named Menu1:

HideAllToolbars "Menu1", True

Code requires set reference to Office X.0 Object Library. Worked OK in Access 2K. Note comments in reference to hiding the main Menu Bar. If using custom menus and toolbars in your application, I’d recommend use the Access Startup options to disable built-in toolbars and shortcut menus, and toolbar customization, and design custom application menu bar, toolbars, and shortcut (popup) menus to replace the built-in ones. All forms and reports should have their own toolbar and/or shortcut menu specified (can design a “generic” form or report toolbar for all forms/reports). This approach would result in better protection for database and would tend to obviate the need to run procedures like the example above.

HTH

Louise,

Glad worked OK. One final note I neglected to mention earlier, if for some reason did not want to use functions, as alternative could use the following expressions as the Control Source in the textboxes on report to calculate the next Sunday and following Saturday. For “FROM” date:

=Date()-Weekday(Date())+8

For “TO” date:

=Date()-Weekday(Date())+14

This should provide same results. I like to use functions in some cases because more flexible, and if needed in more than one place in database you don’t have to re-invent the wheel & figure out the formula again. But if not familiar with VBA code, using expressions like the examples above are a perfectly valid & simpler alternative.

HTH

PS – attached is copy of your sample database, I modified the report to illustrate how to use the functions in report textbox – see textbox ControlSource property:

=GetFirstDayOfWeek(Date(),1)

(I had to reformat slightly so date would fit in box.) Note – as is case with queries, you cannot used named VBA constants in ControlSource expression, you’d have to use the numerical equivalent. This doesn’t apply in the case of your report because the week begins with Sunday, which is the default in VBA.

HTH

Below are examples of some user-defined functions you could use to calculate the beginning of week and end of week dates, relative to a specified date:

Private Const DAYS_IN_WEEK = 7

Public Function GetFirstDayOfWeek(ByRef DateRef As Date, _
Optional ByRef WeekOffset As Long = 0, _
Optional ByRef FirstDayOfWeek As VbDayOfWeek = vbSunday) As Date
On Error GoTo Err_Handler
Dim strMsg As String

' Assumes Sun as default 1st day of week if FirstDayOfWeek not specified
GetFirstDayOfWeek = DateRef - Weekday(DateRef, FirstDayOfWeek) + (DAYS_IN_WEEK * WeekOffset) + 1

Exit_Proc:
Exit Function
Err_Handler:
Select Case Err.Number
Case 0
Resume Next
Case Else
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "GetFirstDayOfWeek Function - Unexpected Error"
Resume Exit_Proc
End Select
End Function

Public Function GetLastDayOfWeek(ByRef DateRef As Date, _
Optional ByRef WeekOffset As Long = 0, _
Optional ByRef FirstDayOfWeek As VbDayOfWeek = vbSunday) As Date
On Error GoTo Err_Handler
Dim strMsg As String

' Assumes Sun as default 1st day of week if FirstDayOfWeek not specified
GetLastDayOfWeek = DateRef - Weekday(DateRef, FirstDayOfWeek) + DAYS_IN_WEEK * (WeekOffset + 1)

Exit_Proc:
Exit Function
Err_Handler:
Select Case Err.Number
Case 0
Resume Next
Case Else
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "GetLastDayOfWeek Function - Unexpected Error"
Resume Exit_Proc
End Select
End Function

Examples of use (current week):

? GetFirstDayOfWeek(Date())
5/21/2006
? GetLastDayOfWeek(Date())
5/27/2006

‘To get next Sunday, relative to today:
? GetFirstDayOfWeek(Date(),1)
5/28/2006
‘ To get next Saturday, relative to today:
? GetLastDayOfWeek(Date(),1)
6/3/2006

Optional parameters are used to make the functions more flexible, in case the “week” needs to be defined other than the VBA default, where Sunday is first day of week and Saturday the last day of week, and to allow “offsets”. Example using optional parameters:

? GetFirstDayOfWeek(#1/1/2006#,-1,vbMonday)
12/19/2005
? GetLastDayOfWeek(#1/1/2006#,-1,vbMonday)
12/25/2005

The sample code can be copied into a standard code module and then used in your application where needed. Note: if using function in query expression, replace the “VbDayOfWeek ” constant with its numerical value, ie, vbSunday = 1, vbMonday = 2, etc.

HTH

NYIntensity posted an example a while back, see Re: Query Design view context menus (XP). You may get some ideas from the attached database.

HTH

As Hans noted, the command button bitmaps in Access aren’t same thing as the Office FaceID commandbar button images. If interested in determining what images correspond to the FaceID numbers, see the att’d demo database (Access 2000 format). Open basToolbar and run Sub CreateNewToolbars(). This procedure will create new custom toolbars, each with 200 command buttons that display the FaceID indicated by button caption. On my system (Office 2K) the sub “errored out” after FaceID #5684 (Toolbar number 29). This number may be different on other versions of Office. Do not know of simple way to determine which FaceID’s are blank other than running procedure & displaying results. Requires reference to applicable MS Office object library.

NOTE: The sub displays only the last toolbar created. Experimented with displaying all of them at once, but discovered this is NOT a good idea unless you want to experience potential “out of memory” issues. Also, after creating the new toolbars the file size increased from appx 116 KB to 272 KB (after compacting).

HTH

As noted you could use a query expression rather than user-defined function to perform this calculation, but the expression will need to be somewhat more convoluted. Attached db file (A2K format) shows an example, see query qry_GetAge, which uses either method to calculate age in years or months, or either depending on person’s age. Note that if calculating months only, you can format results to include an “M” using format string “#M” (the backslash “escapes” the “M” as a literal), and still use the resulting numbers in calculations (see Age Months (Formatted)); but when combining years (number) and months (formatted text) in same calculated field (Age Years Or Months (Expr)), only the numerical results will be usable in calculation, as shown by Test Calc column. Do not know of a way to apply more than one format to a single field in query. This is expression I used:

Age Years Or Months (Expr): IIf(IIf(Date()24,IIf(Date()<DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])),DateDiff("yyyy",[BirthDate],Date())-1,DateDiff("yyyy",[BirthDate],Date())),Format(IIf(Date()<DateSerial(Year(Date()),Month(Date()),Day([BirthDate])),DateDiff("m",[BirthDate],Date())-1,DateDiff("m",[BirthDate],Date())),"#M"))

The db uses copy of Northwind “Employees” table (with some dummy records added) to test query. Generally a query that uses expressions using only built-in VBA functions will open quicker that equivalent query that uses user-defined VBA functions. The logic is easier to follow in a function, but with small sets of records the difference in query execution time will be negligible.

HTH