Tool to convert MS-Access Database to SQL script

Question

Hi,

Our Software Package uses MS-Access Database, this database has been modified many times, so, it's a bit complicated to just see the files (relationship between tables are small hell), anyway,

Is there a way or program to convert this mdb file to SQL Script (Any sort of SQL Script will do it for now).

Thanks,

Answer 1

Have you looked at the SQL Server Migration for Microsoft Access?

If you convert a copy to SQL Server, you may be able to get a better feel for the database.

Answer 2

See this thread: Is there any way to have MS Access 2003 generate a set of DDL, DML, and DCL files for an existing design and dataset?

Answer 3

SubSonic has options for dumping the database's design - as well as its' data - into SQL. I have found it incredibly handy in the past for backing up my test database to source code control.

Download and install version 2.1 of SubSonic.

You can run the generation script entirely from the command line, but I personally find it easier to work with an App.Config file to be able to fiddle with the config options more easily. Setup a .Net project, and setup the App.Config file to the appropriate settings. Documentation on App.Config can be found here: http://subsonicproject.com/configuration/config-options/

Here is a sample from an old project of mine:

<?xml version="1.0" encoding="utf-8" \>
<configuration>
    <configSections>
        <section name="SubSonicService" 
            type="SubSonic.SubSonicSection, SubSonic" 
            requirePermission="false"/>
    </configSections>

    <connectionStrings>
     <clear />
        <add name="subTest" 
            connectionString="Data Source=MYCOMP\MICROSOFTSMLBIZ\,56183;Database=someDB;User ID=someId;Password=somePasswrd!"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

    <SubSonicService defaultProvider="subTest">
        <providers>
            <clear/>
            <add name="subTest" 
                type="SubSonic.sqlDataProvider, SubSonic" 
                connectionStringName="subTest"
                generatedNamespace="TerraCognita.Project.Services.DAL"
                includeTableList="^Bug$, ^Person$, ^Groups$, ^Project$, ^TimeInterval$"/>
        </providers>
    </SubSonicService>
</configuration>

Use a connection string appropriate for your DB, and (I believe) a provider appropriate for an Access database. (You can also specify most parameters at the command line, but I personally find it more straightforward to work with the config file.)

Open a command prompt, the current folder should be the folder which has App.Config. The following is the command you need:

[Install Folder]\SubCommander\sonic.exe [Command] /out [OutputFileName]

Where [Command] can be scriptschema or scriptdata. (There are additional commands, but these are the two you will be interested in. Help is also an option.

Answer 4

You can take a look at Database Comparer

Answer 5

I think MDBScript can fully satisfy your needs.