Creating Relationships in Access

Question

I got two tables in my access file. and I would like to create relationship between them. The following diagram is the relationship I created manually in Access.

enter image description here

However, I want to create the relationships in VB.net and here is my code:

    conn.Open()

    daBooks = New OleDb.OleDbDataAdapter("SELECT * FROM Books", conn)
    daAuthor = New OleDb.OleDbDataAdapter("SELECT * FROM  authors", conn)

    daBooks.Fill(ds, "Books")
    daAuthor.Fill(ds, "authors")

    conn.Close()

    'Set the relation
    Dim parentColumn As DataColumn
    parentColumn = ds.Tables("authors").Columns("AuthorID")

    Dim childColumn As DataColumn = New DataColumn
    Try
        childColumn = ds.Tables("Books").Columns("AuthorID")
    Catch ex As Exception
        MsgBox(ex.Message)
        Exit Sub
    End Try

    Dim a As String
    a = ds.Tables("authors").Rows(0).Item("AuthorID")
    Dim b As String
    b = ds.Tables("Books").Rows(0).Item("AuthorID")

    Dim relation As DataRelation = New  _
           System.Data.DataRelation("Books_Authors", parentColumn, childColumn)
    ds.Relations.Add(relation)

    RelationName.Text = relation.RelationName
    'End of setting relation

    Dim cb1 As New OleDb.OleDbCommandBuilder(daBooks)
    Dim cb2 As New OleDb.OleDbCommandBuilder(daAuthor)

    Try
        daBooks.Update(ds, "books")
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

    daAuthor.Update(ds, "authors")

However after I ran the code, it couldn't change the database. Can anyone help me with this so that I can create a new relationship for two tables in VB.NET.

Generally I think the problem is that the System.Data.DataRelation and ds.Relations.Add(relation) just create the relationship for the dataset but it hasn't been updated to the database through dataadapter or something else. Am I correct by saying so or it's because of other reasons. If I'm correct, then how to update the dataset to database?

Do you want Interop (wiki.lessthandot.com/index.php/Access,_Script_and_Relationships ) or ADOX ( allenbrowne.com/func-adox.html#CreateKeyAdox )? — Fionnuala
– Fionnuala, Commented Mar 31, 2014 at 12:45

HansUp · Accepted Answer · 2014-03-31 13:44:04Z

2

You can create that relationship by executing an ALTER TABLE statement from your OleDb connection.

ALTER TABLE Books
ADD CONSTRAINT BooksRelationship
FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID);

answered Mar 31, 2014 at 13:44

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

2342G456DI8 Over a year ago

Can you be more specific? How to put it in the OleDb connection. THanks very much.

HansUp Over a year ago

I assumed you already have a usable OleDb connection. What is conn in conn.Open()?

2342G456DI8 Over a year ago

dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0; " dbSource = "Data Source = " + Directory.GetCurrentDirectory() + "\JustForFun.accdb" conn.ConnectionString = dbProvider & dbSource

HansUp Over a year ago

I don't know VB.Net. But it looks to me like should create an OleDbCommand object which uses your connection, and then execute the SQL statement from the command object's ExecuteNonQuery method.

2342G456DI8 Over a year ago

I didn't use sqlconnection,I used the OleDB connection, I tried the same way as I updated the database which adds a new row. But it couldn't work still.

Code Pope · Accepted Answer · 2014-03-31 13:21:03Z

You can create a macro in Access that creates a relationship between your tables and run it via VB.NET.
Here is a function to create a relation in MS Access:

Public Function MacroCreateRelation()

Dim db As DAO.Database

CreateRelation("Author", "IdAuthor", _
                           "Book", "IdAuthor")

Set db = Nothing  
End Function


Private Function CreateRelation(primaryTblName As String, _
    primaryFieldName As String, _
    foreignTblName As String, _
    foreignFieldName As String) As Boolean
On Error GoTo ErrHandler

Dim myDB As DAO.Database
Dim newRelation As DAO.Relation
Dim relatingField As DAO.Field
Dim relationName As String

relationName = primaryTblName + "_" + primaryFieldName + _
    "__" + foreignTblName + "_" + foreignFieldName

    Set myDB = CurrentmyDB()

    ' First create the relation
    Set newRelation = myDB.CreateRelation(relationName, _
        primaryTblName, foreignTblName)
    'field of the primary table
    Set relatingField = newRelation.CreateField(primaryFieldName)
    'Then the field of the the second table
    relatingField.ForeignName = foreignFieldName

    'now just add the field to the relation
    newRelation.Fields.Append relatingField

    'Last but not least add the relation to the db
    myDB.Relations.Append newRelation

    Set myDB = Nothing

    return True

Exit Function

    ErrHandler:
    Debug.Print Err.Description + " [ " + relationName + "]"
    return False

End Function

Then you have just to call the macro from VB.NET.

Collectives™ on Stack Overflow

Creating Relationships in Access

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related