Alternatively, you could use the SQL approach (within VBA). Here is a proc
from my Fixit module, which accepts composite keys:
Private Sub AddRelation(str Table As String, strRelName As String, _
strForeignKey As String, strForeignTable As String, strForeignField As
String)
'^^^^^^^^^^^^^^ ^^^^^^^^
Dim strSql As String
On Error GoTo procerr
strSql = "ALTER TABLE " & strTable & " ADD CONSTRAINT " & strRelName & _
" FOREIGN KEY (" & strForeignKey & ") REFERENCES " & strForeignTable &
_
" (" & strForeignField & ");"
IniVal.DataDb.E xecute strSql
procexit:
Exit Sub
procerr:
strSql = strSql & "**FAILED** " & vbNewLine & Err.Description
Err.Clear
xdlg cbNote, cbLog, strSql
Resume procexit
End Sub
I use a class module for the common values and objects such as
Inival.DataDb. This points to the back end. "xdlg" is a handler in my
MessageStuff module.
Clive
"suzanne shelton" <ch*********@ya hoo.com> wrote in message
news:ea******** *************** ***@posting.goo gle.com...
Help! I have two tables that i am wishing to join that involve a
multiple field primary key on the primary table.
Primary table: ScheduleHistory
SID *PK
Schedule *PK
Secondary table: Transactions
SID
Schedule
...
When i run the following code, i get an "Invalid field definition
'SID;Schedule' in definition of index or relationship." on line 6.
I am able to manually create this relationship.
1 Set rel = dbs.CreateRelat ion ("ScheduleHisto ryTransactions" ,
"ScheduleHistor y", "Transactio ns")
2 rel.Attributes = dbRelationUpdat eCascade
3 Set fld = rel.CreateField ("SID;Schedule" )
4 fld.ForeignName = "SID;Schedu le"
5 rel.Fields.Appe nd fld
6 dbs.Relations.A ppend rel
This seems pretty straight forward, but i can't for the life of me
figure out what i'm doing wrong. Anyone have any ideas?!?
-s
