If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
On Cascade and other Constraints when using Alter Table
Hi all,
I've managed to export data from an old mdb into xml, then run Excel vba code to sort it so it will fit the new database (Access 2003), run access vba to load the resulting spreadsheets into my new schema (imported in from Development with no data/relationships) and all I need to do now to "save" time is create the relationships in Access vba code for the new database. I have tried this: ALTER TABLE tblAccountClient ADD CONSTRAINT relAccount_AccountClient FOREIGN KEY (AccountID) REFERENCES tblAccount (AccountID) ON UPDATE CASCADE for example, both as a DoCmd.RunSQL and a Data Def query. It does not seem to recognise the UPDATE part of ON UPDATE. I also wish to set ON DELETE and the same happens. Plus can anyone tell me how to specify the more bizarre joins such as one to one and relation left? Thanks in advance |
#2
|
|||
|
|||
On Cascade and other Constraints when using Alter Table
Lester Lane wrote:
I have tried this: ALTER TABLE tblAccountClient ADD CONSTRAINT relAccount_AccountClient FOREIGN KEY (AccountID) REFERENCES tblAccount (AccountID) ON UPDATE CASCADE for example, both as a DoCmd.RunSQL and a Data Def query. It does not seem to recognise the UPDATE part of ON UPDATE. I also wish to set ON DELETE and the same happens. AFAIK, you need to execute your SQL statement from ADO to allow Jet to honor ON UPDATE or ON DELETE. Here is a sample that works on my system (Access 2003, SP3). Try it by changing the strSql variable to hold your ALTER TABLE statement. Public Sub addConstraint() Dim cn As Object Dim strSql As String strSql = "ALTER TABLE CalendarEntries " _ & "ADD CONSTRAINT Entries_PropRef_FK " _ & "FOREIGN KEY (PropRef) " _ & "REFERENCES CurrentProperties(PropRef) " _ & "ON UPDATE CASCADE;" Debug.Print strSql Set cn = CurrentProject.Connection cn.Execute strSql Set cn = Nothing End Sub Plus can anyone tell me how to specify the more bizarre joins such as one to one and relation left? The only way I know is to use the Relationships window. Good luck, Hans |
#3
|
|||
|
|||
On Cascade and other Constraints when using Alter Table
On 8 May, 16:35, Hans Up wrote:
Lester Lane wrote: I have tried this: ALTER TABLE tblAccountClient ADD CONSTRAINT relAccount_AccountClient FOREIGN KEY (AccountID) REFERENCES tblAccount (AccountID) ON UPDATE CASCADE for example, both as a DoCmd.RunSQL and a Data Def query. *It does not seem to recognise the UPDATE part of ON UPDATE. *I also wish to set ON DELETE and the same happens. AFAIK, you need to execute your SQL statement from ADO to allow Jet to honor ON UPDATE or ON DELETE. Here is a sample that works on my system (Access 2003, SP3). *Try it by changing the strSql variable to hold your ALTER TABLE statement. Public Sub addConstraint() Dim cn As Object Dim strSql As String strSql = "ALTER TABLE CalendarEntries " _ * * *& "ADD CONSTRAINT Entries_PropRef_FK " _ * * *& "FOREIGN KEY (PropRef) " _ * * *& "REFERENCES CurrentProperties(PropRef) " _ * * *& "ON UPDATE CASCADE;" Debug.Print strSql Set cn = CurrentProject.Connection cn.Execute strSql Set cn = Nothing End Sub Plus can anyone tell me how to specify the more bizarre joins such as one to one and relation left? The only way I know is to use the Relationships window. Good luck, Hans Thanks Hans, I would be trying this if it wasn't for the 1-1 etc joins I need to do. I have just recently stumbled on the following in my MS Help: "Note The Microsoft Jet database engine does not support the use of CONSTRAINT, or any of the data definition language (DDL) statements, with non-Microsoft Jet databases. Use the DAO Create methods instead." This is about using the CONSTRAINT Clause and I would think it means you CAN use it purely with Access tables/databases. It states it accepts the ON DELETE etc clauses. BUT I looked at the DAO Create method and this is what it had at the top: "Creates a new Relation object (Microsoft Jet workspaces only)." I give up! So now I am playing with CreateRelation method and it looks promising. Thanks for your help though. |
#4
|
|||
|
|||
On Cascade and other Constraints when using Alter Table
Lester Lane wrote:
I would be trying this if it wasn't for the 1-1 etc joins I need to do. I have just recently stumbled on the following in my MS Help: "Note The Microsoft Jet database engine does not support the use of CONSTRAINT, or any of the data definition language (DDL) statements, with non-Microsoft Jet databases. Use the DAO Create methods instead." This is about using the CONSTRAINT Clause and I would think it means you CAN use it purely with Access tables/databases. It states it accepts the ON DELETE etc clauses. BUT I looked at the DAO Create method and this is what it had at the top: "Creates a new Relation object (Microsoft Jet workspaces only)." I give up! You totally lost me there, Lester. DAO and ADO can accomplish similar tasks, but their approaches are different. So now I am playing with CreateRelation method and it looks promising. Thanks for your help though. OK. I looked at that, too. I think you can use the Attributes property with CreateRelation to do what you want. Good luck, Hans |
Thread Tools | |
Display Modes | |
|
|