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?

Robbie

JET 4.0 exists on Windows 7, but only for x86. You need to use ACE to connect to an accdb. And if your C# application is AnyCPU and you're on a 64-bit platform then you need to use the 64-bit version (Access 2010 x64 Database Engine) – Algorism

@Algorism "Microsoft.ACE.OLEDB.12.0" should work as a provider then? – Robbie

If you're on x64 then yes, but you'll need the Access 2010 x64 Database Engine installed. – Algorism

@Algorism I'm using the 2007 version of MS Access. Do I still use the 2010 db engine? – Robbie

If you're on x86 you can use the 2007 version fine. If you're on x64 then you'll need Access 2010 x64. – Algorism

@Algorism Okay, I am on x64. Which mean that I need to get the latest MS Access version to make this work? That doesn't sound right... – Robbie

No, just the 64-bit version of ACE. Your alternative is to build your application for x86. – Algorism

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#

Greg

I'd only add that Windows isn't a real-time operating system. Since the OP is trying to use an Access database under Windows in some sort of process control application, that might be a really big problem. Might not. – Scarface

While it's not supported/recommended, it is in fact possible (at least sometimes) to install the 32-bit and 64-bit ACE drivers on the same machine: conduct the installation from an administrator command prompt and use one or other of the /passive or /quiet switches (depending on version) to force through the installation. I've not seen any problems with both sets of drivers installed on the same machine. – Remembrance

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

Metempirics

Yes, that's the one I used in the second code example. string RSLogixDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\sezettersth\\Documents\\RSLogixDB.accdb;Persist Security Info=False"; That was a very useful site, indeed. – Robbie

Answer 3

I found this for you. Connecting to MS Access (Windows 7)

also this forum thread will help you.

Thanks

Paul.

Moradabad

Whilst this may theoretically answer the question, we would like you to include the essential parts of the linked article in your answer, and provide the link for reference. Failing to do that leaves the answer at risk from link rot. – Oly

Answer 4

I believe that your connection cannot be established because the Jet OLEDB driver does not come with Windows 7, but needs to be installed separately. One way to do this is to install Microsoft Access, another way is to look for the MDAC redistributable package from Microsoft (MDAC = Microsoft Data Access Components).

You can check if - and if yes - which version of MDAC you have by following the instructions on the following link:

http://support.microsoft.com/kb/301202/en-us

Mowery

JET comes with Windows 7, it's part of WDAC. A 64-bit version doesn't come with Windows though, because there's no 64-bit version of JET. – Algorism

Answer 5

As for now (AUG22), you can have both x32- and x64-bit OLEDB/ACE (/MS Access Database Microsoft.Jet.OLEDB.4.0 and Microsoft.ACE.OLEDB.12.0) drivers installed on the same PC:

I have followed the setup instructions from this article "Using Database Libraries with 32-bit and 64-bit Altium Design Software on the same Computer" and they worked well for me for Win10 Professional x64 v.21H2 (Build 19044.1826) with MS Office x32.

I'm using x32 OLEDB drivers with the legacy VS2019 .NET Framework 4.7.x applications' solutions and x64 ACE drivers with the (modern) VS2022 applications' solutions.

Diurnal