Establishing relationships

G

gaffar

Sir,

Below code(vb.net source code) is used to create a ms access database,
tables and assigning primary keys. now i want the code to establish
relationship between the tables.
waiting for ur reply


Thanking u sir.

'creating tables in the database

'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection
Dim Cat2 As ADOX.Catalog
Dim objTable1, objtable2, objtable3, objtable4 As ADOX.Table
Dim objkey1 As ADOX.Key

Cn = New ADODB.Connection
Cat2 = New ADOX.Catalog
objTable1 = New ADOX.Table
objtable2 = New ADOX.Table
objtable3 = New ADOX.Table
objtable4 = New ADOX.Table

objkey1 = New ADOX.Key

'Open the connection


Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & Application.StartupPath &
"\databases" & "\" & fname1 & ";" & "Jet OLEDB:Engine Type=5")

'"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\sample\gaffar.mdb"

'Open the Catalog
Cat2.ActiveConnection = Cn

'Create the table
objTable1.Name = "new_custdetails"
objtable2.Name = "contact_note"
objtable3.Name = "contact_salesopportunity"
objtable4.Name = "contact_activities"


'Create and Append a new field to the "new_custdetails" Columns
Collection


objTable1.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("salutation", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("title", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("phone", DataTypeEnum.adInteger)
objTable1.Columns.Append("phext1", DataTypeEnum.adInteger)
objTable1.Columns.Append("rec_creator", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("rec_mgr", DataTypeEnum.adVarWChar)

'Append the newly created table to the Tables Collection

objkey1.Name = "PrimaryKey"
objkey1.Type = KeyTypeEnum.adKeyPrimary
objkey1.Columns.Append("company")
objkey1.Columns.Append("contact")
objkey1.Columns.Append("dept")

Cat2.Tables.Append(objTable1)
Cat2.Tables.Append(objtable2)
Cat2.Tables.Append(objtable3)
Cat2.Tables.Append(objtable4)

' clean up objects
' objKey = Nothing

objTable1 = Nothing
objtable2 = Nothing
objtable3 = Nothing
objtable4 = Nothing
Cat2 = Nothing
Cn.Close()
Cn = Nothing
 
C

Cor Ligthert

Gaffar,

A very simple and quick made sample using ADONET and VBNet

\\\\
Dim Sql As String = "SELECT * from A, B Where " & _
"A.n = B.n"
Dim Conn As New OleDbConnection(connString)
Dim da As New OleDbDataAdapter(Sql, Conn)
dim ds as new DataSet
da.Fill(ds, "A")
da.Fill(ds, "B")
Conn.Close()
Dim drlA As New DataRelation _
("AA", ds.Tables("A").Columns("A.n"), _
ds.Tables("B").Columns("B.n"))
ds.Relations.Add(drlA)
DataGrid1.DataSource = ds
DataGrid1.Expand(-1)
////
 
N

Nick Malik [Microsoft]

Hi Cor,

I'm not certain, but I think the OP may have wanted to know how to create
referential integrity links in the database itself.

Your response creates a Data Relation in a dataset, but doesn't affect the
database at all.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
 
N

Nick Malik [Microsoft]

Use the Keys collection to add a key to a table object. Make the Key a
foreign key and use the .RelatedTable property to establish the
relationship.

You can usually add the keys after you define the table.

See
http://msdn.microsoft.com/library/en-us/ado270/htm/adobjkey.asp

Good example at:
http://msdn.microsoft.com/library/en-us/ado270/htm/admsckeycreationexample.asp

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
 
C

Cor Ligthert

Nick,

Maybe, he has dumbed a lot of messages, crossposted and individual
multiposted to at least the newsgroup languages.vb.

However only with this code or with text as "help me" while he did not give
any more explanation and everytime creates a new message.

Cor
 
N

Nick Malik [Microsoft]

Hi Cor,

One thing I've discovered... if someone keeps asking the same question over
and over, it's because he or she either doesn't understand the answers, or
the answers don't really address his or her question.

I'm assuming the latter.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
 
C

Cor Ligthert

Nick,

I know, however I have (as far as I remember me) asked politly if he could
reply in the original thread all the time (except the last one).

And watched if there would come an reaction.

It seems for me not right to start every time new again.

Cor
 
O

Otis Mukinfus

Gaffar,

A very simple and quick made sample using ADONET and VBNet

\\\\
Dim Sql As String = "SELECT * from A, B Where " & _
"A.n = B.n"
Dim Conn As New OleDbConnection(connString)
Dim da As New OleDbDataAdapter(Sql, Conn)
dim ds as new DataSet
da.Fill(ds, "A")
da.Fill(ds, "B") ***********************
Conn.Close()
Click to expand...
***********************
It is not necessary to close a connection for a DataAdapter object. the
connection is opened and closed automatically by the DataAdapter object.
Dim drlA As New DataRelation _
("AA", ds.Tables("A").Columns("A.n"), _
ds.Tables("B").Columns("B.n"))
ds.Relations.Add(drlA)
DataGrid1.DataSource = ds
DataGrid1.Expand(-1)
////
Click to expand...

I'm not sure you answered Gaffar's question at all. He wanted to know how to
create a relationship between two Access tables using ADOX.

here is VB code to create a relationship using ADOX:

Dim table As New ADOX.Table
Dim company As New ADOX.Table
Dim fkey As New ADOX.Key

'Assuming the CompanyID column has been defined within the company table:

fkey.Name = "yourName"
fkey.Type = adKeyForeign
fkey.Columns.Append "fkCompanyID"
fkey.Columns("fkCompanyID").RelatedColumn = "CompanyID"
fkey.RelatedTable = "company"
table.Keys.Append fkey



Otis Mukinfus
http://www.otismukinfus.com
 
G

Guest

Sir,

Below code(vb.net source code) is used to create a ms access database,
tables and assigning primary keys. now i want the code to establish
relationship between the tables.
waiting for ur reply


Thanking u sir.

'creating tables in the database

'
' This code adds a single-field Primary key
'
Dim Cn As ADODB.Connection
Dim Cat2 As ADOX.Catalog
Dim objTable1, objtable2, objtable3, objtable4 As ADOX.Table
Dim objkey1 As ADOX.Key

Cn = New ADODB.Connection
Cat2 = New ADOX.Catalog
objTable1 = New ADOX.Table
objtable2 = New ADOX.Table
objtable3 = New ADOX.Table
objtable4 = New ADOX.Table

objkey1 = New ADOX.Key

'Open the connection


Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & Application.StartupPath &
"\databases" & "\" & fname1 & ";" & "Jet OLEDB:Engine Type=5")

'"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=d:\sample\gaffar.mdb"

'Open the Catalog
Cat2.ActiveConnection = Cn

'Create the table
objTable1.Name = "new_custdetails"
objtable2.Name = "contact_note"
objtable3.Name = "contact_salesopportunity"
objtable4.Name = "contact_activities"


'Create and Append a new field to the "new_custdetails" Columns
Collection


objTable1.Columns.Append("company", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("contact", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("salutation", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("title", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("dept", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("phone", DataTypeEnum.adInteger)
objTable1.Columns.Append("phext1", DataTypeEnum.adInteger)
objTable1.Columns.Append("rec_creator", DataTypeEnum.adVarWChar)
objTable1.Columns.Append("rec_mgr", DataTypeEnum.adVarWChar)

'Append the newly created table to the Tables Collection

objkey1.Name = "PrimaryKey"
objkey1.Type = KeyTypeEnum.adKeyPrimary
objkey1.Columns.Append("company")
objkey1.Columns.Append("contact")
objkey1.Columns.Append("dept")

Cat2.Tables.Append(objTable1)
Cat2.Tables.Append(objtable2)
Cat2.Tables.Append(objtable3)
Cat2.Tables.Append(objtable4)

' clean up objects
' objKey = Nothing

objTable1 = Nothing
objtable2 = Nothing
objtable3 = Nothing
objtable4 = Nothing
Cat2 = Nothing
Cn.Close()
Cn = Nothing
Click to expand...

User submitted from AEWNET (http://www.aewnet.com/)
 
C

Cor Ligthert

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

cannot contain a null value because the required property for this field is set to true. 1
cannot contain a null value because the required property for this field is set to true 1
Establishing relationship 3
establishing relationships with in the ms access database 1
establishing the relationship between the tables in the ms acess database 1
How to establish realtionships betweeen the tables through my vb.net application at run time. 1
establishing realtionships by using ADOX 3
how to establish a relationships between tables from the vb.net application at run time 4

Top