Cannot Delete

[Skip Bisconer]

I have a table with several fields and I am trying to delete one of the fields. It tells me the field in related to one or more tables and to use Database Relationships to delelet the relation. I have taken every table out of the Relation Database, Deleted every query, form that ever related to the table and still can't delete the field from the table. I have done Database repair, delete the entire table and rebuilt it and I still have the problem. Any help out there? Thanks.

 

[jdraw]

I have a table with several fields and I am trying to delete one of the fields. It tells me the field in related to one or more tables and to use Database Relationships to delelet the relation. I have taken every table out of the Relation Database, Deleted every query, form that ever related to the table and still can't delete the field from the table. I have done Database repair, delete the entire table and rebuilt it and I still have the problem. Any help out there? Thanks.

Are you trying to delete one of the values for a field in the Table, or
are you trying to remove one of the fields from the Table definition?

 

[Skip Bisconer]

I am trying to remove one of the fields from the definition.

 

[winshent]

deleting indexes

I think this thread will help..

http://www.tek-tips.com/viewthread.cfm?qid=1338393&page=8

you might have to delete the index using vba.. this should do it..

Public Sub DeleteIndex(TableName As String, IndexName As String)
  
  Dim db As Database, tdf As dao.TableDef, Idx As DAO.index, b As Boolean

    Set db = CurrentDb()
    Set tdf = db.TableDefs(TableName)

    For Each Idx In tdf.Indexes
      Debug.Print Idx.Name
      If UCase(Idx.Name) = UCase(IndexName) Then
        db.Execute "DROP INDEX " & IndexName & " ON " & TableName
        b = True
        Exit For
      End If
    Next Idx
  
    If Not b Then MsgBox "Index " & IndexName & _
    " does not exist in the table " & TableName
   
End Sub

 

[Skip Bisconer]

Thanks I will try your suggestion

 

[ClwFLGator]

Sorry to revive such an old post, but I seem to be having a very similar problem. I was just wondering if the previous solution worked for the OP?

I was also wondering if there was any simpler way to solve the problem than with the code posted above, as I am scared to use that much code. lol

 

[Dennisk]

removing a table from the relationship window does not remove the relationship. So add all the tables back to relationship window. (hint right click in the relationship window and click add all tables). Are any tables connected by lines as these are your relationships. Right click the relationship causing the problem and select delete.

 

[ClwFLGator]

I right clicked and added all the tables to the relationships window as you said (they were all there already except maybe one), still nothing showing up there that I could delete or that was related to the table / field in question.

Let me clarify further. What I am actually trying to do is to remove the primary key designation from a particular field. I have created a new autonumber field and am trying to make that field the new primary key field.

I have deleted all of the relationships to said field in the relationships window (actually moved them over to the new desired primary key field and made all the appropriate updates / changes to the data in the related tables), but Access still will not allow me to remove the primary key designation. It says it is still part of one or more relationships (?).

I am beginning to wonder if I will have to go through every query, etc. that may still reference this field and delete the relationship within each query or is there something else I am missing?

 

[editolis]

Try this.

Create a button to your form and put this code:

Dim strSQL
strSQL = "DROP INDEX YOUR_FIELD_NAME ON YOUR_TABLE"
CurrentDb.Execute strSQL, dbFailOnError

Now try to delete.

 

[editolis]

Also if you want, you can delete your column by this code:

dim strSQL1
strSQL1 = "ALTER TABLE YOUR_TABLE_NAME DROP COLUMN YOUR_COLUMN_NAME"
CurrentDb.Execute strSQL1, dbFailOnError

 

[ClwFLGator]

Well, I didn't want to delete that field, just disable using it as the primary key in my database, and use a different (autonumber) field instead as the primary key. Is that what this will do (your first response):

Dim strSQL
strSQL = "DROP INDEX YOUR_FIELD_NAME ON YOUR_TABLE"
CurrentDb.Execute strSQL, dbFailOnError

 

[ClwFLGator]

I tried this:

Dim strSQL
strSQL = "DROP INDEX YOUR_FIELD_NAME ON YOUR_TABLE"
CurrentDb.Execute strSQL, dbFailOnError

All I got was this error:

Run-time error '3295':
Syntax error in DROP TABLE or DROP INDEX

It did not delete the index, or remove the primary key.

I did get it to work however by deleting the field in question (after making a table referencing the old primary key to the new primary key, and also making a backup of course). I then designated my new autonumber field as the primary key, recreated the old field (which I still need to reference) and then updated the numbers (which I saved earlier) back into that field.

Thanks everyone for your help!

 

[editolis]

The code is correct.

It is obvious that you are doing something wrong.

Maybe your table or you field is “double name”.

For example if your table is like this: “Table Clients”
Or you field is something like this “Client ID” then
You have to put brackets.

Dim strSQL
strSQL = "DROP INDEX [Client ID] ON [Table Clients]"
CurrentDb.Execute strSQL, dbFailOnError

OR

Your table and your field is OK but you have many extra spaces:

Dim strSQL
strSQL = "DROP INDEX ClientID ON TableClients "
CurrentDb.Execute strSQL, dbFailOnError

And this is wrong.

Check again and inform.

 

[Rich]

Well, I didn't want to delete that field, just disable using it as the primary key in my database, and use a different (autonumber) field instead as the primary key. Is that what this will do (your first response):

You'll have to create enough rows with the autonumber in place first, then change the link before deleting the original pk, make sure to back up your db first and make sure the new links are correct for child records