¤ Hi,
¤ How could you create an Access DB from using native .Net
¤ code? I mean, a new file with some tables and relations.
There is no native .NET support for creating Access databases. However, you can use ADOX (Microsoft
ADO 2.x for DDL and Security) to do this. Here are a couple of examples:
Private Sub CreateAccess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles CreateAccess.Click
Dim tbl As New ADOX.Table()
Dim col As New ADOX.Column()
Dim cat As New ADOX.Catalog()
'Engine Type=4 is Access 97 and a value of 5 is Access 2000
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")
tbl.Name = "NewTable"
col.Name = "AutoField"
col.Type = ADOX.DataTypeEnum.adInteger
col.ParentCatalog = cat
col.Properties("AutoIncrement").Value = True
tbl.Columns.Append(col)
col = New ADOX.Column()
col.Name = "DateField"
col.Type = ADOX.DataTypeEnum.adDate
tbl.Columns.Append(col)
col = New ADOX.Column()
col.Name = "Address2"
col.Type = ADOX.DataTypeEnum.adVarWChar
col.DefinedSize = 20
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable
tbl.Columns.Append(col)
col = New ADOX.Column()
col.Name = "Age"
col.Type = ADOX.DataTypeEnum.adInteger
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable
tbl.Columns.Append(col)
cat.Tables.Append(tbl)
cat.Tables("NewTable").Columns("Address2").Properties("Jet OLEDB:Allow Zero Length").Value =
True
cat.Tables("NewTable").Columns("AutoField").Properties("AutoIncrement").Value = True
End Sub
Sub CreatePrimaryKeyWADOX()
Dim tbl As New ADOX.Table()
Dim col As New ADOX.Column()
Dim cat As New ADOX.Catalog()
Dim cnn As New ADODB.Connection()
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\My
Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4"
cnn.Open()
cat.ActiveConnection = cnn
Dim pkey As New ADOX.Key()
pkey.Name = "PrimaryKey"
pkey.Type = ADOX.KeyTypeEnum.adKeyPrimary
Dim oTable As New ADOX.Table()
oTable = cat.Tables("Table1")
pkey.RelatedTable = "Table1Copy"
pkey.Columns.Append("record id")
oTable.Keys.Append(pkey)
cat.ActiveConnection = Nothing
cnn.Close()
cnn = Nothing
End Sub
Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)