Re: Unique Index


"RoyVidar" <roy_vidarNOSPAM@xxxxxxxx> wrote in message
news:mn.7a3f7d68e27fbf60.57374@xxxxxxxxxxx
"paii, Ron" <paii@xxxxxxxxxxxxxx> wrote in message
<WvudnRG6w5MpJn3ZnZ2dnUVZ_oednZ2d@xxxxxxxxxxx>:
"Rick Brandt" <rickbrandt2@xxxxxxxxxxx> wrote in message
news:rL2Eg.12409$gY6.9784@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
paii, Ron wrote:
I have a table listing drawing numbers for jobs. It's primary key
combines Job and numeric part of the drawing number. The structure
allows the number part to repeat for each job.
Job Dwg
6692 001
6692 002
6692 003
6721 001
6721 002

I want add a autonumber primary key. Is there a way in Access97/Jet
to create an index on job / dwg to allow the drawing field to be
unique for a job? I would like to do it at the table level instead
of the form.

No, not if you want the Dwg number to start over per Job. You could
assign that number in the code event of a form though. I would use
the BeforeUpdate event.

If Me.NewRecord Then
Me.Dwg = Nz(DMax("Dwg", "TableName", "Job = " & Me!Job), 0) + 1
End If

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



That is what I thought, using the Access GUI. I was hoping for a SQL
command that would create the index.

Thank for your reply.

I think you can create the index through DDL, but that's the means of
ensuring data integrity, not how to populate it. So, if the two fields
are the current compound primary key, and you wish those to become a
compound index and add an autonumber field to the table to be primary
key, I think something like the following air code might perhaps be of
assistance

dim db as dao.database
set db = dbengine(0)(0)
db.execute "DROP INDEX PrimaryKey ON table1"
' PrimaryKey - the name of the primary key index
db.execute "ALTER TABLE table1 ADD COLUMN id Counter"
db.execute "CREATE INDEX PrimaryKey ON table1 (id) WITH PRIMARY"
db.execute "CREATE UNIQUE INDEX idxJobDwg ON table1 (Job, Dwg) " & _
"WITH DISALLOW NULL"
set db = nothing

--
Roy-Vidar


I will give that code a try. Will the new key show in the GUI or will I need
to document.


.

Relevant Pages

  • Re: Unique Index
    ... Job Dwg ... I want add a autonumber primary key. ... ' PrimaryKey - the name of the primary key index ...
    (comp.databases.ms-access)
  • Re: Is AutoIncrement recommended or not?
    ... > Primarykey field ... I am not aware of issues related to autonumbering, ... What happens with related records in other tables has nothing to do with ... My opinion is that Primary Key shouldn't have any other meaning rather than ...
    (microsoft.public.vb.database.ado)
  • Re: Is AutoIncrement recommended or not?
    ... > to be PrimaryKey. ... And Primary Key is just a special case of Unique Index. ... I thought for a column to become a Foreign Key in another table it had ...
    (microsoft.public.vb.database.ado)
  • Re: Exposing a private instance field in SOAP
    ... >> The PrimaryKey class internally stores the data as either a int or a Guid ... >> If we simply used int to represent a primary key, ... >> through SOAP. ...
    (microsoft.public.dotnet.framework.webservices)
  • Re: Delete primary key
    ... Chances are it's named "PrimaryKey" ... Dim tdf As DAO.TableDef ... Allen Browne - Microsoft MVP. ... >>>> ALTER tblBenchmarks DROP PRIMARY KEY, ...
    (microsoft.public.access.modulesdaovba)