Creating Reports from Microsoft Access Databases with List & Label

Knowledgebase English KB
, , , ,
1

Create reports based on your existing Microsoft Access databases with List & Label. Easy connectivity, relational database support, Report Designer included. See how it works here.

How to Create Reports with List & Label Using Data from Microsoft Access Databases

List & Label can be easily bound to Microsoft Access databases. This allows you to create comprehensive reports based on your existing Access data, whether it is in tables or stored procedures. Relational databases are also supported out of the box, enabling you to drill down or drill through your data.

Samples for Binding Microsoft Access Databases to List & Label

The OleDbConnection of the .NET Framework is used as a basis, which uses the Jet OleDb driver to access the Microsoft Access database.

Connect to Access Database

string databasePath = @"c:\temp\Northwind.mdb";
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databasePath;
OleDbConnection myOleDbConnection = new OleDbConnection(connStr);

Note: For 32-bit applications, the Jet OleDB driver can still be used. But for a 64-bit application, this driver reaches its limits and an alternative must be found. For this purpose, Microsoft itself offers the ACE OleDB driver, which is provided as the Microsoft Access Database Engine. Accordingly, connStr must be adapted as follows:

string databasePath = @"c:\temp\Northwind.mdb";
string connStr = "Provider=Microsoft.Jet.ACE.OLEDB.12.0;Data Source=" + databasePath;
OleDbConnection myOleDbConnection = new OleDbConnection(connStr);

Important: For successful installation and registration of the ACE OleDB driver, a suitable 64-bit Office installation is required on the target system.

Assign Data from Microsoft Access Database to List & Label

Once the appropriate connection has been provided using OleDbConnection, there are a number of ways to access the Microsoft Access database data.

Load the Data into an ADO DataSet
List & Label gets access to ADO objects with the help of the AdoDataProvider:

// Connect to the Microsoft Access database
string databasePath = @"c:\temp\Northwind.mdb";
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databasePath;
OleDbConnection oleDbConn = new OleDbConnection(connStr);
oleDbConn.Open();

DataSet ds = new System.Data.DataSet();

// Add table 'Customers' to the DataSet
string sqlCustomers = "SELECT * FROM [Customers]";
OleDbCommand commandCustomers = new OleDbCommand(sqlCustomers, oleDbConn);
OleDbDataAdapter daCustomers = new OleDbDataAdapter(commandCustomers);
daCustomers.FillSchema(ds, SchemaType.Source, "Customers");
daCustomers.Fill(ds, "Customers");

// Add table 'Orders' to the DataSet
string sqlOrders = "SELECT * FROM [Orders]";
OleDbCommand commandOrders = new OleDbCommand(sqlOrders, oleDbConn);
OleDbDataAdapter daOrders = new OleDbDataAdapter(commandOrders);
daOrders.FillSchema(ds, SchemaType.Source, "Orders");
daOrders.Fill(ds, "Orders");

// Define relation between tables
string relName = "Customers2Orders";
ds.Relations.Add(new DataRelation(relName, "CustomerId", "CustomerId"));

// Create the List & Label object and attach the ADO DataSet
// and call the designer
using(ListLabel LL = new ListLabel())
{
	LL.DataSource = new AdoDataProvider(ds);
	LL.Design();
}

Load Data Directly as IDbCommand
With the help of DbCommandSetDataProvider List & Label has direct access to the data via IDbCommand. The advantage of this variant is that the data is not loaded into memory in advance as it is the case with the DataSet, especially with very large databases this is important:

// Connect to the Microsoft Access database
string databasePath = @"c:\temp\Northwind.mdb";
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databasePath;
OleDbConnection oleDbConn = new OleDbConnection(connStr);
oleDbConn.Open();

DbCommandSetDataProvider provider = new DbCommandSetDataProvider();

// Add table 'Customers' to the DataSet
string sqlCustomers = "SELECT * FROM [Customers]";
OleDbCommand commandCustomers = new OleDbCommand(sqlCustomers, oleDbConn);
provider.AddCommand(commandCustomers, "Customers");

// Add table 'Orders'
string sqlOrders = "SELECT * FROM [Orders]";
OleDbCommand commandOrders = new OleDbCommand(sqlOrders, oleDbConn);
provider.AddCommand(commandOrders , "Orders");

// Define relation between tables
string relName = "Customers2Orders";
provider.AddRelation(relName, "Customers", "Orders", "CustomerID", "CustomerID");

// Create the List & Label object and attach the ADO DataSet
// and call the designer
using(ListLabel LL = new ListLabel())
{
	LL.DataSource = provider;
	LL.Design();
}

More about Microsoft Access Reporting and List & Label

Also see the .NET Tutorial for even more valuable information on using List & Label.

It doesn’t matter if you have a classic Windows desktop application or a modern web application with ASP.NET - the List & Label data providers can be used in both worlds.

Tip: The installation of List & Label already delivers numerous programming samples that use Microsoft Access databases as data sources. These can also be viewed in our GitHub repository.

Please leave a reply to this post if you’ve got questions about how to create reports with List & Label based on data from Microsoft Access databases or if you miss any information about this topic.

If you already enjoy connecting Microsoft Access databases as datasource to List & Label for creating amazing reports, feel free to show your love with a like below. :slightly_smiling_face: