Solved

Help with Access relationships

Posted on 2011-03-18
9
368 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
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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 57
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 57
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Transfer records between two Access tables 6 36
sort Time by AM and PM in query 2 18
Tags from access to excel 3 24
Unrecognized Database Format 8 88
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

948 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

23 Experts available now in Live!

Get 1:1 Help Now