Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with Access relationships

Posted on 2011-03-18
9
Medium Priority
?
425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 total points
ID: 35169838
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
ID: 35169909
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
ID: 35170070
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21
ID: 35170075
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
 
LVL 58
ID: 35171320
<<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
ID: 35171556
I agree and understand, but I have no choice on this one. So, is there any way to do it programmaticaly?
0
 
LVL 21
ID: 35172214
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
ID: 35174846
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 58
ID: 35175445
<<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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

636 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