Deleting Tables

[coops1204]

I am Developing a simple VBA program for Access for my final year of my Degree and I have come up with a problem that I don’t seem to be able to fix.

I have create tables using SQL

SQL = "CREATE TABLE tblInvoiceLines" & _
      "(InvoiceNo LONG, PartNo LONG," & _
      "Quantity LONG)"

DoCmd.RunSQL (SQL)

Deleting this with the below code works,

SQL = ""
    'This will delete the Invoice Line Table
    SQL = "DROP TABLE  tblInvoiceLines"
DoCmd.RunSQL (SQL)

But once I alter the table with the below code the Delete command doesn’t work, does anyone know how I can fix this problem?

   SQL = "ALTER TABLE tblInvoiceLines " & _
   "ADD CONSTRAINT fkInvoiceNo " & _
   "FOREIGN KEY NO INDEX (InvoiceNo) REFERENCES " & _
   "tblInvoices (InvoiceNo) " & _
   "ON UPDATE CASCADE " & _
   "ON DELETE CASCADE "


conDatabase.Execute SQL

Thanks
Coops

 

[raskew]

Hi -

You can't delete a table if it's in a relationship, which you established with the ALTER TABLE code. Here's a routine which first checks for relationships, deletes any found and then deletes the table itself.

HTH - Bob

Sub DeleteTableTest3(pMyTable As String)
' This procedure deletes specified tables and any
' existing relationships the tables are participating
' in.
' Used inappropriately, it will have a devastating
' effect on an application.
' For safety's sake, I've commented-out the actual
' commands (they follow debug.print in every case).
' Once you've examined the code and are ready to go,
' remove the comments.

Dim db      As Database
Dim td      As TableDef
Dim Test    As String
Dim tName   As String
Dim thisrel As Relation

    Set db = CurrentDb
    
    On Error Resume Next
    
    tName = pMyTable
    '**********
    'Does table tName exist?
    'If true, delete it;
    '**********
    
      Test = db.TableDefs(tName).name
      If Err <> 3265 Then
         '**********
         ' Since the delete action will fail if the
         ' table is participating in any relation, first
         ' find and delete existing relations for table.
         '**********
         For Each thisrel In db.Relations
            If thisrel.Table = tName Or thisrel.ForeignTable = tName Then
               Debug.Print tName & " | " & thisrel.name
               'db.Relations.Delete thisrel.Name
            End If
         Next thisrel
         '**********
         ' Now, we're ready to delete the table.
         '**********
         Debug.Print tName & " will be deleted"
         'docmd.SetWarnings False
         'docmd.DeleteObject acTable, tName
         'docmd.SetWarnings True
      End If
    'End If
    db.Close
    Set db = Nothing
End Sub

 

[coops1204]

Works

That worked, thanks
Coops1204