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