Alternatively, you could use the SQL approach (within VBA). Here is a proc
from my Fixit module, which accepts composite keys:
Private Sub AddRelation(strTable 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.Execute 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*********@yahoo.com> wrote in message
news:ea**************************@posting.google.c om...
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.CreateRelation ("ScheduleHistoryTransactions",
"ScheduleHistory", "Transactions")
2 rel.Attributes = dbRelationUpdateCascade
3 Set fld = rel.CreateField("SID;Schedule")
4 fld.ForeignName = "SID;Schedule"
5 rel.Fields.Append fld
6 dbs.Relations.Append 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
