Relationship Link 1

[SpankYou]

Hey,

I have an external database and a table linked in access, as it is an external database it only connects as a one to one relationship and will not seem to allow referential integrity.

So I have been looking for coding ways of doing this, this is what I have come up with. The error is "Invalid or unqualified reference" and it highlights ".CreateRelation".

Any ideas on what the problem may be or how to link them?

thanks for any help Sam



'Connect to Tables
Dim cnnDB As ADODB.Connection
Dim rstProjectAmmend As ADODB.Recordset
Dim rstLinkProject As ADODB.Recordset

Set cnnDB = Application.CurrentProject.Connection
Set rstProjectAmmend = New ADODB.Recordset
Set rstLinkProject = New ADODB.Recordset


'Open Recordsets
rstProjectAmmend.Open Source:="CST_COSTHEADER_AMEND", ActiveConnection:=cnnDB, CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic
rstLinkProject.Open Source:="OMNIS_CST_COSTHEADER", ActiveConnection:=cnnDB, CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic

Dim tdfProjectAmmend
Dim tdfLinkProject

tdfProjectAmmend = rstProjectAmmend
tdfLinkProject = rstLinkProject


' Create EmployeesDepartments Relation object, using
' the names of the two tables in the relation.
Set NewRel = .CreateRelation("ProjectAmmendments", tdfLinkProject.Name, tdfProjectAmmend.Name, _
dbRelationUpdateCascade)

' Create Field object for the Fields collection of the
' new Relation object. Set the Name and ForeignName
' properties based on the fields to be used for the
' relation.
relNew.Fields.Append relNew.CreateField("CH_CODE&quot
relNew.Fields!DeptID.ForeignName = "CH_CODE"
.Relations.Append relNew

 

[mpastore]

You cannot set integrity on an external database table and an access table. Best you may be able to do is enforce integrity via code or export your table to the external db and do it there.

Mike Pastore

Hats off to (Roy) Harper

 

[SpankYou]

I was afraid someone were going to say that, oh well thanks,

Sam