How can I save a database in Access 97 (version 8.0) to Access 95 (version 7.0)?

You can't save a database backwards to a previous format in Access, as you can with documents in Microsoft Word or Microsoft Excel.  There is no feature for converting a database as a whole to an earlier version of Access.  However, you can export the tables to a file format that you can then import into Access 95.  You can use the following steps to do this:

1. In the Database window, right-click the table you want to export, and choose Save As/Export from the shortcut menu.

2. In the Save As dialog box which appears, leave the To an External File or Database option selected, and click OK.

3. In the next dialog box that appears, under Save as Type, change the type to a format that your earlier version of Access can import, such as Text, Excel, FoxPro or dBASE IV.

4. Choose a destination drive and folder in the upper portion of the dialog box, enter a name under File Name, and click Export to export table.

5. Repeat steps 1 through 4 for each table that you need to import into the earlier version of Access.

6. Launch the earlier version of Access, and use the File/Import procedure appropriate to that version of Access it import tables.  After importing the tables into the earlier version of Access, you'll need to recreate manually any queries, forms, reports macros or modules needed.

Tip:   If the table you are bringing into the earlier version of Access contains memo fields or OLE Object fields, FoxPro 2.6 is a recommended choice as the export format, because the complete contents of the memo fields and the OLE Object fields will be retained, and all but the earliest version of Access can import FoxPro 2.6 files.  (Excel does not handle OLE Object fields, and it will not let you store more than 255 characters in a cell, so any memo fields will lose all characters after the 255th character if exported to Excel.)  On the other hand, if the table contains currency fields but no memo fields or OLE Object fields, Excel is a recommended choice because the formatting of the currency fields will be retained. 

When I open my form or report, I see "#Name?" in some controls.  What happened to my data?

"#Name?" is the error message that appears in a control when it can't find the data it is supposed to display.  Controls are the elements of a form or report that can display data.  Each control has many properties that define how it works.  The Control Source property defines the source of the data that the control displays.  If this property is set to a source that Access can't find or that doesn't exist, the "#Name?" error message appears instead of the missing data.

Tip:   The idea behind properties may be confusing at first, but it is actually quite simple.  Properties modify controls the same way that adjectives modify the meaning of a noun.  An adjective can't change the basic definition of the noun, but it does provide more information.  Properties can't change the kind of control in your form or report, but they can provide clarifying information, such as what data the control displays.

Tip:   You can show either some or all of the properties for a control in the Properties window.  To show all of the properties, click the All tab in the window.  To show some of the properties, click the appropriate tab (Format, Data, Event or Other).

Tip:   You can also change the Control Source property for a text box by clicking once within the text box, and typing the new entry for the property.

Why do I get the message "You can't add or change a record because a related record is required in table "tablename" when I try to add a record using my form?

When you set up a relationship between the table the form is based on and another table, you chose to enforce referential integrity.  Relationships between tables indicate how records in the two tables relate.  For example, you could set a one-to-many relationship between the Employee Id field in your Employees table and the Sales Representatives field in your Invoices table.  When you enforce referential integrity, Access makes sure that all new and edited records in the tables keep the type of relationship you set.  For example, in this one-to-many relationship, you can enter ID numbers for the Sales Representatives field only if they already exist in the Employee ID field in the Employees table.  When you try to save a record, Access checks to make sure that the record doesn't violate referential integrity.  For example, after you enter a record in your Invoices table, Access checks that the entry in the Sales Representatives field already exists in the Employees table.  If it doesn't Access won't save the record and displays the message you saw.

Enforcing referential integrity protects you against incorrect entries.  If you mistype an employee's ID in the Sales Representative field, Access ss you.  Obviously, your sales representative can't be someone who isn't already an employee.  This lets you fix the mistake immediately instead of discovering it much later.

In my form, how can I give my fields a raised appearance as if they are on a button, not a flat appearance as if they have been typed on a sheet?

You can do this by adding a special effect of your choice to the field's appearance.  Open the form in Design view, and right-click the field.  From the shortcut menu that appears choose Special Effect.  A submenu will appear as shown here, from which you can choose the desired effect.  You have a choice of flat, raised, sunken, etched, shadowed or chiselled for the possible special effects.

How do I change a field in a form to a list box or a combo box?  

Open the form in Design view, right-click the field, and choose Change To from the shortcut menu.  From the next menu that appears, choose List Box or Combo Box, as desired.  Depending on the size of the entries in the original field, you may want to resize the control to add space for the down arrow at the right of a combo box or the scroll bar in a list box.

Tip:   Once you've changed the field, you will need to modify the Row Source Type and Row Source properties for the list box or combo box, depending on what you want the box to display.  If you're not sure about how to do this, you may be better off adding the list or combo box as a new field and letting the Control Wizards automate the process.  You can delete the existing field, and make sure that the Control Wizards are turned on in the Toolbox (the Control Wizards button should appear depressed).  Next, click the List Box tool in the toolbox (if you want a list box) or click the Combo Box tool in the Toolbox (if you want a combo box).  Finally, click in the form where you want to place the list or combo box.  The wizard will ask you a series of questions.  Depending on your answers, the wizard will fill in the Row Source and Row Source Type properties for you.

How can I break the link to an attached table?    

In the Database window, select the attached table.  Open the Edit menu and choose Delete or press the DEL key.  In the dialog box that appears asking for confirmation, click Yes.  This deletes the link and removes the name of the attached table from the list of tables in the Database Window.  Note that when you delete an attached table, you are deleting only the information that Access uses to open that table.  You are not deleting the table itself.  You can reattach the same table again.

Under the Database Utilities option of the Tools menu there's an option for Make MDE file.  What does this do to my database?     

Saving a database as an MDE file results in a copy of the database that does not allow changes to forms, reports or any Visual Basic for Applications (VBA) code stored in that database.  (You can make changes to tables, queries and macros in a database that has been saved as an MDE file).  If your database contains VBA code, saving it as an MDE file complies all modules removes all editable source code, and then compacts the database.  Any VBA code will continue to run, but it cannot be viewed or edited.  In addition to preventing changes by unauthorized users, saving a database as an MDE file has performance benefits.  The size of your database will be reduced due to the removal of the code, and memory usage is optimised improving performance.

Caution:   If you save a database as an MDE file, be sure to maintain a copy of the original database (.MDB) file in a secure location.  You will need it if you want to make any changes to the forms, reports or code that are stored in the database.

How can I type something into a text box on a form and use that value to filter my query?  

There is a technique called query-by-form that you can use to accomplish this.  You enter an expression in the Criteria row of the query that refers to the text box on the form, and you add a Search button to the form that, when clicked, runs the query. 

You can use the following steps to implement query-by-form:

• Create a form with text boxes you want to use to supply the desired criteria to the query.  You will need one text box for each cell in the query that you want to provide criteria for.  As an example, if you wanted to fill in the City and State fields of a query based on entries in a form, you would add two text boxes to the form.
• Turn on the Control Wizards in the toolbox, if they are not already on.
• Add a command button to the form.  The Command Button Wizard dialog box will appear.  Under Categories, choose Miscellaneous.  Under Actions, choose Run Query. The click Next.
• In the next dialog box, choose your query that will accept the values entered into the form, then click Next.
• In the next dialog box, click Text and enter an appropriate caption for the button, such as "Search".  Then click Finish to add the button to the form.
• Save the form.
• Open the query in Design view.  In the Criteria row for each field that has a corresponding text box on the form, you will need to enter an expression like this Forms![Form Name]![Control Name] OR
• Forms![Form Name]![Control Name] Is Null
• Where Form Name is the name of your form, and Control Name is thename assigned to the text control.  For example, if you named the form "Query People" and the form contained a text box named "AskLastname," you would have an expression like this one in the Criteria row of the Last Name field of the query grid:
• [Forms]![Query People]![AskLastname] OR [Forms]![Query People]![AskLastname] Is Null

• After saving the query, you can open the form, enter criteria is one or more of the text boxes, and click the button to run the query using the criteria supplied.

How can I create a startup form, or a form that initially appears every time I open a database?

In earlier versions of Access, this was commonly done with an Autoexec macro containing an Open Form argument that opened the desired form.  You can still do it this way, but Access 97 provides an easier technique with more options.  With the Database window active, open the Tools menu, and choose Startup.  You will see the Startup dialog box, shown here.

Click the down arrow at the right of the Display Form list box, and choose the form by name that you want to open when the database is opened.  You can use the other options in this dialog box to hide the Database window, to provide a title for the Application title bar, to display or hide the full menus and built-in toolbars, to name any custom menus and menu bars used by the database, and to specify an icon that appears in place of the default Access icon in the Application title bar and in the Windows Taskbar.

How can I bring in data from a data source other than Access?

You can use the following steps to bring in data from an external data source:

• Open the database where you want to store the data, or create a new database if you want the data stored in that database.
• Open the File menu and choose Get External Data.
• From the next menu that appears, choose Import.
• In the Import dialog box that appears, choose the type of file under the Files of Type box.
• Enter a name and path for the file under File Name.  (Alternately, you can use the Drive and Folder icons at the  of the dialog box to navigate to a folder where the file is stored, and select it).
• Click the Import to bring the data into your Access database.
• Depending on the type of data you are importing, Access may launch an Import Wizard to help you complete the process.  If that happens, answer all of the questions in the Wizard.