INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close