[RESOLVED] Access 2007 database creation at runtime

[Charlie20]

Hia all,

In the past I've used this code to create a database at runtime :-

       Dim Cat As New Catalog
        Dim Cn As New ADODB.Connection
        Dim objTable As New ADOX.Table

        ' Create Database
        Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Data\MyNewDB.mdb;" & "Jet OLEDB:Engine Type=5")
        Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\MyNewDB.mdb")
        Cat.ActiveConnection = Cn

        ' Create Tables
        objTable.Name = "EmpMas"

        objTable.Columns.Append("column1")

        Cat.Tables.Append(objTable)

        ' Release objects and Free memory
        objTable = Nothing
        Cat = Nothing
        Cn.Close()
        Cn = Nothing

        MsgBox("Created")

I've been unable to find anything similar for OLEDB & .accdb files. Has anyone been working with anything like this that can point me in the right direction ?

Thanks

Charlie

[kareninstructor]

Try this out, requires a reference to ADOX.

Private adoxFileName As String = Application.StartupPath & "\AdoxDatabase.mdb"
Private ConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", adoxFileName)

If Not IO.File.Exists(adoxFileName) Then
    CreateNewAccessDatabase(adoxFileName)
End If

Sub CreateNewAccessDatabase(ByVal FileName As String)
    Dim dbCatalog As New ADOX.Catalog()
    dbCatalog.Create(ConnectionString)
End Sub

[kareninstructor]

Create database, table and populate

If Not IO.File.Exists(adoxFileName) Then
    CreateNewAccessDatabase(adoxFileName)
End If

Using cn As New OleDb.OleDbConnection(ConnectionString)
    Dim cmd As New OleDb.OleDbCommand( _
    <text>
        CREATE TABLE persons ([Identifier] int identity ,
                              UserName NVarchar(50), 
                              [Password] NVarchar(50),
                              CONSTRAINT [pk_AutoId] PRIMARY KEY ([Identifier])) 
    </text>.Value, cn)

    cn.Open()

    Try
        cmd.ExecuteNonQuery()
    Catch ex As OleDb.OleDbException
        MessageBox.Show(ex.Message, "OleDbException")
        Exit Sub
    Catch ex As Exception
        MessageBox.Show(ex.Message, "GeneralException")
        Exit Sub
    End Try

    Application.DoEvents()

    cmd.CommandText = _
    <Text>
        INSERT INTO persons 
        (UserName,[Password])
        VALUES 
        ("Gallagher","pass1word")
    </Text>.Value
    cmd.ExecuteNonQuery()

    cmd.CommandText = _
    <Text>
        INSERT INTO persons 
        (UserName,[Password])
        VALUES 
        ("Jones","Fluffy2")
    </Text>.Value
    cmd.ExecuteNonQuery()

    cmd.CommandText = _
    <Text>
        INSERT INTO persons 
        (UserName,[Password])
        VALUES 
        ("Smith","abv123w")
    </Text>.Value
    cmd.ExecuteNonQuery()

    cmd.CommandText = _
    <Text>
        INSERT INTO persons 
        (UserName,[Password])
        VALUES 
        ("Student1","College")
    </Text>.Value
    cmd.ExecuteNonQuery()

    cmd.CommandText = _
    <Text>
        INSERT INTO persons 
        (UserName,[Password])
        VALUES 
        ("Gallagher","pass1word")
    </Text>.Value
    cmd.ExecuteNonQuery()

    cmd.CommandText = _
    <Text>
        INSERT INTO persons 
        (UserName,[Password])
        VALUES 
        ("Simpson","Homer1")
    </Text>.Value
    cmd.ExecuteNonQuery()
End Using

[Charlie20]

Thats brilliant - thanks !!