establishing relationships with in the ms access database

[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(one-many) between the tables with in the database but not in
the dataset.

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

Thank you Sir.

 

[Roger Carlson]

Personally, I prefer to use SQL statements for creating tables and
relationships. For instance, suppose I want to create a Books table and a
Publishers table and then create a one-to-many relationship based on the
common "PubID" field. I would do something like this:

Dim strSQL As String
'create table "Publishers"
strSQL = "CREATE TABLE PUBLISHERS "
strSQL = strSQL & "(PubID TEXT(10) CONSTRAINT PrimaryKey PRIMARY KEY, "
strSQL = strSQL & "PubName TEXT(100), "
strSQL = strSQL & "PubPhone TEXT(20));"
cn.Execute strSQL

'create table "Books"
'
strSQL = "CREATE TABLE BOOK "
strSQL = strSQL & "(ISBN TEXT(13) CONSTRAINT PrimaryKey PRIMARY KEY, "
strSQL = strSQL & "Title TEXT(100), "
strSQL = strSQL & "Price MONEY, "
strSQL = strSQL & "PubID TEXT(10)) "
cn.Execute strSQL

'Creates a relationship between two tables: "Books" and "Publishers"

strSQL = "ALTER TABLE Books "
strSQL = strSQL & " ADD CONSTRAINT Books FOREIGN KEY (PubID) REFERENCES
Publishers (PubID);"
cn.Execute strSQL

I think it is much cleaner and easier to read.

An excellent reference for this SQL DDL (Data Definition Language) is
"Access Database Design and Programming" by Steve Roman and published by
O'Reilly.