CODEWITHSUNDEEP
c#sql-serverms-accessreportingsql-server-express
Hancs
Hancs

Reputation: 333

Importing an MS Access report into C#

Can you please assist me with a small problem I have? It would be greatly appreciated :)

So I have an access application I am converting into C#. It is actually quite huge and makes use of multiple access databases as access is very limited. Now a problem I have come across is to do the reports in C#. I know it is possible to export the reports into XML format from Access but the problem is the client wants a SQL Server Express edition which doesn't support SQL Server Reporting services (as far as I understand). So it it possible in anyway to import those reports into C#? Or only the layout at least?

I am using the Microsoft RDLC Report Designer for Visual Studio 2017

Thanks for your time!

Edit: Or is there any alternative I can use to build reports with C#?

Upvotes: 1

Views: 2345

Answers (4)

Lubart
Lubart

Reputation: 81

As for me, the post marked as answer is incorrect. There is no straightforward answer to the question. And where is no copy&paste solutions can be made here. But, I already worked with the similar problems to convert MS Access application into C# programs. Most of the time consuming tasks with report are formatting how reports should look. I used Crystal Reports and DevExpress reports solutions to import report into their reports environment. They worked pretty nice to make all formatting for you. And save a lot of boring time to format all report from scratch. But you are left to fix queries and database connections for the report. Because MS Access used its own SQL syntax. But these are more interesting tasks for a programmer.

Upvotes: 0

ASH
ASH

Reputation: 20342

I have never heard of this before today, but it sounds interesting. I did some googling, and came across this.

The MainForm class is the application and is a good place to start a top-down evaluation of the code. There are just three tasks that this application performs. When the Browse... button is pressed, it loads the reports into the list box. Hide Shrink Copy Code

// we have a valid file name so we now need to
// populate the list box with available reports
listBoxReports.Items.Clear();

// create an application object.
MsAccess.Application app = new MsAccess.Application();
// open the access database file.
app.OpenCurrentDatabase(dlg.FileName, false, "");
string sql = "SELECT [Name] FROM MSysObjects WHERE Type = -32764";
dao.Database db = app.CurrentDb();
// query the database for all the reports.  all this data is
// contained in the MSysObejcts table which is invisible through
// the table listing in access.
dao.Recordset rs = db.OpenRecordset(sql, Type.Missing, Type.Missing, Type.Missing);
// go through and add all the reports to the list box.
while (!rs.EOF) {
    listBoxReports.Items.Add(rs.Fields[0].Value);
    rs.MoveNext();
}

// clean up
rs.Close();
rs = null;
db.Close();
db = null;
app.CloseCurrentDatabase();
app = null;

When the Print... button is clicked, the selected report is opened and sent to the default printer. Hide Copy Code

string report = listBoxReports.SelectedItem.ToString();

// create an application object.
MsAccess.Application app = new MsAccess.Application();
// open the access database file.
app.OpenCurrentDatabase(textBoxAccess.Text.Trim(), false, "");
app.Visible = false;
// open the report
app.DoCmd.OpenReport(report, 
    Microsoft.Office.Interop.Access.AcView.acViewPreview, Type.Missing, 
    Type.Missing, MsAccess.AcWindowMode.acWindowNormal, Type.Missing);
// print the report to the default printer.
app.DoCmd.PrintOut(MsAccess.AcPrintRange.acPrintAll, Type.Missing, 
    Type.Missing, MsAccess.AcPrintQuality.acHigh, Type.Missing, Type.Missing);
// cleanup
app.CloseCurrentDatabase();
app = null;

Finally, when the Save button is clicked, the selected report is saved as HTML in the same directory as the Access database file. Hide Copy Code

// create an application object.
MsAccess.Application app = new MsAccess.Application();
// open the access database file.
app.OpenCurrentDatabase(textBoxAccess.Text.Trim(), false, "");
app.Visible = false;
// open the report
app.DoCmd.OpenReport(report, Microsoft.Office.Interop.Access.AcView.acViewPreview, 
    Type.Missing, Type.Missing, MsAccess.AcWindowMode.acWindowNormal, Type.Missing);
// export the report to an HTML file
app.DoCmd.OutputTo(MsAccess.AcOutputObjectType.acOutputReport, 
    report, "HTML (*.html)", fileName, Type.Missing, Type.Missing, Type.Missing);
// cleanup
app.CloseCurrentDatabase();
app = null;

Finally, 

1)From the main menu select Project > Add Reference.
2)Go to the COM tab. Scroll down and select Microsoft Access 11.0 Object Library.
3)Click Select and then click OK.

using MsAccess = Microsoft.Office.Interop.Access;

As an aside, consider importing Access Tables and Queries into Python or R. If you are using SQL Server Express, I would guess money is a concern. Both Python and R are 100% free and both work perfectly fine with Access. Finally, both Python and R have very, very, very powerful reporting tools.

Upvotes: 1

Gustav
Gustav

Reputation: 55981

Short answer is No.

Access reports are very specific to Access in every matter, and very different from RDLC.

Here is an example made by a fellow of mine and me for reports created in .Net that totally mimics the reports of the old sample Northwind database:

Northwind.NET

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8404

Can you straight-up import them? No. There's no tool out there that I'm aware of that can. You should be able to recreate them in ReportViewer, although it's tedious.

Upvotes: 0

Related Questions