modifying database

[slickkick]

I am trying to change the structure of an existing database Access 2000 database for a DB conversion program. I am running into two problems and I am not familiar enough with database programming to work through them.

1. When I try to set some field values, I get an error
2. When I try to delete some columns, I get an error

The first problem occurs under the following conditions:

I am trying to add a relationship between two tables but I get an error saying "Cannot update. Database or object is read-only."

        strSQL = "SELECT DISTINCT table1.ID AS pKey, table2.table1_ID AS fKey " _
                & "FROM table1 RIGHT JOIN table2 ON table1.Project_ID = table2.Project_ID"

        RS.Open strSQL, Conn, adOpenStatic, adLockPessimistic

        If Not RS.BOF Then
            While Not RS.EOF
                RS.Fields("fKey") = RS.Fields("pKey") 'ERROR HERE
                RS.Update
                RS.MoveNext
            Wend
        End If

        RS.Close

For the second error, I am simply trying to remove a column to a foriegn key whose relationship is no longer valid. I recieve an error "Cannot delete a field that is part of an index or is needed by the system."

         strSQL = "ALTER TABLE table3" _
               & "DROP COLUMN Old_ID"

How do I delete the relationship?

God I hope someone out there knows what they are doing

[Norby mahoney]

1.) To create a relationship between these two tables, try this:
strSQL =
"ALTER TABLE table2
ADD CONSTRAINT FK_foreignkeyName
FOREIGN KEY (table1_ID) REFERENCES
table1(ID)
*ON UPDATE CASCADE
*ON DELETE CASCADE"

*These might cause a problem. The rest of the query works fine, though.

2.)Remove key before column deletion:
ALTER TABLE table3
DROP CONTRAINT FK_foreignkeyName

Now you should be able to drop the column.