Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    Hi guys & gals, it's another of my long winded questions again, but at least I'm 99% of where I want to be!

    I have two tables in my database - the first is a data dictionary, that holds the following information:

    Conceptual Area
    Table Name
    Field Name
    Data Type
    Field Size
    Primary Key Indicator

    This is then processed in vba to build a number of individual tables (SQL CREATE TABLE...)

    My second table then holds a list of relationships, made up as follows:

    LeftTable
    LeftField
    RightTable
    RightField

    (these fields are selected via drop downs etc.)

    The second table is then run against some more code to build the database relationships, using DAO - the majority of these relationships are simply one field in one table mapping to another field in another table - a few use as many as four fields in the relationship.

    The area I have a problem in, is when 1 table has two distinct relationships with another table.

    E.g.:
    Table vw_Consent_source has two fields:
    Consent_source_code (primary)
    Consent_source_desc

    Table vw_consent has a number of fields:
    Known_individual_id
    Consent_type_code
    Direct_consent_source
    Indirect_consent_source
    etc...

    In my data model, the vw_consent_source.consent_source_code links to vw_consent.direct_consent_source AND vw_consent.indirect_consent_source

    Using the relationships window is easy, as you can add a second version of the table, which automatically gets an alias of [[ tablename ]]_1, and you can build the new relationship from this, however, trying to trap this in code is not very easy - I can determine that it is error code 3015, but that is as far as I get!!

    If anybody thinks tey can help, and would like a copy of the database I am working from, with all of my code, then please shout!

    Many thanks, once again, for everybodies help at this forum

    Chris

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    Reaching quite a ways back in the post archives, I found this <post#=44019>post 44019</post#>.

    You may want to investigate the other posts in the thread as well as the links mentioned there (particularly the MSDN link).

    I realize this is not a direct answer for you, but maybe it will help point you in the right direction.

    HTH

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    It isn't clear why you would have a join like that unless vw_consent_source is a lookup table, in which case, why are you worrying about creating permanent relationships and what is the actual problem?

    If it is a lookup table, then the join is really the other way around, and you have two separate relationships, one from w_consent_source.consent_source_code to vw_consent.direct_consent_source AND a separate relationship from w_consent_source.consent_source_code to vw_consent.indirect_consent_source. As long as those relationships are given unique names and created and appended to the relationships collection separately, you shouldn't have a problem.

    Post the code you're using to create the relationships and maybe I can point out the problem. We build them in code using multifield keys all the time when updating a back end, but that isn't really what you're doing. The alias you see in the relationship window is not really there, it's just a convenient way to showing the two relationship graphically, so don't worry about it.
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    Excuse a dumb question <img src=/S/confused.gif border=0 alt=confused width=15 height=20> , but why would you wish to create tables and their relationships on the fly?
    Pat

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    I can't speak for why Chris is doing it, but I can think of a couple of reasons off the top of my head.

    [list=1]<LI>Creating and destroying Temp Tables
    <LI>If you have a FE/BE situation and make changes to the FE that requires changes to the BE and it's all at a remote site, you can write code to make the changes to the BE the first time new FE is run. Although you don't have to do that now, since there is a tool that I was involved with writing that will do this for you. See the link in my sig if you are interested. For a more detailed description of this concept have a look at this page
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    Fair comment, it's amazing how much you forget in time (where is the Altzeimers Smily?).
    I must admit that I have done this before in A97 to build Databases and tables within those databases in an application where I had a database per month (over a 7 year period) and a table per nnn days.
    Thanks for your response.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    With this large a block of code, it is better to post it as an attachment rather than putting it direclty into a message ... especially when only the last part relates to creating relationships! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> There also seems to be something a little odd about the original formatting wherever you copied it from because some of the lines refused to wrap or are breaking in odd places for no reason I can see. Just as a caveat to anyone else looking at it, it's all there, but some things have slipped to the next line.

    Are you building tmpRelationships in there somewhere? I didn't see it, and I'm not willing to wade all the way through that to see what the table structure might be, so I'm going to guess on a few things. At first glance it looks to me like your problem is that you're checking for an existing relationship between two tables and assuming that there can only be one, which is not always the case. You don't appear to have your routine set up to use anything but a combination of the two table names as the name for the relationship. I got around this by creating a routine that allowed for the passing of an optional string containing a relationship name. In the table where we store our relationship information, we have an optional field for relationship name. If it is empty, the two table names are used to name the relationship. If it is populated, the field value is used. Doing something like that should work for you as well.
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    As an afterthought, I use a custom datatype to pass the information into the routine I built. It looks like this:

    <pre>Public Type NewRelation 'holds values for a new relationship
    RelName As Variant 'name of relationship object
    PrimaryTable As String 'primary table name (parent table)
    ForeignTable As String 'foreign table name (child table)
    PrimaryField1 As String 'key field name in primary table
    PrimaryField2 As String 'additional keys for multi-field relation
    PrimaryField3 As String '
    ForeignField1 As String 'key field name in foreign table
    ForeignField2 As String 'additional keys for multi-field relation
    ForeignField3 As String '
    AttribUnique As Boolean 'Unique relationship?
    AttribDontEnforce As Boolean 'No referential integrity?
    AttribInherited As Boolean 'relationship in non-current database?
    AttribUpdateCascade As Boolean 'Cascading updates?
    AttribDeleteCascade As Boolean 'Cascading deletes?
    AttribLeftJoin As Boolean 'Left outer join
    End Type 'NewRelation</pre>

    Charlotte

  9. #9
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing DAO Relationships in code - Error 3015 (Access 2K/Win 2K)

    Edited by Charlotte to reduce horizontal scrolling

    Charlotte,

    Just a quick bit of background first!

    I am building a tool for a large number of SAS users, who use PROC SQL to access our TeraData Warehouse (many terabytes in size, over 2 billion records
    within various hierarchies - customer; transaction; product; loyalty event; promotion etc.). The reason for this, is that though they are great analysts, their SQL
    writing skills leave a great deal to be desired!

    This data warehouse is currently going through the first of many business driven changes - lauch of a new loyalty program requiring changes to the db structure,
    followed by the merging of this, and another (even larger) data warehouse into a complete MIS/CRM solution. I am using access to essentially rebuild the entire
    data warehouse structure via the use of a data-dictionary (and vba to write the CREATE TABLE sql), followed by another table to build the relationships (using vba again).

    To demonstrate, here are a few entries for the Datadictionary table:


    <table border=1>


    <td>Table</td><td>ID</td><td>Field</td><td>PrimaryKey</td><td>ios_contact_history</td><td>0</td><td>known_individual_id</td><td>yes</td><td>loyalty_rc_transfer</td><td>0</td><td>rc_party_account_id</td><td>Yes </td><td>loyalty_rc_transfer</td><td>1</td><td>lms_party_account_id </td><td>Yes </td><td>loyalty_rc_transfer </td><td>2</td><td>linked_ind</td><td>No</td><td>vw_account_bhvr </td><td>0</td><td>account_ID</td><td>Yes</td></table>

    I then use the following code to build the tables:

    <pre>Function Create_All_Tables()

    On Error GoTo Create_All_Tables_Error

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim dropSQL As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("del_tables")
    With rs
    If Not .EOF Then
    .MoveLast
    Forms!frmTable_Builder!ctlProgBar.Max = rs.RecordCount + 1
    Forms!frmTable_Builder!ctlProgBar.Value = 0
    .MoveFirst
    Do Until .EOF
    Forms!frmTable_Builder!Combo0.Value = "Deleting " & ![Name] & "..."
    Forms!frmTable_Builder!ctlProgBar.Value = _
    Forms!frmTable_Builder!ctlProgBar.Value + 1
    Forms!frmTable_Builder.Repaint
    dropSQL = "DROP TABLE " & ![Name] & ";"
    DoCmd.RunSQL dropSQL
    Table_Deleted_Move_Next:
    .MoveNext
    Loop
    End If
    End With
    Set rs = db.OpenRecordset("lst_tables")
    With rs
    If Not .EOF Then
    .MoveLast
    Forms!frmTable_Builder!ctlProgBar.Max = rs.RecordCount + 1
    Forms!frmTable_Builder!ctlProgBar.Value = 0
    .MoveFirst
    Do Until .EOF
    Forms!frmTable_Builder!Combo0.Value = "Building " & !<!t>[table]<!/t> _
    & "..."
    Forms!frmTable_Builder!ctlProgBar.Value = _
    Forms!frmTable_Builder!ctlProgBar.Value + 1
    Forms!frmTable_Builder.Repaint
    Create_Table (!<!t>[table]<!/t>)
    .MoveNext
    Loop
    Forms!frmTable_Builder!Combo0.Value = "Creating Relationships..."
    If Create_Relationships Then
    Forms!frmTable_Builder!ctlProgBar.Value = _
    Forms!frmTable_Builder!ctlProgBar.Value + 1
    MsgBox "All Tables & Relationships Built", vbOKOnly + vbInformation, "Build Tables"
    Else
    MsgBox "All Tables Built! " & vbCrLf & "Check Status of Individual Relationships", _
    vbOKOnly + vbInformation, "Build Tables"
    End If
    Forms!frmTable_Builder!Combo0.Value = ""
    Forms!frmTable_Builder!ctlProgBar2.Visible = False
    End If
    End With

    Set rs = Nothing

    Create_All_Tables_Exit:
    Exit Function

    Create_All_Tables_Error:
    Select Case Err.Number
    Case 3167
    Resume Table_Deleted_Move_Next
    Case 3281
    Dim rel As DAO.Relation
    'Dim j As Integer
    For Each rel In db.Relations
    db.Relations.Delete (rel.Name)
    Next rel
    Resume
    Case Else
    MsgBox "Error: " & Err.Description & _
    " (" & Err.Number & ") "
    Resume Create_All_Tables_Exit
    End Select
    End Function


    Function Create_Table(strSource As String)

    Dim db As Database
    Dim rs1 As String
    Dim rsKeys As String
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim strSQL As String
    Dim PKeys As Boolean
    Dim MultiPKeys As Boolean
    Dim strPkeys As String

    Set db = CurrentDb
    Forms!frmTable_Builder!ctlProgBar2.Max = 4
    Forms!frmTable_Builder!ctlProgBar2.Value = 0
    'Create a table to hold the specific table structure called 'zz TABLENAME_ddic'
    rs1 = "SELECT MasterDDIC.Tablename, MasterDDIC.ID, MasterDDIC.Field, "
    rs1 = rs1 & "MasterDDIC.Type, MasterDDIC.Size, masterDDIC.pkey "
    rs1 = rs1 & "INTO [zz_" & strSource & "_ddic] "
    rs1 = rs1 & "FROM MasterDDIC "
    rs1 = rs1 & "WHERE (((MasterDDIC.Tablename) = '" & strSource & "')) "
    rs1 = rs1 & "ORDER BY MasterDDIC.ID;"

    DoCmd.RunSQL rs1
    Forms!frmTable_Builder!ctlProgBar2.Value = 1

    'Create a table to hold the primary keys data called 'zz TABLENAME_keys'
    rsKeys = "SELECT Masterddic.Tablename, MasterDDIC.ID, MasterDDIC.Field "
    rsKeys = rsKeys & "INTO [zz_" & strSource & "_keys] "
    rsKeys = rsKeys & "FROM MasterDDIC "
    rsKeys = rsKeys & "WHERE (((MasterDDIC.Tablename) = '" & strSource _
    & "') AND ((MasterDDic.pkey) = true)) "
    rsKeys = rsKeys & "ORDER BY MasterDDIC.ID;"

    DoCmd.RunSQL rsKeys
    Forms!frmTable_Builder!ctlProgBar2.Value = 2
    'Use zz TABLENAME_ddic and build the final table
    'Open the table specific data dictionary
    Set rs = db.OpenRecordset("zz_" & strSource & "_ddic")
    strSQL = "CREATE TABLE " & strSource & " ("
    With rs
    .MoveFirst
    Do Until .EOF
    strSQL = strSQL & "[" & ![field] & "] " & _
    "TEXT (" & ![Size] & ") , "
    .MoveNext
    Loop
    End With
    strSQL = Left(strSQL, Len(strSQL) - 3) & ");"


    DoCmd.RunSQL strSQL
    Forms!frmTable_Builder!ctlProgBar2.Value = 3
    'Identify if there is a primary key for this table
    'Open the table specific keys list
    Set rs2 = db.OpenRecordset("zz_" & strSource & "_keys")
    With rs2
    .MoveFirst
    If Len(![field]) > 0 Then 'check to see if there is an entry in the table
    PKeys = True 'if so, set the indicator to true
    .MoveNext
    If Not (.EOF) Then
    'If Len(![field]) > 0 Then 'check to see if there is a second entry in the table
    MultiPKeys = True 'if so, set the multifields indicator to true
    Else
    MultiPKeys = False
    End If
    Else
    PKeys = False
    End If
    End With
    'start building the primary keys details
    If PKeys Then 'there is at least one field set as a primary key element
    If MultiPKeys Then 'there are at least two fields set as primary key elements
    strPkeys = "CREATE INDEX PrimaryKey on " & strSource & "("
    With rs2
    .MoveFirst
    Do Until .EOF
    strPkeys = strPkeys & "[" & !field & "], "
    .MoveNext
    Loop
    strPkeys = Left(strPkeys, Len(strPkeys) - 2) & ") WITH PRIMARY; "
    End With
    Else
    strPkeys = "CREATE INDEX PrimaryKey on " & strSource & "("
    With rs2
    .MoveFirst
    strPkeys = strPkeys & "[" & !field & "]) WITH PRIMARY;"
    End With
    End If
    DoCmd.RunSQL strPkeys
    Forms!frmTable_Builder!ctlProgBar2.Value = 4
    End If

    End Function</pre>



    The final stage of this code (when building all tables) is to create the relationships - the relationships table holds four fields, Left Table, Left Field,
    Right Table and Right Field

    The code I use to build the relationships is:

    <pre>Function Create_Relationships() As Boolean

    Dim db As Database
    Dim rel As DAO.Relation
    Dim fld As DAO.field
    Dim relName As String
    Dim relCntr As Integer

    Dim curLeftTab As String
    Dim curRightTab As String
    Dim SameRelationship As Boolean

    Dim rs As DAO.Recordset

    On Error GoTo Create_Relationships_Err

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tmpRelationships ORDER BY lefttable, righttable;")
    With rs
    .MoveLast
    Forms!frmTable_Builder!ctlProgBar2.Value = 0
    Forms!frmTable_Builder!ctlProgBar2.Visible = True
    Forms!frmTable_Builder!ctlProgBar2.Max = .RecordCount
    .MoveFirst
    curLeftTab = ""
    curRightTab = ""
    relName = ""
    Do Until .EOF
    If (!LeftTable = curLeftTab) And (!RightTable = curRightTab) Then
    SameRelationship = True
    Else
    SameRelationship = False
    End If
    If Not (SameRelationship) Then
    curLeftTab = !LeftTable
    curRightTab = !RightTable
    If Len(relName) > 1 Then
    db.Relations.Append rel
    End If
    relName = curLeftTab & " TO " & curRightTab
    Debug.Print "Building Relationship: " & vbTab & relName
    Forms!frmTable_Builder!Combo0.Value = "Building " & relName & "..."
    Forms!frmTable_Builder.Repaint
    Set rel = db.CreateRelation(relName, curLeftTab, curRightTab)
    Set fld = rel.CreateField(![LeftField])
    fld.ForeignName = ![RightField]
    Debug.Print "Left Field: " & vbTab & !LeftField
    Debug.Print "Right Field: " & vbTab & !RightField
    rel.Fields.Append fld
    Else
    Set fld = rel.CreateField(![LeftField])
    fld.ForeignName = ![RightField]
    Debug.Print "Left Field: " & vbTab & !LeftField
    Debug.Print "Right Field: " & vbTab & !RightField
    rel.Fields.Append fld
    End If
    .MoveNext
    Forms!frmTable_Builder!ctlProgBar2.Value = _
    Forms!frmTable_Builder!ctlProgBar2.Value + 1
    Debug.Print "Relationship Entry number: " _
    & Forms!frmTable_Builder!ctlProgBar2.Value _
    & "/"; Forms!frmTable_Builder!ctlProgBar2.Max
    Loop
    End With
    Create_Relationships = True
    Create_Relationships_Exit:
    Exit Function

    Create_Relationships_Err:
    Select Case Err.Number
    Case 3012
    'If the relationship exists,
    'just delete it, and try again!
    db.Relations.Delete rel.Name
    Resume
    Case 3015
    'If an index already exists between
    'the two tables, rebuild the relation name
    rel.Name = curRightTab & " TO " & curLeftTab
    Resume
    Case Else
    MsgBox "Error: " & Err.Description & _
    " (" & Err.Number & ") "
    Create_Relationships = False
    Resume Create_Relationships_Exit
    End Select

    End Function</pre>



    As I said before, the table that is linked to another table twice, keeps failing on Error number 3015 - if you want to test this, you'll need
    to put a break point on the case statement, otherwise the code keeps looping.

    Sorry it's such a long email, but you did ask!!

    Many thanks, Charlotte, for your considerable time and effort once again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •