Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Writing SQL code

Discussion in 'Information Technology' started by blacklf, Jul 28, 2006.

Tags:
  1. blacklf

    blacklf
    Expand Collapse
    Guest

    I am trying to automate a task for a user in MS Office Access 2003. I have
    everything figured out except how to write an SQL statement to temporarily
    disable a primary key. Does anyone know how to do this? I've read so much
    material searching for the answer that my eyes are crossing!
     
  2. Loading...

    Similar Threads Forum Date
    Flowery Writing Questions and Answers Nov 3, 2016
    Literature Has Anyone Read: "The Sikh Religion: Its Gurus, Sacred Writings And Authors" Language, Arts & Culture Oct 22, 2016
    The writings of Hesse Blogs Oct 16, 2015
    Devanagari as a Writing System for Punjabi: Plus or Minus for Punjabi? Punjab, Punjabi, Punjabiyat Jul 30, 2013
    Rumal with Shahmukhi writing okay for the Gurdwara? New to Sikhism Dec 21, 2012

  3. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello "blacklf".

    "blacklf" wrote:
    > I am trying to automate a task for a user in MS Office Access 2003.
    > I have everything figured out except how to write an SQL statement
    > to temporarily disable a primary key. Does anyone know how to do
    > this? I've read so much material searching for the answer that my
    > eyes are crossing!


    You can't temporarily disable a primary key, you can delete it
    (Drop Index [indexname] On [tablename])
    and create a new one later
    (Create Index [indexname] On [tablename] ([fieldname]) With Primary)
    But:
    Why would you delete a primary key? To be able to violate the
    uniqueness of the values? You won't be able to create a primary
    key afterwards!

    --
    Regards,
    Wolfgang
     
  4. pvdg42

    pvdg42
    Expand Collapse
    Guest

    "blacklf" <blacklf@discussions.microsoft.com> wrote in message
    news:19E91829-C3FB-4474-BB47-B63DE8AB1174@microsoft.com...
    >I am trying to automate a task for a user in MS Office Access 2003. I have
    > everything figured out except how to write an SQL statement to temporarily
    > disable a primary key. Does anyone know how to do this? I've read so
    > much
    > material searching for the answer that my eyes are crossing!


    Here's a reference for you,

    http://office.microsoft.com/en-us/assistance/HP010322071033.aspx
     
  5. blacklf

    blacklf
    Expand Collapse
    Guest

    Wolfgang,

    Thanks for your reply. My user has a database that is used in 1 year
    cycles. She wants to be able to clear the data and reset her autonumbers to
    start over with 1 for the new year. I am trying to write the code so she can
    accomplish this "with the push of a button."

    I tried the code you sent me last week. I get a message that the index
    cannot be dropped because it is used in a relationship. I guess what I
    really need is a way to use code to remove a relationship.


    "Wolfgang Kais" wrote:

    > Hello "blacklf".
    >
    > "blacklf" wrote:
    > > I am trying to automate a task for a user in MS Office Access 2003.
    > > I have everything figured out except how to write an SQL statement
    > > to temporarily disable a primary key. Does anyone know how to do
    > > this? I've read so much material searching for the answer that my
    > > eyes are crossing!

    >
    > You can't temporarily disable a primary key, you can delete it
    > (Drop Index [indexname] On [tablename])
    > and create a new one later
    > (Create Index [indexname] On [tablename] ([fieldname]) With Primary)
    > But:
    > Why would you delete a primary key? To be able to violate the
    > uniqueness of the values? You won't be able to create a primary
    > key afterwards!
    >
    > --
    > Regards,
    > Wolfgang
    >
    >
    >
     
  6. blacklf

    blacklf
    Expand Collapse
    Guest

    This is a good reference. Thanks. Will keep looking. Wolfgang brought to
    my attention that what I really need is a way to delete a relationship with
    code. When I try to drop the index I get a message that it can't be done
    because the index is used in a relationship.

    "pvdg42" wrote:

    >
    > "blacklf" <blacklf@discussions.microsoft.com> wrote in message
    > news:19E91829-C3FB-4474-BB47-B63DE8AB1174@microsoft.com...
    > >I am trying to automate a task for a user in MS Office Access 2003. I have
    > > everything figured out except how to write an SQL statement to temporarily
    > > disable a primary key. Does anyone know how to do this? I've read so
    > > much
    > > material searching for the answer that my eyes are crossing!

    >
    > Here's a reference for you,
    >
    > http://office.microsoft.com/en-us/assistance/HP010322071033.aspx
    >
    >
    >
     
  7. pvdg42

    pvdg42
    Expand Collapse
    Guest

    "blacklf" <blacklf@discussions.microsoft.com> wrote in message
    news:7A8FB376-3829-4632-842E-626183BCFAF6@microsoft.com...
    > This is a good reference. Thanks. Will keep looking. Wolfgang brought
    > to
    > my attention that what I really need is a way to delete a relationship
    > with
    > code. When I try to drop the index I get a message that it can't be done
    > because the index is used in a relationship.
    >

    Actually, I was referring to the "DROP CONSTRAINT" part of the reference.
    You may have to drop constraints, do whatever you want to do, then add the
    constraints back.
     
  8. Bill Edwards

    Bill Edwards
    Expand Collapse
    Guest

    This is a bad use of autonumber.
    Autonumber is meant to provide only a unique key value for a record. It is
    not guaranteed to be sequential. When used properly, your users should not
    care what the value of the autonumber field is -- in fact they should
    probably not even see it.

    Assuming you have created relationships and cascade delete is not enabled,
    to reset the autonumber:
    Delete all the records from the child tables
    Delete all the records from the parent table
    Compact and repair the database.

    A better solution would to add a date field and a sequence number field to
    the table. Then write code to increment the sequence number for a given
    year.


    "blacklf" <blacklf@discussions.microsoft.com> wrote in message
    news:C87FC2F1-73E4-453E-B03C-C05F1B236AFA@microsoft.com...
    > Wolfgang,
    >
    > Thanks for your reply. My user has a database that is used in 1 year
    > cycles. She wants to be able to clear the data and reset her autonumbers
    > to
    > start over with 1 for the new year. I am trying to write the code so she
    > can
    > accomplish this "with the push of a button."
    >
    > I tried the code you sent me last week. I get a message that the index
    > cannot be dropped because it is used in a relationship. I guess what I
    > really need is a way to use code to remove a relationship.
    >
    >
    > "Wolfgang Kais" wrote:
    >
    >> Hello "blacklf".
    >>
    >> "blacklf" wrote:
    >> > I am trying to automate a task for a user in MS Office Access 2003.
    >> > I have everything figured out except how to write an SQL statement
    >> > to temporarily disable a primary key. Does anyone know how to do
    >> > this? I've read so much material searching for the answer that my
    >> > eyes are crossing!

    >>
    >> You can't temporarily disable a primary key, you can delete it
    >> (Drop Index [indexname] On [tablename])
    >> and create a new one later
    >> (Create Index [indexname] On [tablename] ([fieldname]) With Primary)
    >> But:
    >> Why would you delete a primary key? To be able to violate the
    >> uniqueness of the values? You won't be able to create a primary
    >> key afterwards!
    >>
    >> --
    >> Regards,
    >> Wolfgang
    >>
    >>
    >>
     
  9. blacklf

    blacklf
    Expand Collapse
    Guest

    Thanks, Bill. Point taken. I didn't create the database, but the user is
    asking me to make it work the way she wants it to. Maybe I can talk her into
    an alternate solution.

    "Bill Edwards" wrote:

    > This is a bad use of autonumber.
    > Autonumber is meant to provide only a unique key value for a record. It is
    > not guaranteed to be sequential. When used properly, your users should not
    > care what the value of the autonumber field is -- in fact they should
    > probably not even see it.
    >
    > Assuming you have created relationships and cascade delete is not enabled,
    > to reset the autonumber:
    > Delete all the records from the child tables
    > Delete all the records from the parent table
    > Compact and repair the database.
    >
    > A better solution would to add a date field and a sequence number field to
    > the table. Then write code to increment the sequence number for a given
    > year.
    >
    >
    > "blacklf" <blacklf@discussions.microsoft.com> wrote in message
    > news:C87FC2F1-73E4-453E-B03C-C05F1B236AFA@microsoft.com...
    > > Wolfgang,
    > >
    > > Thanks for your reply. My user has a database that is used in 1 year
    > > cycles. She wants to be able to clear the data and reset her autonumbers
    > > to
    > > start over with 1 for the new year. I am trying to write the code so she
    > > can
    > > accomplish this "with the push of a button."
    > >
    > > I tried the code you sent me last week. I get a message that the index
    > > cannot be dropped because it is used in a relationship. I guess what I
    > > really need is a way to use code to remove a relationship.
    > >
    > >
    > > "Wolfgang Kais" wrote:
    > >
    > >> Hello "blacklf".
    > >>
    > >> "blacklf" wrote:
    > >> > I am trying to automate a task for a user in MS Office Access 2003.
    > >> > I have everything figured out except how to write an SQL statement
    > >> > to temporarily disable a primary key. Does anyone know how to do
    > >> > this? I've read so much material searching for the answer that my
    > >> > eyes are crossing!
    > >>
    > >> You can't temporarily disable a primary key, you can delete it
    > >> (Drop Index [indexname] On [tablename])
    > >> and create a new one later
    > >> (Create Index [indexname] On [tablename] ([fieldname]) With Primary)
    > >> But:
    > >> Why would you delete a primary key? To be able to violate the
    > >> uniqueness of the values? You won't be able to create a primary
    > >> key afterwards!
    > >>
    > >> --
    > >> Regards,
    > >> Wolfgang
    > >>
    > >>
    > >>

    >
    >
    >
     
  10. blacklf

    blacklf
    Expand Collapse
    Guest

    Thanks. I'll give that a try.

    "pvdg42" wrote:

    >
    > "blacklf" <blacklf@discussions.microsoft.com> wrote in message
    > news:7A8FB376-3829-4632-842E-626183BCFAF6@microsoft.com...
    > > This is a good reference. Thanks. Will keep looking. Wolfgang brought
    > > to
    > > my attention that what I really need is a way to delete a relationship
    > > with
    > > code. When I try to drop the index I get a message that it can't be done
    > > because the index is used in a relationship.
    > >

    > Actually, I was referring to the "DROP CONSTRAINT" part of the reference.
    > You may have to drop constraints, do whatever you want to do, then add the
    > constraints back.
    >
    >
    >
     
  11. jean-pierre.vasseur

    jean-pierre.vasseur
    Expand Collapse
    Guest

    "blacklf" <blacklf@discussions.microsoft.com> a écrit dans le message de news: 19E91829-C3FB-4474-BB47-B63DE8AB1174@microsoft.com...
    >I am trying to automate a task for a user in MS Office Access 2003. I have
    > everything figured out except how to write an SQL statement to temporarily
    > disable a primary key. Does anyone know how to do this? I've read so much
    > material searching for the answer that my eyes are crossing!
     

Share This Page