Deleting a table with DAO

[Rick H]

How do I delete a table from a access database. I am using DAO.

I have a number of tables in the database, "Rev 0","Rev 1" etc.. I have tried the following code but it does'nt work

Dim db As Database
Dim rs As Recordset

Set db = OpenDatabase(App.path & "\Crr_data\" & ListView2.SelectedItem.text & "\valve.mdb")
Set rs = db.OpenRecordset(ListView5.SelectedItem.text, dbOpenDynaset)

rs.Delete ' goes wrong here
db.Close

I get the message "No current record"

Any ideas?

Cheers

[paulw]

You can either use the TableDefs collection Delete method or use the DROP TABLE TableName SQL syntax. Remember to refresh the collection or the database window.

Check Access help for those.

Cheers,

P.

[Rick H]

Cheers Paul,

I`ve tried doing it with the tabledefs but I still cant get it to work, have you got short examples, I think I am getting the syntax wrong

Cheers

Rick

[Gaffer]

Dunno what happened to Paul, I had lunch with him and he's gone all quiet...

Anyway, try this:

Dim db As Database

Set db = OpenDatabase(App.Path & "\Crr_data\" & ListView2.SelectedItem.Text & "\valve.mdb")

db.TableDefs.Delete ListView5.SelectedItem.Text

[paulw]

Really, use DROP TABLE - its easier and more portable, but if you must.

Dim db As Database
Set db = CurrentDb
db.TableDefs.Delete "MyTable"
db.TableDefs.Refresh
Set db = Nothing

Cheers,

P.