Board index » delphi » Problem with compact & repair access 2000 database

Problem with compact & repair access 2000 database

Hi

Sometimes, after compact & repair the database, one of the table that has
autonumber as the primary key somehow lost
the primary key setting. ie a table without a primary key.

Is this a bug in Compact & repair? Can someone show me how to reset the
table's primary key in ado/sql commands?

Thanks

James

 

Re:Problem with compact & repair access 2000 database


Quote
>Can someone show me how to reset the
>table's primary key in ado/sql commands?

ALTER TABLE tblCustomers
   ALTER COLUMN CustomerID INTEGER
   CONSTRAINT PK_tblCustomers PRIMARY KEY

or

ALTER TABLE tblCustomers
   ALTER COLUMN CustomerID INTEGER PRIMARY KEY

but using the short version won't allow you to specify the constraint name.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Problem with compact & repair access 2000 database


I tried the following but it gave me the error message, "Cannot change field
CustomerID, it is part of one or more relationship."

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:3jk3otokunh4ji8f65b2umit35julskqok@4ax.com...

Quote

> >Can someone show me how to reset the
> >table's primary key in ado/sql commands?

> ALTER TABLE tblCustomers
>    ALTER COLUMN CustomerID INTEGER
>    CONSTRAINT PK_tblCustomers PRIMARY KEY

> or

> ALTER TABLE tblCustomers
>    ALTER COLUMN CustomerID INTEGER PRIMARY KEY

> but using the short version won't allow you to specify the constraint
name.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:Problem with compact & repair access 2000 database


Quote
>I tried the following but it gave me the error message, "Cannot change field
>CustomerID, it is part of one or more relationship."

What is the structure of the table?
What other indexes and constraints are defined for this table?

The SQL to create a Primary key isn't going to work if Access won't allow it.
What happens when you try to create this same key interactively in Access.
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Problem with compact & repair access 2000 database


I can recreate/reset the Primary key in Access 2000 without any problem.

AuditTrans Table                                    SalesTrans Table
Users Table
----------------------------------------------------------------------------
--------------------------------------------------
ID (AutoNumber, primary Key)                ID    (AutoNumber, Primary Key)
ID (int, primary Key)
DeviceTime  (dateTime)                         Total (Currency)
Name
ServerTime  (dateTime)                          Cash (Currency)
etc
UserID    (int)                                             Cheque
(Currency)
TransType (int)                                         AuditTransID (int)
etc                                                              etc

The Field name is actually "AuditTrans.ID", not "CustomerID".

I use the "relationships" to link the above tables. ie AuditTrans.ID link to
SalesTrans.AuditTransID. The link type is
Indeterminate. It is because a record exists in AuditTrans doesn't mean it
exists in SalesTrans. But it can only be
one record in AuditTrans  to one record in SalesTrans.

There is also a link from AuditTrans.UserID to Users.ID. In this case, the
link type is one to many.

For some strange reason, the AuditTrans.ID lost its Primary Key setting
after "COMPACT & REPAIR". It
does not happen all the times, just from time to time.

Thanks!!!!

Cheers
James

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:ovudotg15p38amc15jco1n42adj7n1d2ec@4ax.com...

Quote

> >I tried the following but it gave me the error message, "Cannot change
field
> >CustomerID, it is part of one or more relationship."

> What is the structure of the table?
> What other indexes and constraints are defined for this table?

> The SQL to create a Primary key isn't going to work if Access won't allow
it.
> What happens when you try to create this same key interactively in Access.
> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:Problem with compact & repair access 2000 database


Quote
>The Field name is actually "AuditTrans.ID", not "CustomerID".

>I use the "relationships" to link the above tables. ie AuditTrans.ID link to
>SalesTrans.AuditTransID. The link type is
>Indeterminate. It is because a record exists in AuditTrans doesn't mean it
>exists in SalesTrans. But it can only be
>one record in AuditTrans  to one record in SalesTrans.

I can't guess where the problem is.  About all I can suggest is you try removing
these relationships and see if that allows you to create the index with SQL.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:Problem with compact & repair access 2000 database


I am able to create the index with SQL if I remove these relationships.
Do you know how to remove the relationships using SQL without using MS
Access.

Many thanks fo all your help.

James

"Brian Bushay TeamB" <BBus...@Nmpls.com> wrote in message
news:571mot4cv5vles544p2laqmm11tgamldim@4ax.com...

Quote
> >The Field name is actually "AuditTrans.ID", not "CustomerID".

> >I use the "relationships" to link the above tables. ie AuditTrans.ID link
to
> >SalesTrans.AuditTransID. The link type is
> >Indeterminate. It is because a record exists in AuditTrans doesn't mean
it
> >exists in SalesTrans. But it can only be
> >one record in AuditTrans  to one record in SalesTrans.

> I can't guess where the problem is.  About all I can suggest is you try
removing
> these relationships and see if that allows you to create the index with
SQL.

> --
> Brian Bushay (TeamB)
> Bbus...@NMPLS.com

Re:Problem with compact & repair access 2000 database


Quote
>I am able to create the index with SQL if I remove these relationships.
>Do you know how to remove the relationships using SQL without using MS
>Access.

ALTER TABLE tblCustomers
   DROP CONSTRAINT NameOfTheConsraintHere

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads