424,054 Members | 1,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

multiple field primary key relationship

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
CDB
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

Nov 12 '05 #2

P: n/a
thanks, allen! worked like charm.
"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<t1*******************@news-server.bigpond.net.au>...
Suzanne, I think you have to append each field in turn to the Fields of the
Relation.

Set fld = rel.CreateField("SID")
fld.ForeignName = "SID"
rel.Fields.Append fld

Set fld = rel.CreateField("Schedule")
fld.ForeignName = "Schedule"
rel.Fields.Append fld

dbs.Relations.Append rel
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.