Do we need to use both ADODB and OLEDB?

Question

I am new to C# and I am developing an application that uses a small Access database. I tried to understand what to use, ADODB or OLEDB, but it seems I was unable to solve the question.

So, is it possible use only one of these libraries? Or it is normal use both?

For example, the usual stuff with databases:

string MyQuery = "SELECT * FROM MyTable";
Recordset rs = new Recordset();
rs.Open(MyQuery, MyConnection, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic);

uses all ADODB objects, until:

OleDbDataAdapter adapter = new OleDbDataAdapter();
DataTable dt = new DataTable("MyTable");
adapter.Fill(dt, rs);

so I feed a DataGridView with the Datatable. The adapter is OLEDB, not ADODB. Is there a way to avoid OLEDB? So, I thought I got rid of ADODB and do everything in OLEDB, all went well until I wanted add tables at runtime:

ADOX.Catalog MyCat = new ADOX.Catalog();
MyCat.ActiveConnection = MyConnection;
ADOX.Table table = new ADOX.Table();
table.Name = "MyTable";
table.Columns.Append("ID", ADOX.DataTypeEnum.adInteger);
table.Columns["ID"].ParentCatalog = MyCat;
table.Columns["ID"].Properties["AutoIncrement"].Value = true;
table.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID");
table.Columns.Append("DateAdded", ADOX.DataTypeEnum.adDate);
table.Columns.Append("Name");
table.Columns.Append("Surname");
cat.Tables.Append(table);

where the catalog absolutely refuses an OLEDB connection and demands an ADODB one so I needed to rescue ADODB.

So, in the end, do I need to resign to use both or I am doing something wrong in the code samples I posted? Alternatives are welcomed, thanks in advance.

It would very nice for a newbie know which system to use and stick with it, knowing it can do everything it needs to be done.

Answer 1

Taken from this answer by an MCC, MVP:

OLEDB is a Microsoft standard that defines a set of API (Application Interface) functions for accessing a database. It is a COM (Component Object Model) API that was a follow-up to the ODBC API. Typically OLEDB is used to create a database specific driver, known as a provider, that can be implemented by a higher level data access library such as ADO or ADO.NET.

ADO (ADODB) is a generic (COM) database library, that can be used by programming languages such as Visual Basic and C++ to access any type of database for which an OLEDB Provider has been developed. In this context, ADO is an OLEDB Consumer. It communicates with the OLEDB Provider, which in turn communicates with the database directly or a database server.

For example, to open an Access database, the Connection object of ADODB would specify the Jet OLEDB Provider in its connection string to open and subsequently communicate with the database:

ADODB.Connection cnn;

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source=E:\My Documents\db1.mdb;" +
                            "Jet OLEDB:Engine Type=5";
cnn.Open();

To me this implies that unless you have a specific ADO provider you want to use it would be better / quicker to use OLEDB directly.

However OLE may require more development time, although ADO will be quicker to develop in but slower in performance than OLE.

Some leading questions: What is your connection string? (Your problem implies that you are using an ADODB provider in your connection string). What is ADOX ?

P.S. Some reference material for you:

Microsoft Access Performance Tips to Speed up Your Access Databases

Access connection strings

EDIT: Taken from here: "With version 2007 onwards, Access includes an Office-specific version of Jet, initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine. This engine is fully backward-compatible with previous versions of the Jet engine, so it reads and writes (.mdb) files from earlier Access versions. It introduces a new default file format, (.accdb), that brings several improvements to Access, including complex data types such as multivalue fields, the attachment data type and history tracking in memo fields. It also brings security and encryption improvements and enables integration with Microsoft Windows SharePoint Services 3.0 and Microsoft Office Outlook 2007"

Also you may run into 64 bit issues with Jet...

"The driver is not part of the Windows operating system, but is available as a redistributable.[11] Previously the Jet Database Engine was only 32-bit and did not run natively under 64-bit versions of Windows."

For how to work with MS Access via C# have a read of this good tutorial If you scroll down to the section with the title "Access (accdb)" you will see the code uses ADOX.catalog.

EDIT2: For info on ADOX.Catalog read this "Open the catalog by setting the ActiveConnection property to an ADO Connection object or a valid connection string."

So try something like this... Taken from the post underneath the accepted answer by Gord Thompson

ADOX.Catalog cat;
ADOX.Table tbl;

cat.ActiveConnection = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Users\Public\Database1.accdb;"