New Access database, how can it be done?

Question

I have a small project in C# (Microsoft Office Access). I can read and save now. I now need to allow the user to use the new database project but structered like the working one, and also to do "save as".

Besides I need to export to a text file/CSV.

Any idea or sample?

Answer 1

One way to create a blank DB is to try the following

    using System;
    using ADOX;

    public class CreateDB
    {
        public static void Main( string [] args )
        {
            ADOX.CatalogClass cat = new ADOX.CatalogClass();

            string create =
            @"Provider=Microsoft.Jet.OLEDB.4.0;Data
            Source=C:\BlankAccessDB\MyAccessDBCreatedFromCsharp.mdb;" +
            "Jet OLEDB:Engine Type=5";

            cat.Create(create);

            cat = null;
        }
    }

Both Save and SaveAs is as easy as using SaveFileDialog to prompt the user to specify the filename and location to save the file.

Answer 2

The way I did this was to create a new empty access database file (this comes to about 100 KB) and then embed that file as a resource in my application. To "create" a new database is then simply a matter of extracting the resource to a file - which gives you a blank database - and then running a schema update code to create the schema you require in the blank database and then off you go.

I have a project that contains an empty database set to be embedded, a class with one method as below and, er, that's about it.

This is the code to dump the file from the embedded resource - it's not up to date, I wrote it 6 years ago but have had no need to change it:

public void CreateDatabase(string sPath)
{
    // Get the resource and, er write it out?
    System.IO.Stream DBStream;
    System.IO.StreamReader dbReader;
    System.IO.FileStream OutputStream;

    OutputStream = new FileStream(sPath, FileMode.Create);
    Assembly ass = System.Reflection.Assembly.GetAssembly(this.GetType());
    DBStream = ass.GetManifestResourceStream("SoftwareByMurph.blank.mdb");
    dbReader = new StreamReader(DBStream);
    for(int l=0;l < DBStream.Length;l++)
    {
        OutputStream.WriteByte((byte)DBStream.ReadByte());
    }
    OutputStream.Close();
}

Simple, effective and the .dll is 124 KB.

Note I use an utterly blank and empty Access file - attempting to maintain the right schema in the embedded file is going to cause it to grow (because of the way .mdb files work) and may result in shipping data - which probably shouldn't happen. The schema itself is created/updated/maintained by a separate lump of DDL (SQL) that I run from code.

Export to .CSV is moderately trivial to do by hand since you pretty much just need to iterate over the columns in a table but for a smarter approach look at FileHelpers.