Microsoft Access Help

>> I am new to Microsoft Access and want to create an application, where do I begin?

Be prepared to spend a lot of time on the application. Microsoft Access distinguishes itself from other office applications in that its breadth of capabilities is much deeper than any other Office application. You can actually build a very solid, complex application with Microsoft Access. Spend time getting to understand the basic objects of Microsoft Access: Tables, Queries, Forms, Reports. Also take time to learn about good database and table design.

Back to Top

>> Should I use a SQL database or a Microsoft Access Database for my needs?

When deciding between SQL and Microsoft Access, you should ask yourself two questions. First of all, how many users will be entering data into the database at one time? If your answer is 10 or more users, you should probably consider SQL Server instead of Microsoft Access. Also, how much data do you plan on storing? If the amount of data in any one table exceeds 200,000 records, would require three or more tables, or have 100,000 or more records, you should consider SQL Server. When you are first designing a database, it may be hard to anticipate how many users and/or how many records will ultimately be included in the database. Microsoft Access is a great database platform because is allows you start out small and build upon. If your database becomes too big for Microsoft Access' capabilities, it is easy to upgrade from an Access database to SQL Server.

Back to Top

>> I am using a Microsoft Access database and have created a report that I would like to send to someone. However, I do not want to send them the entire database. Is there a way to just send the report?

Use the .snp (snapshot) file format to save a Microsoft Access report to a file that can be viewed by others. The .snp file shows the entire report in a view similar to the page preview view in Microsoft Access. From there, the user can print the report to their printer. The user cannot rerun or update the report.

To create a snapshot file from a report, first open the report in Print Preview view, then choose Export from the file menu and change the file type to .snp format. A single .snp file is created for each report. These files can then be shared with others without sharing the entire database (.mdb) file. The only thing to keep in mind is that the user needs the snapshot viewer installed on their PC to view a .snp file. This viewer is automatically installed if they have Microsoft Access on their computer, or they can download the snapshot viewer. If this link doesn't work, you can simply search for snapshot viewer at: www.microsoft.com.

Back to Top

>> I have a continuous Access form and I would like to change the format of an individual record based on values in that record. For example, I would like to bold a value if it is greater than a certain amount. Is there a way to do this?

You can do this as long as you are using Microsoft Access 2002 or later. With Access 2002, the Conditional Formatting tool allows you change some of the formats of a control based on its value or based on the value of other controls for the same record. With Access 2003, this feature was enhanced, allowing a control to be enabled/disabled based on another control's value. This is a very powerful user interface tool. You will find this option when you are in the design view of a form, under the format menu.

Back to Top

>> I am unable to receive a Microsoft Access database file (.mdb) in my Outlook e-mail account. Microsoft Outlook blocks the e-mail file because it could be potentially harmful (i.e. carrying a virus). How can I get around this?

There are several options to "unblock" this type of file from Outlook. Have the user send you the file via a compressed folder (i.e. zip file). Using a compressed folder also has the added benefit of shrinking the file size. Microsoft Outlook does not block .zip files from incoming e-mail.

Back to Top

>> Why has my Microsoft Access Database file (.mdb) size has grown so large? How do I keep it small?

One of the less published issues with a Microsoft Access Database file is that it does not automatically reclaim unused file space. To illustrate this, let's say you have a 100K sized .mdb file containing only 2 tables. One table takes up 80K of the database, the other takes up the 20K. You then delete all the records (or even the entire table) of the 80K table, you will notice the file size will remain 100K. This is because the 80K of unused space needs to be cleaned up. To clean it up, you need to perform a compact and repair on the database. Simply choose Compact and Repair on the Database Utilities submenu found on the Tools menu. You can also set the database to compact itself each time the database is closed (as long as no other user has it open) by choosing the Compact on Close checkbox in the Tools->Options menu.

Back to Top

>> What is the best database design for my situation?

Database Design is one of the most crucial aspects of database development. Only through good Database Design does an application reach its goals. While there is no "magic" one-size-fits-all database design, it is worth taking the time to explore every possible opportunity. A poor database design will continually hamper an application, while a good database design will ultimately unleash the power of your database. If you are new to database design, we highly recommend that you work with experienced professionals at the beginning. Trying to fix a poorly designed database after it is in production is very time consuming and costly.

Back to Top

>> What are some suggestions on creating user-friendly Access Forms?

Another important step in creating an Access Database is to create a well designed user interface. This is done through the use of Access Forms. Spend some time thinking about applications you have used in the past. What were the good things about using the forms? What were the bad things about using the forms? These three suggestions may help you create successful Access Forms:

1. Keep the interface clean. Be sure to size and line up controls with each other; this will give your forms a professional appearance.
2. Avoid using a series of forms to get one task accomplished. Use up the screen real estate to include all the information with the least number of forms possible.
3. Use control tool tips, status bar messages, and other visual clues to help the user learn to use the form quickly.

Back to Top

>> My Access Queries are not returning the information I expect or are returning no information at all, how can I find out what is wrong?

When an Access Query does not return the information you expect, follow these simple steps to troubleshoot. First, remove any criteria you have entered. Remove each individual criterion and re-run the query until the data you expected to see starts to show up. If it does, than you know there is something wrong with the criterion you just removed. If this does not fix your Access Query or you Query does not specify any criteria, follow the same step-by-step procedure by deleting one table from your query at a time. After each delete, run the query. When the query starts to return the results you need, analyze the join from the table/query you just deleted and make sure it is the right type.

Back to Top