How to create Access database from a script

Question

I would like to create an Access database from a script. Ideally, I would love something similar to the way SQL scripts can be used to create a SQL database.

Is this possible at all? Is there anything that would leverage the SQL scripts I already have? I want to create the same database structure as my SQL database.

Let me know if you need me to clarify anything. Thanks.

Answer 1

I ended up going with my own solution. I could not get either of the first two to work very well. I created two executables: one to create the database and one to run scripts.

For the application that creates the database, I added the COM reference "Microsoft ADO Ext. 2.8 for DDL and Security". The code is actually quite simple: (Replace "test.mdb" with the proper file path for your file.)

Dim cat As ADOX.Catalog = New ADOX.Catalog()
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb;Jet OLEDB:Engine Type=5")

To run the scripts, I created a simple parser to read and run "Access scripts". These scripts are based on SQL scripts, in that they provide a list of commands to run. For example, a script might be defined as:

--Create the table.
CREATE TABLE [Test] ([ID] Number, [Foo] Text(255))

--Add data to the table.
INSERT INTO [Test] ([ID], [Foo]) VALUES (1, 'Bar')

Here is the code for the parser. (Replace "test.mdb" with the proper file path for your file.)

Dim textStream = File.OpenText(scriptPath)
Dim lines As List(Of String) = New List(Of String)
While textStream.Peek() <> -1
    lines.Add(textStream.ReadLine())
End While
textStream.Close()

Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb")
connection.Open()
For Each line As String In lines
    If Not String.IsNullOrEmpty(line) Then
        If Not line.StartsWith("--") Then
            Dim dbCommand = New OleDb.OleDbCommand(line, connection)
            dbCommand.ExecuteNonQuery()
        End If
    End If
Next
connection.Close()

This solution works well and was pretty simple to implement.