I am running into the dilema not beeing able to use my current autonumberas
my mainID, primary key....
I need to use a six digit number which has to inlcude the last two digitsof
the current year the record is added, 090001, 090002….
The dBase already contains 10,000+ records in a dz tbl's and need to keep
the records.
Thanks for your help!!
General Comment:
You may wish to keep your current key as the primary key and continue
to use it for joins. Generally, the primary key should not contain
information, but be used strictly as a unique identifier. You would
use your new key only in the human / machine interface (i.e. forms and
reports). This minimizes the changes as well as staying more
theoretically correct.
Below are the instructions assuming you want to go ahead and delete
the current key. If you will retain the current key, you need not do
steps 6 - 8 and step 5 is simplified to changing only the control
sources and grouping for your forms and reports. (SQL should still be
checked, but if the key is used only for joins, it will not have to be
changed.)
1. Define NewKey in the primary table
2. Run an update query in the primary table, defining a new field:
newKey:Right(DatePart("yyyy",DateValue(nz(DateAdded,#1/1/2000#))),2) &
right(Format([OldKey],"0000"),4)
"DateValue(nz(Left([start],9),#1/1/2000#))" above translates string
text date into internal date format and assigns Jan 1,2000 as the
default date for any row without a DateAdded.
You can simplify this to:
Right(DatePart("yyyy",DateAdded),2) & right(Format(OldKey,"0000"),4)
if DateAdded is already in date/time internal format and the field is
never null.
The rest of the expression extracts the 2 digit year and appends the
last 4 digits of the old key.
3. Define NewKey in any tables that reference OldKey.
4. Run update queries matching the primary table to other tables
referencing OldKey, copying the value from the primary table into the
referencing tables.
5. Change all queries using OldKey to use NewKey. If you have SQL in
code, macros, reports, or forms that use OldKey, you will have to
change these as well. Change any controlSource or rowSource in forms
or reports that use Oldkey to use NewKey. Also remember to change the
grouping variable in the forms and reports to newKey
6. Update the tables in design mode to which you added NewKey to make
NewKey the Primary Key.
7. Change any relationships needed in the Relationships window.
8. Delete the OldKey field from all tables.
By the way, an alternative to changing all references in the queries,
sql, etc. from OldKey to NewKey is to delete OldKey and rename newKey
to OldKey. This is simpler, but its success may depend on which
version of Access you have and its settings. If you want to try it,
be sure to turn off the Perform Name Autocorrect option in the General
tab of the Options dialog (assuming this is in your version of Access.
