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.

I haven't ignored my question. I just have work items with more priority. Once I start implementing this, I will select the answer that helped me. — Jeremy, Oct 06 '10 at 13:01

score 1 · Accepted Answer · answered Oct 13 '10 at 15:58

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.

score 0 · Answer 2 · answered Oct 01 '10 at 08:05

It is possible to create an access database by code, either with DDL instructions of by manipulating ADO or DAO objects.

I doubt that DDL T-SQL code can be of any use to create an Access database: lots of instructions will not be understood in Access, from field types to indexes and constraints.

One option would be to use ADODB connections to connect both to the original SQL database and the newly created Access database (see #HansUp proposal), and use ADOX Object Model. You'll then be able to 'read' the SQL database (ie the ADOX Catalog) and its objects and recreate objects with 'similar' properties on the Access side: tables, fields, indexes, relations, etc.

Something similar could be done with DAO Object Model, but I guess it will be easier with ADOX.

Another option would be to check if existing softwares can do the trick. EMS SQL Manager is an option.

How to create Access database from a script

2 Answers2