Replace system error message with a custom message
In Access 2003/XP/2000/97, I have a form that is linked to an Oracle table. When I enter duplicate data, I receive an error message "ODBC - failed... Unique Constraint violated...".

---

When I go to open .mdb it gives and error then installs
I have Office XP installed and all apps within it work just fine when I open them except Access. When I try to open Access with a shortcut on my desktop, I get and error that says "Windows cannot find the file...". Behind that message, another opens that says "Preparing to Install... please wait while Windows configures Office XP Professional" If I go to the folder called C:Program FilesMicrosoft OfficeOffice10 and click on the MSACCESS.EXE file itself, there is no error, but it does the install part. Word and Excel start perfectly without this issue. I tried using the cd to repair the installation, but this had no effect.

---

Access 2007 Error - when I try to import a text file
I am having some issues with Access 2007. When I try to import a text file into a table using a predefined spec, the routine will run for a while and then error out with the message "the search key was not found in an record". Any ideas on what this is and how I can fix it??

---

Delete Duplicate Records From Access Tables
Yes, deleting duplicates should be easy, but it's not. A Google search for newsgroup posts about deleting duplicates scored over 2500 hits. Clearly, this is a common problem. The confusion is compounded, no doubt, by the existence of the built-in Find Duplicates query wizard. While this wizard does a great job of helping you identify duplicates, it does not provide a way to delete them. Many posts at the newsgroup included frustrated comments like this: "I can see them, but I can't delete them!"

---

What exactly does "null" mean in Database?
First I tried to look at it as a "zero" (with a little confusion though) when I came across it in some queries, or think about it as a text string of blank spaces. Null means either "not applicable" or "don't know": it's not really the same as zero (0) or any other default value, but more importantly, null is treated quite differently from other values in SQL, because it literally has no value.

---

Error accessing file - or - This action will reset the current code in break mode
When you try to view the code for a Visual Basic for Applications module, compile a database, create an MDE file, or execute code in an Access 2000 database, you may see one of the following messages: "Error accessing file. Network connection may have been lost." "This action will reset the current code in break mode."

---

SQL Operators and Operands
In order to use a computer language, you usually give instructions to an internal program. An instruction can be formulated as a command. Examples are "give me food" or "touch your head". Another type of instruction can be formulated as a question. Examples are "what time is it?" or "Are you hungry?". The sentence or group of words that constitute an instruction is also called a statement.

---

Avoiding divide by zero errors in Access
If you try to divide a number by zero in Access, you receive a #DIV/0 error if you are in a form or report, or a #ERROR error if you are in a query. To avoid a divide by zero error, you can use the IIF function to set the results field to a zero-length string ("") if the divisor in a calculation is zero. You can then search the field for any values that contain a zero-length string and flag the results. The following steps demonstrate this technique by creating a table and a bound form, inserting three text boxes, and entering an expression.

---

How to fix a Compile error in query expression Error
In Access 2003/XP/2000/97, I tried using the Left function in a query and I get the following error: ("Compile error.in query expression") I've seen this function work in other Access databases. How come it does not work in mine?

---

Create a query that accepts "Start" date and "End" date as parameters
In Access 2003/XP/2000/97, I'm trying to run a query based on a date. When the query opens, I would like some type of window prompting for the user to enter a "start" date and an "end" date. How can I do this?

---

Copy an image of a window or screen
In the past, the PRINT SCREEN key did what it says - it sent the current screen of text to your printer. Now, pressing PRINT SCREEN captures an image of your entire screen, also known as a "screen shot" or "screen capture", and copies it to the Clipboard in your computer's memory. You can then paste (CTRL+V) the image into a document, e-mail message, or other file. If you want, you can print the image from the application that you pasted it into.

---

Showing a Please Wait/Progress Macro
There maybe occasions where you will need to display a message to inform your user's that they must wait for an action or process to be completed. You may want to display the text "Database Loading" whilst Access initializes a large database, or display the text "Please Wait" whilst Access runs a long query.

---

Frequently asked questions about Access security warnings
Why does Access display these security warnings? Why does the message "Security Warning: Unsafe expressions are not blocked" appear when I open a file in Access? I have installed the Microsoft Jet service pack. Now when I restart Access and open a file, I see another message that asks me if I want to block unsafe expressions. Why? I enabled sandbox mode, but now it looks as if my application is not working properly. Why? Is enabling sandbox mode the only way to hide this message? What if I don't want to enable sandbox mode? In addition to blocking harmful functions and properties, Access blocks the harmless CurDir and Environ functions in sandbox mode. Access also blocks all the custom properties of ActiveX controls in sandbox mode. Why? I have installed the Jet service pack and clicked Yes when prompted to block unsafe expressions. Now I see the "This file may not be safe" or the "Cannot open file due to security restrictions" warning. Why do these messages still appear? Why does the "This file may not be safe" message appear when I open my file in Access? I see a message that says "Access cannot open the file due to security restrictions. Security settings restrict access to the file because it is not digitally signed." Why? When I open a file, I see the message "This publisher has not been authenticated and therefore could be imitated. Do not trust these credentials." Why? When I open a file, I see the message "This file has been digitally signed by (publisher). The file may not be safe if it contains code that was intended to harm your computer." Why? Often, when I view or change the macro security level, Access prompts me about enabling sandbox mode. Why? My users are complaining about the warnings that appear each time they open one of my databases. Some users cannot even open the database. Why can't I turn off these messages for them? At the High macro security level, I can't open my own databases. At the Medium security level, I can't open my own databases without seeing the "This file not be safe" warning each time I open them. Why? I created this database and wrote some code. But the code does not do anything malicious. Then, why warn me every time? My system is very secure. My computer is not part of a network, and I alone use my databases. Is it okay if I set the macro security level to Low in Access to avoid these messages? Should I set the macro security level in Access to High or Medium? What difference does it make when I open a file? Do I have to buy a certificate to prevent the "This file may not be safe" warning from appearing?

---

Update one table with anothers data?
At work, I've built an Access XP database for keeping track of our computer inventory. Everything works right now, but there's something I'd love to set up. The Current Set Up: We're looking at two of my tables, one for Computers and another for Monitors. In both tables, there is a field labled "Location". Also found in both tables is a field labled "Computer Name". Here's what we do. We install the new PC and monitor. In a form that is linked to the Computer table, we input all of the information about the computer such as the computer name and it's location. Then, in a form linked to the monitor table, we enter information about the monitor such as the name of the computer it's attached to and it's location. (I'm sure you can see where I'm going with this by now.)

---

Troubleshoot exporting in Access
I do not see any entries for MicrosoftOffice Excel 2003 in the Files of type box when I start the export operation. I exported data to an Excel spreadsheet, but now I see green triangles and strings that begin with "#" in the spreadsheet. When I export to Excel, the graph objects are missing in the worksheet. Data is in the wrong column in the resulting Excel worksheet. The worksheet is showing null values instead of date values. I see only "#" in an entire column in my Excel worksheet after the export operation is complete. I exported a form to Excel, but the subforms are not in the worksheet. I don't see any of the pictures that are in the original report int the resulting Excel worksheet. Memo fields are truncated during the export to Excel operation. Report totals are missing in the worksheet to which I exported an Access report. I get the message "Unable to read file" when I export to Excel. I get the message "Too many rows" when I export to Excel. File extensions don't show up beside the file names in the Save As Type dialog box. I'm not able to link my database objects to Microsoft IIS or Microsoft ASP files.

---

Run-time error on form
I have set up a database for monitoring users of our financial system. with the user ID being numeric, and everything has worked fine. Recently the administrators of the database have asked me to alter the database to accept alphanumeric ID's. After changing the data type to text, certain of my forms that use a filter are producing the following error message. "Run-time error '2501' The ApplyFilter action was cancelled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box." I know that the error message is due to the change in data types, because when I delete the alphanumeric data and change the type back, the forms work again.

---

Rolling dates by pressing "+" or "-"
Code to increment or decrement a date by pressing the plus or minus key in a Microsoft Access database. Some commercial programs (Tracker, Quicken, etc) allow the user to press "+" or "-" to increment or decrement a date without the hassle of selecting the day part of the field and entering a new value. This is especially useful in fields where the default date offered could be a day or two different from the date desired. To provide this functionality in Access, attach this Event Procedure to the KeyPress event of your control.

---

How to determine why Access is unable to create an MDE database
I tried to compile my MDB database into an MDE file, but I received the error: "Microsoft Access was unable to create an MDE database." There weren't any other error messages. How can I find out what the problem is?

---

Using queries to get the information you need
At first glance, it appears that splitting information into multiple tables and relationships creates more of a headache than it is worth. People generally like to have all the information they require on one screen (like a spreadsheet, for instance); they do not want to have to know about multiple tables, foreign keys, relationships, and so on. Saved queries address this problem. Queries allow the user to join data from one or more tables, order the data in different ways, calculate new fields, and specify criteria to filter out certain records. The important thing to keep in mind is that a query contains no data it merely reorganizes the data from the table (or tables) on which it is built without changing the "underlying tables" in any way. Once a query is defined and saved, it can be used in exactly the same way as a table. Because of this, it is useful to think of queries as "virtual tables". Indeed, in the majority of DBMSes, saved queries are called views because they allow different users and different applications to have different views of the same data. Learning objectives: create different types of queries; understand how queries can be used to answer questions; develop a naming convention for queries; understand the difference between an 'updatable' and 'non-updatable' recordset. (pdf file, install Acrobat Reader to read this tutorial)

---

Troubleshoot importing and linking
The Get External Data command is disabled. I get an error message saying the wizard is not installed or is disabled. I started the wizard, but it disappeared. What do I do? I don't see Microsoft Excel listed in the Files of Type box. I get an error saying Access cannot import data from my file. The option to append data to an existing table is disabled when importing a spreadsheet. I am importing data from a spreadsheet, but I cannot specify field options for any field other than the first.I get errors when I import or link to a spreadsheet or text file, or the data doesn't get imported (or linked to) properly. Importing a text file is taking a long time. I get errors when I append data to a spreadsheet or text file. Some field values appear truncated in a linked table. I get the Numeric field overflow message when I query a linked table. I can't open or update a linked Microsoft Visual FoxPro or dBASE table. I can't open or update a linked Paradox table. I can't open or update a linked SQL table. I get a key violation message. I imported a file and Access says it created a Tablename$_ImportErrors table. I'm trying to import or link data from my security-enabled database. File extensions don't show up beside the file names in the Files Of Type dialog box. I get an "ODBC - connection to datasourcename failed" message when I open a table or form in a converted database.

---

Customize Not In List error message
My users get a generic error when they type something in a combo box control that is not in the list. I still want to force them to select a choice from the list, but how do I customize this error message to make it more friendly?

---

How to: Change Tables Involved in a One-to-Many Relationship in a DAO Recordset
Dynaset-type Recordset objects can be based on a multiple-table query containing tables with a one-to-many relationship. Learn how to freely change the values on the "one" side of a one-to-many relationship.

---

Create a Query in Microsoft Access Using the LIKE Operator
You can create a query to search data in a table by using the "LIKE" parameter. The "like" syntax allows you to perform wildcard searches against text strings.

---

Multiuser Databases
In order for multiple users to open a database at the same time, all of these users must have "Full Control" permissions on the directory where the file is located. If any one of the users doesn't have "Full Control" permissions on the directory, which includes read, write (create), modify, and delete permissions, then the locking database file (*.LDB), or lack thereof, will interfere with the users trying to open and use the database file, because one or more of these users cannot lock records in the database.

---

What to do when you see -Package- instead of Bitmap Image in your database tables
When you use Datasheet view to add GIF, JPEG, and other common types of image files to an OLE Object (OLE object: An object supporting the OLE protocol for object linking and embedding. An OLE object from an OLE server (for example, a Windows Paint picture or a Microsoft Excel worksheet) can be linked or embedded in a field, form, or report.) field in a table, you see the word "Package" instead of the expected "Bitmap Image."

---

Help with a SQL query? Multiple tables, but all I want to do
This is turning out to be trickier than I thought. Basically, what I'm trying to do is implement a Coldfusion search form on a basic "products" website. The business handles fruit juice products. So for instance, if the user searches for "apple", they'll get a list of all apple products - apple juice concentrate, apple essence, apple extracts etc. The CF portion is easy enough, I just need help with the SQL query. Here's the problem: the products are not all contained in one table. They CAN'T be, because different types of products require different types of specifications. So, I've got each product type in its own table. I want a SQL query that will search the PRODUCT_NAME column of all the tables for values containing the user's search term. I understand I'll need some kind of join for this, but the online resources I've looked at all seem to gloss over joins, which is surprising as they seem essential to database use.

---

Forms, Access Databases and ASP Code
I have been working on this site for a while now, and there are a few things that I will need to do that I am unsure of. The first is that I want the users instead of Registering, to take a survey, at the end of which they will have been registered. To do this I want to keep the Survey Data, and the User Data Separate. I have below the Form Page, and the Submission page, cutting out the name of the database for security reasons. I need the data from the form, on submission to go into 2 table fields, and it's giving me some trouble. Worst of all, I would like to reference the Users ID Number, userid, from the "Users" Table, into the "Survey" Tables field userid. Among the many errors, I'm thinking there is an easier way to do all this than what I have done. Help is greatly appreciated

---

Security: Turn off the Message Bar and run code safely
Learn how to turn off the Message Bar and its security warnings. This course explains how to turn off the Message Bar by "trusting" digital certificates or creating trusted locations. After completing this course you will be able to: 1)Add a digital certificate to your list of trusted publishers. 2)Remove a digital certificate from your list of trusted publishers. 3)Create a trusted location. 4)Run files that contain unsigned code in a trusted location.

---

Turn on or off automatic correction of text and data
Use AutoCorrect to automatically correct text that you frequently mistype, and to automatically replace abbreviations with long names. For example, you can create an entry "esq" for "Martin R. Esquivel." Whenever you type esq followed by a space or punctuation mark, Microsoft Access automatically replaces "esq" with the name Martin R. Esquivel. AutoCorrect works with text in a datasheet and with text in a form in Form View.

---

How to Limit Data in a Microsoft Access Field to Certain Values
Limiting data in a field prevents entry errors. For instance, suppose you are selling towels in three colors - red, blue, and yellow. Limiting the data values to "red," "blue" or "yellow" will prevent people from mistakenly ordering pink towels. These instructions apply to MS Access 97.

---

Using "Top Values" Criteria
Read this article and learn Use of "Top Values" Criteria in MS-Access queries. (video tutorial)

---

Resetting a Microsoft Access 2003 AutoNumber Field
"When you delete the last record or records in a table or in a form that has an AutoNumber field, and then you compact the database, the AutoNumber field does not start at the next higher number when you add new records."

---

Need help on MS Access
I am creating a database using access 2003 for myself. Everything I key in a new record I have to manually type in the name in the first field, it is time consuming if the name is long. So I created a drop down list. I wanted to get the data on my "name" field, so during the list creation I choose the option: "I want the list box to look up the values in a table or query"

---

Display a subtotal for each category within a report
In Access 2003/XP/2000/97, I'm trying to create a report that will display total hours at the end of each category. For instance, in my database I have the following fields titled: "computer" and "total hours". The values in "total hours" represent the number of hours that a technician has worked on a computer. I would like a total of hours for computer #1 displayed before the detail for computer #2 is displayed.

---

Report does not generate a correct Grand Total
I have a crosstab query in MS Access that I have turned into a report. Whenever I try to use a label to calculate the grand total of a field, I will either get the last value entered in that field, a 0 value or "#Error" inside the label.

---

How to Edit Table Columns and Rows in Access 2007
Tables in Access 2007 may be edited in several ways, including datasheets, find-and-replace, forms, queries and the Data Collection feature, which is new for Access 2007. Remember that the term "edit" refers to changing existing data and is not the same process as adding new data. The following steps will show how to edit tables in Access 2007.

---

Filter help with MS Access
How do you get the "filter by form" and "filter" button to appear on your toolbar and how do you access it from the drop down menu's?

---

Backing up a Microsoft Access 2007 Database
One of the rules of protecting your work and data is backing it up. We learned it from the first computer science class and we all do it, whether it's saving your vacation photos on a flash disk or burning a cd with the latest book you wrote. This also applies to databases: using a back-up easily protects against any data loss. Plus, by reverting to an older version you can "undo" actions that the command "undo" is not able to help you with.

---

Perform a stack rank in a query in Access 2003
Is there a way to write a formula in a query that would stack rank a group of numbers in a field? I have a table called "Data Table" with a field named "AHT" that contains whole numbers like 245, 352, and 536.

---

ActiveX component can't create object
When you use Microsoft Access, you receive the following error message: ActiveX component can't create object This article describes some common causes for this error message and offers methods that you can use to resolve the issue.

---