Creating a relationship between to tables in the same database ( runtime )

[MikeCt]

Hello
I tried to make thing as simple as possible for clearity

Fisrt I'm working in VB6
I have a simple form with a command buton on it
The project has a reference to Microsoft DAO 3.6 Object Library

There are 2 table in the database ( Patients ) & ( Medications )
Patients has 3 Fields ( PatientID ) ( FirstName ) ( LastName )
Medications has 3 Fields ( RecordID ) (PatientID ) ( Medication )

I need to create a relationship between the tables Patients & Medications on the field PatientID
I can create a relationship between the 2 fields easily in access
but I need to be able to do this programmicaly

I've run the program and both tables get indexed
Patients_PatientID gets indexed Yes (No Duplicates)
Medications_PatientID gets indexed Yes (No Duplicates)

All works well till I reach the line of code ".Relations.Append relNew" where I get the error
run-time error '3368'
Relationship must be on the same number of fields with the same data type.

Patients_PatientID data type is set to AutoNumber
Medications_PatientID data type is set to Number
Thanks for any help i can get
Mike

Private Sub Command1_Click()

Dim dbsDatabase As Database
Dim tdfPatients As TableDef
Dim tdfMedications As TableDef
Dim idxNew As Index
Dim relNew As Relation

Set dbsDatabase = OpenDatabase("C:\Relationship\database.mdb")

With dbsDatabase
Set tdfPatients = .TableDefs!Patients
Set tdfMedications = .TableDefs!Medications

With tdfPatients 'Create Index object for Patients table ( Primary )
Set idxNew = .CreateIndex("PatientIDIndex")
idxNew.Fields.Append idxNew.CreateField("PatientID") 'Create index on PatientID
idxNew.Unique = True 'Set index to no duplicates
.Indexes.Append idxNew 'Finalize index on patients
End With

With tdfMedications 'Create Index object for Medications table
Set idxNew = .CreateIndex("PatientIDIndex")
idxNew.Fields.Append idxNew.CreateField("PatientID") 'Create index on PatientID
.Indexes.Append idxNew 'Finalize index on patients
End With

' Create Relation object between table1 Patients_PatientID.......table2 Medications_PatientID
Set relNew = .CreateRelation("PatientsMedications", tdfPatients.Name, tdfMedications.Name, dbRelationUpdateCascade)
relNew.Fields.Append relNew.CreateField("PatientID")
relNew.Fields!PatientID.ForeignName = "PatientID"
.Relations.Append relNew
.Close
End With

End Sub

 

[trevil620]

Hey Mike,

Just curious if the two fields are of the same 'type'. I believe the 'Owner' table key is AutoNumber, so probably the 'Field Size' property is 'Long Integer'.

What is the Field Type of the 'Member' table (Medication) field? Is it also 'Long Integer'? If not, that is a problem.

Long ago I had a VB6 application that would analyze all the table structures in our database against a table of 'required indices' and relationships and rebuild whatever was missing. Possibly could find that code if needed.
Good Luck,
Wayne