How to create an Access database using C# and continuously save real-time data to it

Question

I am currently in the process of making an application that runs 24/7 and constantly collects information from an OPC-sever.

What I need to do now is to send this information to a database (.accdb-file). This need to happen very, very quickly. I fetch a new value from the server every millisecond and I need to send that value to the database at the same speed. Lastly I'm compressing the database to a .zip-file at 00:00 every day.

I have been searching the web like a maniac but I can't seem to find an "Up-to-date" tutorial. Every one I have found so far uses "Microsoft.Jet.OLEDB.4.0" as provider but it doesn't exsist on my Windows 7 PC.

I have made a small application to test connections with and I'll attach my current code bellow to give you a hint of what I have tried so far, none of this seems to work, though.

private void button1_Click(object sender, EventArgs e)
{
    System.Reflection.Assembly oAssembly = System.Reflection.Assembly.GetExecutingAssembly();
    System.IO.Stream oStream = oAssembly.GetManifestResourceStream("RSLogixDB.accdb");
    System.IO.FileStream fileStream = new System.IO.FileStream("C:\\Users\\sezettersth\\Documents\\RSLogixDB.accdb", System.IO.FileMode.Create);

    byte[] abyt = new byte[oStream.Length];
    oStream.Read(abyt, 0, (int)oStream.Length);
    fileStream.Write(abyt, 0, (int)oStream.Length);
    fileStream.Close();
    if (fileStream != null)
    {
        fileStream.Close();
        fileStream = null;
    }
    if (oStream != null)
    {
        oStream = null;
    }
}

This is the first approach I tried, here I use a homemade class that is used for creating and writing to files. It didn't work for .accdb-files, unfotunately. (The comments on the first lines include providers that I have tried.

private void button1_Click(object sender, EventArgs e)
{
    //sFileDia.ShowDialog();
    //Provider=Microsoft.ACE.OLEDB.12.0
    //Povider=.NET Framework Data Provider for OLE DB
    //Povider=Microsoft.Jet.OLEDB.4.0
    //Driver={Microsoft Access Driver (*.mdb, *.accdb)};
    //Data Source=C:\\Users\\sezettersth\\Documents\\RSLogixDB.accdb;
    //Persist Security Info=False;
    //Provider=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\sezettersth\\Documents\\RSLogixDB.accdb;Persist Security Info=False
    string RSLogixDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\sezettersth\\Documents\\RSLogixDB.accdb;Persist Security Info=False";
    string RSLogixDBPath = "C:\\Users\\sezettersth\\Documents\\RSLogixDB.accdb";
    OleDbConnection connection = new OleDbConnection(RSLogixDB);
    string connectionStr = connection.ConnectionString;

    //OleDbDataReader dataReader = new OleDbDataReader(RSLogixDB);
    ImportExport textwriter = new ImportExport();
    textwriter.setExportPath(RSLogixDBPath);
    textwriter.endExport();
}

There has to be a simple solution to this, I can't be the only one using Access without the "Microsoft.Jet.OLEDB.4.0" provider.

(I haven't started the code for zipping the file and if you have an idea on how to do that I'm happy to hear it, but the main focus here is the database issue.)

UPDATE: The problem might not lie on the provider as I initially though, Microsoft.ACE.OLEDB.12.0 allows me to create a .accdb-file but it is just an empty txt-file with a fancy extension basically and it cannot be opened or used with MS Access. How do I make a working .accdb-file?

Answer 1

Regarding your code

To be honest, I'm at a loss at what you are trying to achieve in the code samples you presented.
From what I can see, you are simply creating a file RSLogixDB.accdb.
Maybe I'm missing something, but that's not how you create a database at all.

Have you looked and tried the code in the answers to these SO questions:

• How i Create Access Database at runtime in C#?
• How to create Microsoft Access database in C# programmatically?

Now, some more explanations.

Jet vs ACE

First, the ACCDB file format is the result of a complete revamp of Jet now called ACE. It's only used with Access 2007/2010 and contains new functionalities that are not backward compatible.

To open such a file, you must have either Access 2007/2010 installed (or the free Access Runtime at least) or the specific ACE drivers from Microsoft.

Regarding 32/64 bitness

For the sake of your sanity, regardless of your OS bitness, only use Office and Access 32 bits. Even Microsoft doesn't recommend using the 64 bits version except if you are pushing Excel to its limits.

• you can't mix Office 32 bits and 64 bits applications
• you can't install the 32 bits and 64 bits ACE drivers on the same machine
• Use either the version 2007 or 2010 of the ACE drivers, but avoid having both installed.
• make sure that your .Net application is compiled for the same bitness as Access/ACE driver installed (so you should specifically compile to x86 if you follow the advice above, not AnyCPU!).

Performance

You can get very decent performance with an Access database if you keep a few rules:

• if possible, keep the database on the same machine as the application that will write to it (avoid network slowdowns)

• if your application is the only one using the database, open the database in exclusive mode instead of shared mode:

  // Share Mode=16 - use if multiple users must have simultaneous access to the db
  string constr = @"Provider=Microsoft.ACE.OLEDB.12.0;Mode=16;Data Source=C:\...\RSLogixDB.accdb;user id=;password=;";
  
  // Share Mode=12 - exclusive mode if only your app needs access to the db
  string constr = @"Provider=Microsoft.ACE.OLEDB.12.0;Mode=12;Data Source=C:\...\RSLogixDB.accdb;user id=;password=;";
  

• open a dummy connection to the database when your application starts and keep it open until you don't need the database (to zip it for instance or when your app shuts down).
  This is actually a very important performance improvement: opening a new connection to an Access database is expensive because the driver need to create/destroy/update the .accdl lock file.
  If you keep an always-open connection to the database, you will greatly speed-up your read/writes to it. For more info on this subject, please check this article:
  Where is the OLE DB connection pooling?

Back to the task

The way I would do what you are trying to achieve:

• Use Access to manually create a simple database RSLogixDB.accdb with the table that would hold the data.
  Also create a dummy table in it with little or no data. We'll use it to keep the connection open and avoid the lock file performance issues.

• If you need to re-create the database every day from scratch, then keep a copy of that original database as a template, then copy that template and fill the copy with the new daily data.

• If you're the only user of the db, open it in exclusive mode.

• Open a connection and read data from the dummy table, but don't close it. Keep it open.

• save data to your data table all day long

• at midnight, close the database (close the dummy table), make sure the lock file .accdl has disapeared then zip the database.
  Replace the database by the template if you need to start with an empty db.
  I would also compact the database to reduce its size before zipping it.

• If the standard performance of ADO.Net data access is insufficient for your case, have a look at using the native DAO routines. Check out the answer to this SO question:
  Writing large number of records (bulk insert) to Access in .NET/C#

Answer 2

If you ever need the connection string for connecting to a particular database then the first stop should always be http://www.connectionstrings.com/

For Access you'd probably find this page helpful: http://www.connectionstrings.com/access-2007