Writing SQL code

#1 (**permalink**) 28-Jul-2006, 08:26 AM

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!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12079-writing-sql-code.html

*

#2 (**permalink**) 28-Jul-2006, 08:26 AM

Hello "blacklf".
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079

"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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
(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

#3 (**permalink**) 28-Jul-2006, 08:26 AM

"blacklf" 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!
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079

Here's a reference for you,

http://office.microsoft.com/en-us/as...322071033.aspx
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079

#4 (**permalink**) 28-Jul-2006, 08:26 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
start over with 1 for the new year. I am trying to write the code so she can
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
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
>
>
>

#5 (**permalink**) 28-Jul-2006, 08:26 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079

"pvdg42" wrote:

>
> "blacklf" wrote in message
> news:19E91829-C3FB-4474-BB47-B63DE8AB1174@microsoft.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
> >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/as...322071033.aspx
>
>
>

#6 (**permalink**) 28-Jul-2006, 08:26 AM

"blacklf" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
>
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.

#7 (**permalink**) 28-Jul-2006, 08:26 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
year.

"blacklf" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
> 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
>>
>>
>>

#8 (**permalink**) 28-Jul-2006, 08:26 AM

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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
> >> uniqueness of the values? You won't be able to create a primary
> >> key afterwards!
> >>
> >> --
> >> Regards,
> >> Wolfgang
> >>
> >>
> >>
>
>
>

#9 (**permalink**) 28-Jul-2006, 08:26 AM

Thanks. I'll give that a try.

"pvdg42" wrote:

>
> "blacklf" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
> > with
> > code. When I try to drop the index I get a message that it can't be done
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12079
> > 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.
>
>
>