Create an Access DB from .Net?

[Amelinckx]

Hi,
How could you create an Access DB from using native .Net
code? I mean, a new file with some tables and relations.
Thanks,
Christian.

 

[Paul Clement]

¤ 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)