Help with Access relationships

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?
WillbrosAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
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
 
WillbrosAuthor Commented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President Online Computer Svcs, WNY IncCommented:
<<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
 
WillbrosAuthor Commented:
I agree and understand, but I have no choice on this one. So, is there any way to do it programmaticaly?
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
WillbrosAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President Online Computer Svcs, WNY IncCommented:
<<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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.