FAQs - Microsoft Access


 
What format should my database be saved in to allow different versions of Microsoft Access to open the database?

MS Access is not backwards compatible. To share tables between different versions of MS Access, you can link the tables between two different databases. However, only the tables in the converted database would be available.


What is the maximum size of a database that can be opened in Microsoft Access?

1 Gigabyte


I have created a custom database with a custom menu in Microsoft Access, and I am trying to recover to a full menu. How do I perform this task?

When you create a custom command bar that uses commands in the Built-In Menus category on the Commands tab of the Customize dialog box, Microsoft Access creates a pointer reference to the original built-in menu. Therefore, any modification you make to the Customs Command bar actually modifies the built-in menu bar. The solution is to split the database. Create a copy of the database, putting tables in one database and the query, forms, etc. in the other, then compile the query, forms, etc. When you need to make changes, you must make changes to the original databases, make a copy of it, then recompile the database.


Access is returning incorrect results for the specified years 1900 through 1915.

Access interprets years differently than in prior versions. For example #01/01/15# returns January 1, 1915 in previous versions. Access will return January 1, 2015. Modify your date specification to be #01/01/1915#.


I am designing an Access database form. How can I get my next control in the form to be automatically selected?

By default, Access waits until you press Enter before it selects the Next control on your form. However, this is a wasted keystroke you can eliminate by setting each control's Auto Tab property to Yes. This Property also requires that you set an input mask. Once the data you're entering satisfies the input mask, the Auto Tab property Automatically selects the form's next control. We don't recommend you Use this feature unless you can apply it consistently; otherwise you'll just confuse your users, who won't know when to press Enter.


I am using the Switchboard Manager in MS Access. I am getting an error message "There was an error executing this command."

The Switchboard manager in MS Access offers only limited information on errors that occur within the database. Execute the selection directly from the database window to get more detailed information on the error.


I have converted my MS Access database to a current conversion. I am getting conversion errors.

Design specs have changed in new releases of MS Access. Visit the on-line help option for "conversion and compatibility" that explains different portions of Access databases that will have difficulty converting. Some reprogramming may be required.


In MS Access, the long and short date option does not show 4 digit years. How do I achieve the mm/dd/yyyy format?

In the date field properties of the table, form, or properties manually input the format you would like. It is not required to choose one of the date formats in the drop down box. Example would be mm/dd/yyyy.


How can I open an MS Access database that has been converted to a current version?

MS Access is not backwards compatible. A workaround to share tables between different versions of MS Access would be to link the tables between two different databases. All objects in the database would not be available.


Suppose I have a database of sales broken out by state. How would I exclude a certain state or states from my query?

Finding records that match criteria is a common database function. To accomplish this specific query you can use the Not operator. For instance, if you want to find all your customers outside California, you might use the expression Not "CA" as your criterion.


What is the size limitation of an Access database?

Size limitation does not pertain to number of records in a table within a database. Forms, reports, macros, and modules also contribute to database size, but is rather minimal compared to tables containing records. It is recommended that if large amounts of data will be maintained or constantly deleted and new data imported, put your tables in another database and link the tables back into the main database. The utilities for database compression and repair will run much more quickly. In Access 2.0 databases the byte size limitation is 1 gigabyte. In general, use the 1gb rule and suggestions here to maintain performance of the database. See also "How Replication Increases Size of Database.".


Would you like to easily automate your MS Access database through menu driven selections?

This can be accomplished by creating a form with customized buttons that point to macros. An easier way is to use MS Access' built in function called "Switchboard Manager". This can be located in Tools/Add-Ins.


I am having trouble aligning labels properly through the Reports option. Is there any easier way?

Link the database to MS Word and create the labels. A DDE link is established within MS Word and a copy of MS Access actually launches with the database open. MS Word can then link directly to the table or query that the labels are being generating from.


I am designing an Access database form. How can I get my next control in the form to be automatically selected?

By default, Access waits until you press Enter before it selects the next control on your form. However, this is a wasted keystroke you can eliminate by setting each control's Auto Tab property to Yes. This property also requires that you set an input mask. Once the data you're entering satisfies the input mask, the Auto Tab property Automatically selects the form's next control. We don't recommend you use this feature unless you can apply it consistently; otherwise you'll just confuse your users, who won't know when to press Enter.


When I assigned a primary key to my table in Microsoft Access, I was expecting my data to be sorted. Am I using the correct procedure?

Some developers occasionally rely on indexes to sort their data, but shouldn't. That's because an index is an internal Jet operation that relies on internal rules to speed sorting, which is not the same as actually performing the sort. Records are not stored differently or changed. The sort is not performed until an action is being taken against the data like a query. This is not the same as simply opening a table in its Datasheet View. Although an index often appears to sort data, those internal rules often conflict with normal sorting practices and can have unexpected results. Sorting can be accomplished through the use of a query where ascending or descending can be specified against any one of the fields chosen from the table.


A table that has a field formatted as autonumber somehow has resulted in duplicate numbers. How can this happen?

When an autonumber format is selected, Access will assign a number to each new record that is entered to the table. The initial number default starts at 1, but utilizing the following steps can change this default. Follow the example 1. Create a table of addresses using field names ID, Fname, Lname, Address1, Address2, City, State, and Zip. Be sure to format ID as autonumber and all other fields as Text (or some other format that will accept your data. Save the table as TableA. 2. Add records to TableA. Watch Access automatically assign an incremental number for the ID field starting at 1. 3. Now create a second table using the same fields as above. This time format the ID field as number. Save this table as TableB 4. Add records to the table. This time you will have to assign an ID to each record. Start the first record with ID = 100, and increment from there up. 5. Create an Append query to populate the records of TableB to TableA. As long as you named the fields the same between the two tables, Access will automatically link the field names when you choose Append Query. See the QBE grid. Run the query. 6. Now go back to TableA and add another record. The field ID formatted as autonumber now starts at the next highest value of all the records within TableA. This is the only known way to defeat the autonumber procedure. To answer the question above, add records to TableB duplicating the ID numbers from TableA. Re-run the append query, then view TableA again. The ID field will be duplicated.

More Help