Solved

Help with Access relationships

Posted on 2011-03-18
9
383 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

777 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