sql cascade errors

[kerrance76]

Hello i am moving a database to a different system and have been told to write several functions for it. I have written the code below for setting up a new database but am having problems setting the delete and update cascades in strSQL5. I've tried everything i can think of but keep getting error in the constraint clause, can any body point out where i have gone wrong please.

I know this is not the easiest way or quickest of building a table but this is how i have been told to do it

Thanks for looking

Public Function createTables()

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String

strSQL1 = "CREATE TABLE tblCustomers " & _
         "(CustomerNo INTEGER, LastName TEXT (15), FirstName TEXT (10), Address TEXT (25), " & _
         "City TEXT (15), Telephone TEXT (13), DiscountRate Single, " & _
         "CONSTRAINT pk1 PRIMARY KEY (CustomerNo))"
         
strSQL2 = "CREATE TABLE tblSalesmen " & _
          "(SalesmanNo INTEGER, SalesmanName TEXT (50), MonthlyBasicSalary SINGLE, " & _
          "CommissionRate SINGLE, " & _
          "CONSTRAINT pk2 PRIMARY KEY (SalesmanNo))"
          
strSQL3 = "CREATE TABLE tblParts " & _
          "(PartNo LONG, Description TEXT (30), SellingPrice CURRENCY, CostPrice CURRENCY, " & _
          "CONSTRAINT pk3 PRIMARY KEY (PartNo))"

strSQL4 = "CREATE TABLE tblInvoices " & _
          "(InvoiceNo AUTOINCREMENT, [Date] DATE, CustomerNo INTEGER, SalesmanNo INTEGER, " & _
          "CONSTRAINT pk4 PRIMARY KEY (InvoiceNo), " & _
          "CONSTRAINT fk1 FOREIGN KEY (CustomerNo) " & _
          "REFERENCES tblCustomers (CustomerNo), " & _
          "CONSTRAINT fk2 FOREIGN KEY (SalesmanNo) " & _
          "REFERENCES tblSalesmen (SalesmanNo))"
         
strSQL5 = "CREATE TABLE tblInvoiceLines " & _
          "(InvoiceNo LONG, PartNo LONG, Quantity INTEGER, " & _
          "CONSTRAINT pk5 PRIMARY KEY (invoiceNo, PartNo), " & _
          "CONSTRAINT ck1 FOREIGN KEY (PartNo) " & _
          "REFERENCES tblParts(PartNo), " & _
          "CONSTRAINT ck2 FOREIGN KEY (InvoiceNo) " & _
          "REFERENCES tblInvoices(InvoiceNo) " & _
          "ON DELETE CASCADE ON UPGRADE CASCADE)"

Dim db As Database
Set db = DBEngine.OpenDatabase(Application.CodeProject.Path & "\copy.mdb")

Dim qdf As QueryDef

Set qdf = db.CreateQueryDef("", strSQL1)
    qdf.Execute
Set qdf = db.CreateQueryDef("", strSQL2)
    qdf.Execute
Set qdf = db.CreateQueryDef("", strSQL3)
    qdf.Execute
Set qdf = db.CreateQueryDef("", strSQL4)
    qdf.Execute
Set qdf = db.CreateQueryDef("", strSQL5)
    qdf.Execute

End Function

 

[RoyVidar]

Update, not upgrade

"ON DELETE CASCADE ON UPDATE CASCADE)"

and, I don't think this is valid DAO DDL. Open an ADO/OLE DB connection to the db, and execute on that connection in stead.

 

[kerrance76]

Thanks but i still get the same error

 

[RoyVidar]

What is the current code, and error message?

 

[RoyVidar]

Just ran this

Public Function createTables2()

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String
Dim cn As ADODB.Connection

strSQL1 = "CREATE TABLE tblCustomers " & _
         "(CustomerNo INTEGER, LastName TEXT (15), FirstName TEXT (10), Address TEXT (25), " & _
         "City TEXT (15), Telephone TEXT (13), DiscountRate Single, " & _
         "CONSTRAINT pk1 PRIMARY KEY (CustomerNo))"
         
strSQL2 = "CREATE TABLE tblSalesmen " & _
          "(SalesmanNo INTEGER, SalesmanName TEXT (50), MonthlyBasicSalary SINGLE, " & _
          "CommissionRate SINGLE, " & _
          "CONSTRAINT pk2 PRIMARY KEY (SalesmanNo))"
          
strSQL3 = "CREATE TABLE tblParts " & _
          "(PartNo LONG, Description TEXT (30), SellingPrice CURRENCY, CostPrice CURRENCY, " & _
          "CONSTRAINT pk3 PRIMARY KEY (PartNo))"

strSQL4 = "CREATE TABLE tblInvoices " & _
          "(InvoiceNo AUTOINCREMENT, [Date] DATE, CustomerNo INTEGER, SalesmanNo INTEGER, " & _
          "CONSTRAINT pk4 PRIMARY KEY (InvoiceNo), " & _
          "CONSTRAINT fk1 FOREIGN KEY (CustomerNo) " & _
          "REFERENCES tblCustomers (CustomerNo), " & _
          "CONSTRAINT fk2 FOREIGN KEY (SalesmanNo) " & _
          "REFERENCES tblSalesmen (SalesmanNo))"
         
strSQL5 = "CREATE TABLE tblInvoiceLines " & _
          "(InvoiceNo LONG, PartNo LONG, Quantity INTEGER, " & _
          "CONSTRAINT pk5 PRIMARY KEY (invoiceNo, PartNo), " & _
          "CONSTRAINT ck1 FOREIGN KEY (PartNo) " & _
          "REFERENCES tblParts(PartNo), " & _
          "CONSTRAINT ck2 FOREIGN KEY (InvoiceNo) " & _
          "REFERENCES tblInvoices(InvoiceNo) " & _
          "ON DELETE CASCADE ON UPDATE CASCADE)"

'Dim db As Database
'Set db = DBEngine.OpenDatabase(Application.CodeProject.Path & "\copy.mdb")
'Dim qdf As QueryDef
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
        Application.CodeProject.Path & "\copy.mdb;"

With cn
    .Execute strSQL1
    .Execute strSQL2
    .Execute strSQL3
    .Execute strSQL4
    .Execute strSQL5
End With

'Dim qdf As QueryDef

'Set qdf = db.CreateQueryDef("", strSQL1)
'    qdf.Execute
'Set qdf = db.CreateQueryDef("", strSQL2)
'    qdf.Execute
'Set qdf = db.CreateQueryDef("", strSQL3)
'    qdf.Execute
'Set qdf = db.CreateQueryDef("", strSQL4)
'    qdf.Execute
'Set qdf = db.CreateQueryDef("", strSQL5)
'    qdf.Execute

End Function

on my setup, and it worked. What I've done, is exactly what I said. Replaced upgrade with update, and execute on an ADO/OLEDB connection.

 

[kerrance76]

Sorry i've taken so long to reply, been away for a few days,

The error i was getting was error 3289, syntax error in constraint clause, which i get even with only the delete clause entered.

I have never created a connection using ADO before, but after copying your code and adding an Active X Library to my references it worked fine, I'm not sure of the difference between these 2 types of connection so i have a bit of reading to do.

Thank you

 

[unclejoe]

Hi,

You still having trouble? I had no problems creating tables with Roy's code but the Cascade Delete and Update were not successful.

Maybe you need "Alter Table"?

Sorry i've taken so long to reply, been away for a few days,

The error i was getting was error 3289, syntax error in constraint clause, which i get even with only the delete clause entered.

I have never created a connection using ADO before, but after copying your code and adding an Active X Library to my references it worked fine, I'm not sure of the difference between these 2 types of connection so i have a bit of reading to do.

Thank you

 

[kerrance76]

Hi
no that is all sorted now, the cascade delete and update worked ok on the join they were specified for (InvoiceNo) using Roy's code, i am now reading up on ADO so that i can use it for the rest of this project.

Thanks for looking