 Top
How to avoid a circular reference when using the same name repeatedly.
Q: I need to use a calculated value with a column in a query, but I need to keep the alias the same name as the column name. How do I avoid a circular reference error?
A: Never create circular references (this is when an object gets a value from itself before changing it to a new value, which would alter the original value, which would affect the new value, and so on). To avoid the circular reference, qualify which column it is by identifying the table name with the source column name. For example:
SELECT ID, Nz(tblOrderItems.NumItems, 0) AS NumItems
FROM tblOrderItems;
Another common error is "Control has a reference to itself." This is when the bound field has a text box of the same name and the control's Control Source Property uses this name to assign a value. For example, if both the field and text box were named StartDate, then the following expression would produce the "Control has a reference to itself" error.
=Format([StartDate],"mm\/dd\/yy")
The solution is to name the control something else, such as txtStartDate.
Top
How to compact a database using the command-line switch.
Q: How can I compact a database using the command line or through a shortcut?
A: You can use the /compact command-line switch to compact the database. Use syntax such as the following on the command line or in the "Target" Property of a Windows shortcut (watch out for word wrap):
"<FullPathToMSOffice>\MSAccess.EXE" "<FullPathToSourceDB>\Source.MDB" /compact "<FullPathToTargetDB>\Target.MDB"
Top
How to create a hot key for a control on a form that has had the label deleted previously.
Q: I have a combo box on my form. Originally, the combo box didn't need a label to identify it, so I deleted it. Now that I'm adding hot keys to other controls on the form, I added a new label for this combo box, but because the label isn't actually attached to the combo box, I can't create the hot key. Will I have to recreate the combo box from scratch just to get an attached label?
A: No. Select the unattached label with your mouse. Right click and select "Cut" from the pop-up menu. Select the combo box with the mouse. Right click and select "Paste" from the pop-up menu. The label is now attached to the combo box.
Now create the hot key for the label by altering the Caption Property of the label. Add an & before the character that is to serve as the hot key for the combo box. The hot key will now be used to set focus to the attached combo box.
Top
How to determine which version of MS Office is installed.
Q: How can I find out which version of MS Office is installed on my or my users' computers, including which service packs?
A: There's no setting or property that one may read to determine which version of Office is installed. One can compare the versions of the Office executables with the following lists to determine which version, and which service pack, is installed.
Microsoft Office 2003:
|
ExecName
|
Office_03
|
Office_03_SP1
|
|
MSAccess.exe
|
11.0.5614.0
|
11.0.6355.0
|
|
WinWord.exe
|
11.0.5604.0
|
11.0.6502.0
|
|
Excel.Exe
|
11.0.5612.0
|
11.0.6355.0
|
|
FrontPage.exe
|
|
11.0.6356.0
|
|
Outlook.exe
|
11.0.5510.0
|
11.0.6353.0
|
|
PowerPnt.exe
|
11.0.5529.0
|
11.0.6361.0
|
|
WinProj.exe
|
|
11.1.2004.1707.15
|
|
MSPub.exe
|
11.0.5525.0
|
11.0.6255.0
|
|
Visio.exe
|
|
11.4301.6360
|
Microsoft Office XP:
|
ExecName
|
Office_XP
|
Office_XP_SP1
|
Office_XP_SP2
|
Office_XP_SP3
|
|
MSAccess.exe
|
10.0.2627.1
|
10.0.3409.0
|
10.0.4302.0
|
10.0.6501.0
|
|
WinWord.exe
|
10.0.2627.0
|
10.0.3416.0
|
10.0.4219.0
|
10.0.6612.0
|
|
Excel.Exe
|
10.0.2614.0
|
10.0.3506.0
|
10.0.4302.0
|
10.0.6501.0
|
|
FrontPage.exe
|
10.0.2623.0
|
10.0.3402.0
|
10.0.4128.0
|
10.0.6308.0
|
|
Outlook.exe
|
10.0.2627.1
|
10.0.3416.0
|
10.0.4024.0
|
10.0.6626.0
|
|
PowerPnt.exe
|
10.0.2623.0
|
10.0.3506.0
|
10.0.4205.0
|
10.0.6501.0
|
|
Visio.exe
|
10.0.525
|
|
|
|
Microsoft Office 2000:
|
ExecName
|
Office_2K
|
Office_2K_SP1
|
Office_2K_SP3
|
|
MSAccess.exe
|
9.0.0.2719
|
9.0.0.3822
|
9.0.0.6620
|
|
WinWord.exe
|
9.0.0.2717
|
9.0.0.3822
|
9.0.0.8216
|
|
Excel.Exe
|
9.0.0.2719
|
9.0.0.3822
|
9.0.0.8216
|
|
FrontPage.exe
|
4.0.2.2717
|
4.0.2.3821
|
|
|
Outlook.exe
|
9.0.0.2416
|
9.0.0.2416
|
9.0.0.6604
|
|
PowerPnt.exe
|
9.0.0.2716
|
9.0.0.3821
|
9.0.0.6620
|
|
WinProj.exe
|
8.0.98.407
|
|
|
Top
How to enable the Control Wizards to create new controls on forms and reports.
Q: When I create a new command button on my form, the Wizard doesn't appear any more to assist me in writing the code. For that matter, there's no Wizard for the combo box or any other controls that I create. This functionality is no longer available. Where did my Wizards disappear to?
A: It's possible to turn the Control Wizards on and off. Open a form in Design View so that the Toolbox toolbar is displayed. You'll see the arrow cursor button at one end of the Toolbox toolbar and right next to it you'll see a button with a magic wand with magic dust (okay, three rocks) spilling out of it, and ellipses (three horizontal dots) on it. That button is the "Control Wizards" button. Select this button and you'll see your Wizards apparate back from Hogwarts School of Witchcraft and Wizardry the next time you want to use them.
Top
How to hide all menu bars and toolbars.
Q: Is there any way of "hiding" the menu bar when you start an Access application? When I start my application it shows my start up form but also a menu bar containing:
File Edit Insert Records Window Help
A: Do you wish to hide the built-in menu bar, or all menu bars, or both menu bars and toolbars?
If you just want to hide the built-in menu bar and display only your own custom menu bar, then first ensure that your custom menu bar is displayed, then select the Tools -> Startup... menu to open the Startup dialog window. Select the name of the custom menu bar in the "Menu Bar:" combo box, then uncheck the "Allow Full Menus" check box, then close the Startup dialog window. Only your custom menu bar is displayed. Whenever you open this database, the built-in menu bar will be displayed for a second (well, that depends upon how fast your CPU is and how much memory is being used), then it will be replaced by your custom menu bar.
If you want to hide all menu bars, including your own custom menu bar, then first ensure that your custom menu bar is displayed, then select the Tools -> Startup... menu to open the Startup dialog window. Select the name of the custom menu bar in the "Menu Bar:" combo box, then uncheck the "Allow Full Menus" check box, then close the Startup dialog window. Close the database. Open the database again, then right-click on the menu bar and select "Customize..." from the pop-up menu to open the Customize dialog window. Select the "Toolbars" tab and uncheck the name of your custom menu bar. Select the "Close" button to save your change.
All menu bars are now hidden.
If you want to hide all menu bars and toolbars, then execute the steps listed above, but also uncheck the "Allow Built-In Toolbars" check box in the Startup dialog window. Or you may hide the entire Access window on start up. Please see the following Web page for instructions:
http://www.mvps.org/access/api/api0019.htm
To adjust these start up settings in the future, open the database while pressing the <SHIFT> key and continue holding the <SHIFT> key until the database is completely open. The built-in menu bar and toolbar will appear, so select the Tools -> Startup... menu to change these start up settings.
Top
How to kick users out of the database when they've been idle too long.
Q: I have users that open the Access application, then leave their desks for long lengths of time. Is there a way to kick them out when they've left for lunch so that I can do some maintenance on the Access application?
A: (Courtesy of Tom Wickerath) Consider implementing a method of closing inactive sessions. This will help prevent a user from keeping the database open for long periods of time with no activity. Here is one method of doing this:
HOW TO: Detect User Idle Time or Inactivity in Access 2000
http://support.microsoft.com/?id=210297
I tend to use Const IDLEMINUTES = 20 (instead of the 1 minute setting shown in the Knowledge Base article), and I do not display a message box as shown in the article, since message boxes are modal and this would defeat any attempt to close:
Sub IdleTimeDetected(sngExpiredMinutes)
'Dim strMessage As String
'strMessage = "No user activity detected in the last" & vbCrLf
'strMessage = strMessage & sngExpiredMinutes & " minute(s)!"
'MsgBox strMessage, vbInformation, "No Sign of Activity!"
Application.Quit acSaveYes
End Sub
Top
How to overcome limitations in the Access Runtime version.
Q: When I deploy my database to users who have the Access Runtime version instead of the Access retail version, many of the built-in features (such as the built-in menus) are missing. Is there any way to get this built-in functionality in the Runtime version, too? Is there any way of knowing exactly which features won't be available to users with the Runtime version?
A: Usually, but not always. You'll need to customize the settings and write VBA code for these functionalities into the Runtime version of your application. This includes custom menu bars and tool bars, special user interfaces, and more robust error handling techniques. When testing these new functionalities and features while developing with the retail version of Access, you can use the /runtime command-line switch to open the Access database application in simulated Runtime mode. For example (watch out for word wrap, as this is all one line):
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /runtime "C:\Data\MyDB.mdb"
For more information, please see the following Web pages:
http://support.microsoft.com/kb/q208730
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ac2003_ta/ html/odc_AcBasicsofRuntime.asp?frame=true
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ac2003_ta/ html/OfficeAccessDeployMDB.asp?frame=true
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ac2003_ta/ html/OfficeAccessDeployMDB_Part2.asp?frame=true
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/de ovrManipulatingCommandBarsCommandBarControlsWithVBACode.asp?frame=true
Top
How to prevent new users from opening the database when the database is in shared mode.
Q: When I'm testing a multiuser database, I need to open it in shared mode for multiple connections, but I don't want to allow any of the other users to open and use it during my test. Is there a way to avoid opening the database in exclusive mode, yet limit the total number of users who can use the database at the same time?
A: Yes. Create a button on a form and name it "NewUsersBtn." Set the button's Caption property as "New Users Allowed" then copy and paste the following code into the form's module (requires ADODB library reference):
Private Sub NewUsersBtn_Click()
On Error GoTo ErrHandler
Const NEW_USERS As String = "New Users Allowed"
Const NO_NEW_USERS As String = "Don't Allow New Users"
Const BLOCK As Integer = 1
Const UNBLOCK As Integer = 2
If (Me!NewUsersBtn.Caption = NO_NEW_USERS) Then
CurrentProject.Connection.Properties("Jet OLEDB:Connection Control") = BLOCK
Me!NewUsersBtn.Caption = NEW_USERS
Else
CurrentProject.Connection.Properties("Jet OLEDB:Connection Control") = UNBLOCK
Me!NewUsersBtn.Caption = NO_NEW_USERS
End If
Exit Sub
ErrHandler:
MsgBox "Error in NewUsersBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub ' NewUsersBtn_Click( )
Save and compile the code. Later, open the database application in shared mode for as many test connections as you need. Then open the form and press the button so that the button's caption reads: No new users allowed. No more users will be able to open the Access database application until you either press the button again or after you close all current connections to the database application.
Top
How to prevent the Linked Table Manager from prompting for the new location of every linked table.
Q: When I use the Linked Table in one of my databases, I'm prompted one at a time for the new location of each linked table that I've selected in the database, instead of being prompted only once like my other databases. This database has lots of linked tables, so this is time consuming. Is there a way to have the Linked Table Manager remember the database from one link to the next?
A: The linked tables in this database link to more than one database, and at least one of the table names in one database matches the table name in another database. To resolve any ambiguity, the Linked Table Manager requires that you select which database to link the table to, but this is a group setting so it will be applied to the entire list of selected tables to connect to. This is why you see the prompt which database to use to link each table to.
To prevent this from happening, do any of the following:
1. Don't use the Linked Table Manager. Instead, store the linking information (database path and file name, linked table name, and local table name) in a table, then use VBA code to retrieve this information and link the tables as convenient.
2. Rename some of the tables so that all table names in the databases linked to are unique.
3. Use remote queries for the tables with non-unique names. For example, the following remote query will retrieve all records and all fields in the Orders table in the Northwind.mdb database:
The query can then be used as if this table were a local table or a linked table in the current database.
Top
How to prevent the nag for Windows registration and delay Windows activation.
Q: Every time I install Windows XP on a computer, I have to go through the time-consuming "Would you like to register Windows now?" and "Would you like to activate Windows now?" If I don't activate Windows it expires in three days, which might not be enough to install and troubleshoot new hardware and software. Is there a way to bypass both the registration and activation, or at least delay Windows activation for more than the allotted three days?
A: Yes. To avoid being asked to register Windows, edit the following Windows Registry key (watch out for word wrap):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\RegDone
Place the string value 1 in this key.
One cannot legally bypass Windows activation, but one can delay the activation for a period of 30 days if one installs Windows XP SP-2 on the computer.
Top
How to preview or E-mail a report when no printer is installed on the computer.
Q: When I try to see or E-mail a report on a computer that isn't connected to a printer, I receive an error message that no printer is installed. I'm not trying to print the report. I just want to see it or send it. Is there a way to prevent this error message or fool Access that a printer is connected to this computer when it is not?
A: The default view for opening a report is "Normal," which due to legacy applications sends the report to the printer to print. Therefore, whenever a report is opened, the necessary criteria are checked, including whether the printer is on line. If there is no printer installed, then the error message is displayed, even when the report is not intended to be sent to the printer. However, it is not necessary to connect a printer to the computer, as long as a printer driver is installed on the computer. You'll find many downloadable printer drivers at www.Xerox.com.
Top
How to reinitialize Windows after editing the Windows Registry.
Q: When I'm making changes to the Windows Registry, I keep having to reboot the computer afterwards. This can be multiple reboots for multiple changes, which can be time-consuming. Is there any way to skip the reboot process, but still have the latest changes take effect?
A: Yes. The next time you feel that a reboot is necessary after an adjustment to the Windows Registry -- but for whatever reason you don't want to reboot -- open the Windows Task Manager and select Explorer.exe in the "Processes" tab. Select the "End Process" button to shut it down. Select the "Applications" tab and then select the "New Task…" button. Type Explorer.exe in the text box and then select the "OK" button to start the process again. All Windows Registry entries that are read during bootup will be re-read (initialized) again without the trouble of rebooting.
Top
How to reset the AutoNumber in a table.
Q: How do I reset the AutoNumber in a table?
A: The Autonumber is not guaranteed to be sequential, so if you need a field value incremented by a value of exactly one for each new record created -- without any gaps -- then you shouldn't be using an Autonumber for the field's data type, because one shouldn't assign an Autonumber to a field that has meaning. If you need this field to mean something, then create a user-defined method to assign and maintain sequential numbers for each record.
That said, if there are no relationships established between this table and any other tables that utilize this AutoNumber field, then there are two methods for resetting the AutoNumber while there are records in the table. If the AutoNumber field is the Primary Key of the table, then use method #1. If it is not, then you may want to use method #2 for best results.
Method 1: Open the table in Design View. Delete the AutoNumber field. Save the table. Create a new field with the same name as the former one and make the data type AutoNumber. Save the table and open it in Datasheet View. If this field is the Primary Key of the table then the AutoNumber will have sequential numbers from one to the ordinal number of the last record in the table. If this field is not the Primary Key, then the order may not appear to be in sequential order, but the records will be numbered sequentially without gaps.
Method 2: Rename the current table with an "_Old" suffix, such as "tblData_Old." Open it in Design View and save it as the original name of the table by selecting the File menu -> Save As. Next, create an append query and paste the following SQL statement into it:
INSERT INTO tblData
SELECT tblData_Old.Fld1, tblData_Old.Fld2
FROM tblData_Old
ORDER BY ID;
Replace tblData with the name of your original table. Replace tblData_Old with the name of your renamed table. Replace ID with the name of your AutoNumber field.
Replace tblData_Old.Fld1 with the name of your renamed table, then the dot operator, and then the name of the first field in your table. Explicitly identifying which table the field is using in the SELECT clause will avoid any ambiguities. Continue adding field names for all of the fields in the table using this same pattern until all except the AutoNumber field are in the SELECT clause.
Run the query. The AutoNumber field in the new table will be numbered sequentially, from one to the last ordinal numbered record in the table. Delete the tblData_Old table, then compact/repair the database to remove the unneeded table.
Top
How to restore scrollbar movement in the Database window when using Windows XP Themes.
Q: When I'm working in an Access 2003 database, I frequently return to the Database window after working on the design of a form or report only to find that the vertical scrollbar doesn't respond to my mouse. I can use the arrow keys and tab key to select items in the Database window, but I can't see the names of the objects at the bottom of the list because the scrollbar is frozen. To get the scrollbar to move again, I can either open another tab in the Database window and then return to the tab I was in, or I can set focus to another application, then return to Access. I've been advised to turn Windows XP Themes off, but I like them. Is there another way to prevent this annoying behavior?
A: Unfortunately, Windows XP Themes don't work well with Microsoft Access. If you don't want to use the "switch back and forth" method to reset the focus back to the desired tab, then you can use the mouse to resize the database window by dragging it until it's just slightly larger or smaller. The scrollbar will once again "notice" your mouse again.
Top
Why a split database is so slow, in "really simple language."
Q: I just split my access database and the performance is horrible! I've been reading all the articles posted on increasing performance, but I was wondering if someone could explain, in really simple language, why the unsplit database (located on a server and buried in a folder) runs so much faster than the split one. It's in the same location. The only difference is the split. I've read all the solutions, but I want to know the cause.
A: It's much slower because you built the database for a single user who opens the database located on his own workstation, not for a networked, multiuser database application.
Does the front end reside on your own workstation or on the server? If it's on the server, then your computer has to make multiple requests across the network to retrieve data for multiple files.
Really simple language = "Two calls take more than twice as long as one call, because you have to wait in line to make the second call."
Does your front end maintain a persistent connection to the back end? If not, the locking database file will be established each time a connection to one of the tables in the back end is needed. That means that the Windows Networking API's have to be checked on every directory in the path to ensure that your User ID has permission to access that directory. The deeper down the directory structure, then the more times these Windows Networking API's get called. And then the .LDB file has to be created. This can take a lot of extra time.
Really simple language = "Take one step forward. Dig through your purse and find your wallet to check the name on your drivers license, the state that issued it, and whether the drivers license expiration date is valid, then put the wallet back in your purse. Report the results to your boss. Take another step forward. Dig through your purse and find your wallet to check the name on your drivers license, the state that issued it, and whether the drivers license expiration date is valid, then put the wallet back in your purse. Report the results to your boss. Take another step forward . . . " and so on for every directory in the path. When you get to your destination, build the desk and chair you'll be working on while you're there.
Does the path or file name use Windows long name conventions or the old DOS 8.3 naming conventions? If it's not DOS 8.3, then the Windows Long Names API has to be consulted to resolve the name.
Really simple language = "Call your co-worker on the phone and ask, 'How do you spell <insert any word here>.' Wait for your co-worker to look it up in the dictionary, then write it down for you, then walk over to your desk from his office across the building to hand you the slip of paper. After you finish all the other tasks you've been assigned while you were waiting, you read it."
I could go on, but I think you get the idea. Now go implement those solutions you've found. And remember, primary keys and indexes are your best friends.
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
Visitors since Oct. '04:
|
