Solved

Help with Access relationships

Posted on 2011-03-18
9
357 Views
Last Modified: 2013-11-27
I have created a releationship between two tables, in access using the wizard. Now I need to know how to (programmaticaly delet the relation and later creat it again). Can anyone help?
0
Comment
Question by:Willbros
  • 4
  • 3
  • 2
9 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
Comment Utility
Deleting and adding back a relationship on a regular basis is not generally NOT  a good programming practice.

Curious, why are you needing to do this?

To work with relationships I like to use DAO for this.

Examples:

Deleting:
Function DeleteRelationDAO()
    DBEngine(0)(0).Relations.Delete "tblDaoContractortblDaoBooking"
End Function

Open in new window


Creating:
Function CreateRelationDAO()
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb()
    
    'Create a new relation.
    Set rel = db.CreateRelation("tblDaoContractortblDaoBooking")
    
    'Define its properties.
    With rel
        'Specify the primary table.
        .Table = "tblDaoContractor"
        'Specify the related table.
        .ForeignTable = "tblDaoBooking"
        'Specify attributes for cascading updates and deletes.
        .Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
        
        'Add the fields to the relation.
        'Field name in primary table.
        Set fld = .CreateField("ContractorID")
        'Field name in related table.
        fld.ForeignName = "ContractorID"
        'Append the field.
        .Fields.Append fld
        
        'Repeat for other fields if a multi-field relation.
    End With
    
    'Save the newly defined relation to the Relations collection.
    db.Relations.Append rel
    
    'Clean up
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing
    Debug.Print "Relation created."
End Function

Open in new window



.
0
 

Author Comment

by:Willbros
Comment Utility
I agree with you on :is not generally NOT  a good programming practice.
This is a Confilcts of interest app, I must replace every record that comes in form the trainning site via internet. I can not append. I also have to keep any commemts form HR for those records even if they get replace with a new record or neverf get replaced.
So, what i did was put a subform on my user entry form to allow the user to enter notes into what I call the notes table(2 columns employee_id and notes). This way when I have to upload a new file and remove every record currently in the main table(or the entire tabe), I will still have the notes enterd and what employee_id they belong to. If the new file had a record with that same employee_id and I re connect a relation to that table, the use will see the notes when heor she looks at that record.
If I could have done a VB app , this would have been done weeks ago, but not they want it in access.
Does this make sence or did I totally confuse you?
If you know a better way, spit it out man!!! lol

0
 
LVL 21
Comment Utility
In your case I would not use enforce  Referential Integrity at the database engine level. I would do it in the front end if it is needed.  I am not usre it is even needed in this application for this relationship.
0
 
LVL 21
Comment Utility
In your case I would not use Enforce  Referential Integrity at the database engine level. I would do it in the front end if it is needed.  I am not sure it is even needed in this application for this relationship.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<So, what i did was put a subform on my user entry form to allow the user to enter notes into what I call the notes table(2 columns employee_id and notes). This way when I have to upload a new file and remove every record currently in the main table(or the entire tabe), I will still have the notes enterd and what employee_id they belong to. If the new file had a record with that same employee_id and I re connect a relation to that table, the use will see the notes when heor she looks at that record.>>

  I agree with HiTechCoach; this is not something you'd generally do.  The ability to create relationships is intended for generation of DB's from meta data.  It's not something you'd be doing in the general course of using an app and what it points at is that your DB design is not correct.

JimD.
0
 

Author Comment

by:Willbros
Comment Utility
I agree and understand, but I have no choice on this one. So, is there any way to do it programmaticaly?
0
 
LVL 21
Comment Utility
Why do you feel you need o enfiorce RI?

I would just turn it off since it does not make sens to use it in the situation.
0
 

Author Comment

by:Willbros
Comment Utility
Ok, let me ask this.
If I ask you to write an app in access that does the following:
Has a user screen for display of record info only, except for the notes field, ( you can add/edit it).
Will import an XL file every week that may or may not have some of the same records in the one you imported a week ago or may have a new one of the same employee_id as some of the old records.
And, you must replace the file on import, (no appending).
However, when you do import a new file, all the notes that applied to a given record still show up in the notes field when that record is selected for display.
My answer to my Boss was this needs to be a vb or c application, but his answer was, no, it must be in access..  
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<My answer to my Boss was this needs to be a vb or c application, but his answer was, no, it must be in access..  >>

  Why is it you feel that is the case?   I see no reason it can't be done in Access unless you want to do this in a OOP or n-Tier design for specific reasons.  Although another language may give you more flexability in your development as far as the UI, n-tier, etc., from a functional perspective based on what you state, there is nothing that would stop you from using Access.  

<<And, you must replace the file on import, (no appending).>>

  You mean record?  I would do one of two things:

1. Keep one copy of the employee record and at each import, append new records to the table and update ones that already exist.  I would keep a "LastImported" date/time stamp on the employee record.  In that way I could tell at any given time when a employee record was last touched. I'd also have a snapshot of the last import file (by looking for a specific LastImported date/time). Notes linked to the employee record would remain between imports and RI could be enforced.

2. Create a new employee record at each import, even for ones that already exist.  In other words, you have a complete copy of the import file stored.  Notes could be kept, but RI not enforced.  Or you could have an employee "Master" table with one record per employee, then an "import table", which would have a copy of each import record.  The master table only severing to allow for the entry of notes, which would be linked to it.

   In short, without knowing some more details, it's hard to suggest a specific approach, and there are a multitude of ways that this could be structured.

  For example, is it important to know at all how the employee record changed from import to import?  If the answer is no, then I'd probably go with #1 above.

  And if I needed more detail on the import itself, then I would create an import header table.  This would contain one record for each import process and contain the Date/time of the import, who did it, what file it came from, etc.  The employee records then would link to this with a "LastImport" field in place of the date/time stamp.

JimD.

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now