A2003: want to delete table but can't

[bulrush]

A2003 on Win XP

Goal: Copy fresh data from production DB to my test DB. Code is in test DB.

Steps:
- Delete table "oldTable1".
- Rename "Table1" to "oldTable1"
- Import "Table1" from production DB to test DB.

When I try to delete "oldTable1" I get an error about "oldTable1" being in a relationship and it cannot be deleted. How do I delete the relationship so I can delete the table?

Here is my code:

' Delete old tables first.
DoCmd.DeleteObject acTable, "oldTable1"

' Rename tables to "old" prefix.
DoCmd.Rename "oldTable1", acTable, "Table1"

' Import real tables from M:
dbsrc = "M:\blah\production.mdb"
tname = "Table1"
DoCmd.TransferDatabase acImport, "Microsoft Access", dbsrc, acTable, tname, tname

 

[the_net_2.0]

check out the 'relationships' collection of the "currentdb" object. this is actually pretty complicated in VBA, so I would recommend finding another way to do it. Moreover, when you del a relay like that through code, obviously when you rename another table or import a new one to replace the old one, the relay will have to be established again. That's two steps that you're probably not going to want to spend time on...they're complex.

Unless of course, you can find code already written via the web.

 

[bulrush]

The relationships really don't matter, as I only run SQL code in this app and define my relationships via SQL.

Well, I deleted all the relationships by hand, then tried to run my code again, and got the same error: The table cannot be deleted because it's in a relationship. Weird.

 

[the_net_2.0]

did you try closing the db, compacting it, and then opening and doing it again?

 

[Steve R.]

check out the 'relationships' collection of the "currentdb" object. this is actually pretty complicated in VBA, so I would recommend finding another way to do it.

Thanks very much. I was not having a problem. Your post provided a great learning opportunity for an alternative approach to what I was doing for obtaining a listing of the relationships in a database.

Public Sub PrintRelationshipsV2()
    Rem using CurrentDB object
    Dim i As Integer
    i = 0
    For i = 0 To CurrentDb.Relations.Count - 1
        Debug.Print "Relationship: "; i, CurrentDb.Relations(i).Name
    Next i
End Sub

.

Bulrush: What happens if you run the code above? (PS: I see that vbaInet provided a far superior solution below.)

Well, I deleted all the relationships by hand, then tried to run my code again, and got the same error: The table cannot be deleted because it's in a relationship. Weird.

 

[bulrush]

No, I did not close nor compact the db after deleting the relationships by hand.

Could there be an SQL statement supported by Access, that deletes all Access relationships?

 

[vbaInet]

Here you go:

http://allenbrowne.com/DelRel.html

 

[the_net_2.0]

Here you go:

http://allenbrowne.com/DelRel.html

(laughing)

I was going to tell Steve that this already existed, but alas, there you have it!

 

[vbaInet]

(laughing)

I was going to tell Steve that this already existed, but alas, there you have it!

I was already writing aircode for this but then I remembered Allen B had some code for relationships so I searched on his site and found the link

 

[AccessBlaster]

You don't mention if any forms are bound to the old table. If there are you will have to release them first Me.RecordSource = "" then excute the export & drop table.

 

[the_net_2.0]

I was already writing aircode for this but then I remembered Allen B had some code for relationships so I searched on his site and found the link

I've browsed it for many years now. Actually sir, I don't really think there's much there anymore that's useful. But most of the stuff I've seen there and used, I've copied onto my machines anyway, so I privately take credit for it myself.

The thing I've used the most is the recursive file and folder listings. That's incredible. It has sure saved me a bunch of time, AND brain power!

 

[vbaInet]

I've browsed it for many years now. Actually sir, I don't really think there's much there anymore that's useful. But most of the stuff I've seen there and used, I've copied onto my machines anyway, so I privately take credit for it myself.

The thing I've used the most is the recursive file and folder listings. That's incredible. It has sure saved me a bunch of time, AND brain power!

To be honest, I only go there if I want to direct someone to his site. I like to code things myself

 

[the_net_2.0]

To be honest, I only go there if I want to direct someone to his site. I like to code things myself

well then you must have a he$$ of a lot of time on your hands! I copy and paste code whenever and wherever I can get it. I hate doing it, especially when there's no need to.

 

[vbaInet]

well then you must have a he$$ of a lot of time on your hands! I copy and paste code whenever and wherever I can get it. I hate doing it, especially when there's no need to.

Either that or I get it done in quick time I will copy / paste some APIs.

 

[the_net_2.0]

Either that or I get it done in quick time I will copy / paste some APIs.

there is nothing quicker than code that's already written, you dork!

 

[vbaInet]

there is nothing quicker than code that's already written, you dork!

There's lots of shabby code out there and some of the more reputable codes are generalised using things like Variants and the like, so I much prefer to create mine. You've gotta love it