Microsoft Access Developer

When you are doing initial testing or if you make mistakes entering data and delete a row, the AutoNumber field keeps incrementing from the last number used. You may wish to start from 1 again (perhaps with existing data or perhaps after deleting initial test data).

This is very simply done as follows:

☛ Make a copy of the table (just in case)

☛ Remove all relationships between this table and any others

☛ If the AutoNumber field is a primary key, click on the primary key icon to undesignate it as a key

☛ Delete the row for this field

☛ Insert the row again and recreate the field as an AutoNumber field

☛ Redesignate it as a primary key, if appropriate

☛ Look at the table in design view - it should now be renumbering from 1 again.

☛ Reinstate the table relationships.

Some developers occasionally rely on indexes to sort their data, but shouldn’t. Thai’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 he specified against any one of the fields chosen from the table.

When you open an Access database in a newer version of the software, Access will ask you if you want to convert it and then, if you reply 'yes' will do the conversion. When converting from Access 97 to Access 2000, it is recommended that you first compile all the code (see Debug-->Compile all Modules), then repair and compact it (see Tools-->Database Utilities). If you don't do this, you may get a message saying that the database is corrupt. This not does seem to be necessary when converting to XP.

If you reply 'No' to the conversion, then the database can be opened and data amended (I think), but no design changes can be made.

There is also information regarding conversions to/from different versions in Access Help. Use the keywords

convert, access

to find the relevant pages.

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.

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.

You have probably got the field defined as a 'check box' in the table definition. If this field is actually destined for a form, then a check box may be appropriate. However, it can look odd in the table layout or on a report. If you want to change the display, simply look at the property box for the field on the table, form or report and change the display control from 'check box' to 'text'.

You may also like to think whether a Yes/No box really is the most appropriate data type to use. The user has to enter either 'Yes' or 'No' for the field. A 'Yes' (or tick in a check box) on a form for 'Smoker' may make sense, but seems (to me, at least) to be decidedly odd for the 'Sex' or 'member/class' fields for the Chelmer leisure Scenario (though it does save space). You may like to experiment with using a Look-Up Wizard for the data type here, and setting just the two values 'Male' and 'Female' or 'Member' and 'Class' for the list box that the Wizard will create. 'M' or 'F' etc would save space.

There are two common reasons for this:

1 - Existing data violates integrity (error message: "Microsoft Access can't create this relationship and enforce referential integrity" - this message is then followed by a nice clear example of what the problem might be).

The most likely reason that either or both of your tables already have data in them but that the data violates the integrity rule.

Example A: You have data in your membership table but not yet in your category table. The tables will be joined via the category number. As referential integrity requires that all category numbers in the membership table are also in the category table, you must now set data in your category table before creating the relationship.

Example B: You have data in both the membership and the category tables. The likely reason is that you have a category number in your membership table that is not in the category table. Correct your data and try again.

It is safest to set up all validations and rules before entering any data.

2 - Key data types do not match (error message: "relationship should be on the same number of fields with the same data type")

The relationship joins two keys - one in each table. These keys should be of the same data type and size. If the primary key of one table is an Autonumber field (LongInteger), then the corresponding foreign key in the other table should be a Number data type, also LongInteger. If one key is a text field, then so should the other one (and of the same size).