1

I got a very old mdb-database. I want to load all tables from the database into a C# DataTable. If possible the final tool should be stand-alone and don't need to install additional software.

Environment

  • .NET 5.0
  • Visual Studio 2019
  • Windows 10 - 64 bit
  • Office 365 - 32 bit

Questions:

  • How does the code for this looks like?
  • Which packages do I have to add/install?
  • Are there other dependencies?
Seraphim
  • 37
  • 6
  • [Microsoft Access Database Engine 2016 Redistributable](https://www.microsoft.com/en-us/download/details.aspx?id=54920) -- [Access connection strings](https://www.connectionstrings.com/access/) – Jimi Sep 12 '21 at 22:17
  • You can pull data from a Access database (and you need the Access data engine installed on that computer to achieve that goal). However, in .net, you THEN need some storage system, or you can continue to use access as the database. If you want to drop Access as the database, then you have no place to store the data, and you need at least some code of data system. If this is to be stand alone desktop, then you could use sqlLite, as that can be used without having to install it on the end computer, but sqlIte does have to be included in your project. So data has to go some place to dump access – Albert D. Kallal Sep 13 '21 at 05:11
  • For the moment I only need to put the data into a DataTable and work with this DataTable. I only have to gather some data out of the tables and put them to a json export, but thats not the clue. Is there no way to get access without the access data engine installed? – Seraphim Sep 13 '21 at 06:18
  • Since you need to generate JSON strings, you can also store the data in JSON format. DataSet and/or DataTable are supported: note that you need to set all values in the first Row of your DataTable(s), otherwise, when deserializing, the data type can/will be misinterpreted (unless you also add a JSON Schema). – Jimi Sep 13 '21 at 13:10
  • yes, to use the Oracle database, you need the Oracle drivers. To use MySQL data, then you need to install MySQL drivers. And to use SQL server data? (well, ok, those ARE installed by default on windows). So yes, as a genreal rule, you need the appropiate drivers installed. So, yes, to open a PDF file, you need PDF software. And to consume access data? Well, then again like everything else? Yes, you need the correct library software to do this (so, yes, of course you need the ACE data engine and drivers installed like any other system you would use. – Albert D. Kallal Sep 13 '21 at 16:37
  • @AlbertD.Kallal and in many cases i just have to just implement a dll instead of installing a third party tool. That's why I'm asking. – Seraphim Sep 13 '21 at 19:18
  • 1
    not aware of any ODBC, or oleDB drivers that are just a .dll. While sqlLite is a simple .exe + .dll, if you want drivers, then you still have to install some ODBC driver to even use that. But yes, you need the ACE data engine installed, same if you need to view a PDF on your computer - you need a PDF viewer. So yes, you need the ACE data engine installed here, and THEN also the oleDB or ODBC drivers. (installing the ACE connectivity pack includes both ACE data engine and the ODBC + oleDB drivers. – Albert D. Kallal Sep 13 '21 at 19:57

1 Answers1

1

Code:

string dbLocation = "DatabaseName.mdb";
string tableName = "TableName";
string connString =  $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbLocation}";

DataTable results = new DataTable();
          
using (OleDbConnection conn = new OleDbConnection(connString))
{
    OleDbCommand cmd = new OleDbCommand($"SELECT * FROM {tableName}", conn);
                
    conn.Open();
    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
    adapter.Fill(results);
}

Conditions:

accessdatabaseengine.exe /quiet
  • Add package System.Data.OleDb
  • Target plattform of the application has to be set to x86/x64 according to the other packages that are used

Still not solved:

  • Actually you need to have Microsoft Access Database Engine Redistributable installed. If there exist any other methode without this, please let me know.
Seraphim
  • 37
  • 6
  • You don't need to have Office installed. The database Engine has no relation to Office. What you have to consider is that, IF an Office installation is present and its Bitness is not the same as your app, you may need to install a version of the Engine that matches your app Bitness. -- You cannot have the same Engine in the x86 and x64 versions installed in the same machine, so you need to install the version you need of another Engine (e.g., if the `ACE.12` is already installed in x86 version and your app needs the x64 version, install the x64 version of `ACE.16`). – Jimi Sep 13 '21 at 14:59
  • OR, replace the connection strings based on the version of the ACE Engine you find already installed in a machine, if any. – Jimi Sep 13 '21 at 15:00
  • @Jimi Thanks, that's good to know. Than I only have to make sure the Redistributable is installed. – Seraphim Sep 13 '21 at 15:03