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

You can use DDL to create tables, but as far as I know Access' (Jet/ACE) database engine does not support a CREATE DATABASE statement. You can use DAO to create the new database.

Public Function CreateEmptyDB(ByVal pstrNewDbPath As String) As Boolean
    Dim db As DAO.Database
    Dim blnRetValue As Boolean
    blnRetValue = False

    'dbVersion40 => 2002-2003 format '
    Set db = DBEngine.CreateDatabase(pstrNewDbPath, dbLangGeneral, dbVersion40)
    db.Close
    Set db = Nothing
    blnRetValue = True
    CreateEmptyDB = blnRetValue
End Function

Once you've created the database, you can execute "CREATE TABLE" statements.

But if you already have an existing SQL Server database you want to re-create in Access, it might be easier to import the tables with an ODBC connection to SQL Server. When importing you can select tables with their data, or only the table structures.

Update: Using VBA, DoCmd.TransferDatabase can import a table from an ODBC database. This example was adapted from a sample in Access' TransferDatabase help topic:

DoCmd.TransferDatabase acImport, "ODBC Database", _
    "ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
    & "DATABASE=pubs", acTable, "Authors", "dboAuthors", True

I added True as the StructureOnly parameter to import the table structure without the data. Change it to False if you want to import the data as well.

Answer 2

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.

Answer 3

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.