Update path for legacy reporting ("Built-in" Access 2003)?

Question

Update : Albert D. Callal kindly started the discussion, and to get a few more opinions, I add generosity.

This is a non-trivial question about serving an outdated application and two other developers. We are not the original developers, and the code base is 300,000 lines of MFC and business logic, closely related to each other. We do not know every line of code 100%.

We know the code for the main components, and we know that it is poorly written. Our goal is to reorganize the application from 1995 to 2010. Between the three of us, we have (collectively) sufficient experience in the field of software architecture and database design so that we can fix components that are poorly designed in the code or incorrectly modeled into databases, but we do not have much experience working with modern reporting systems . So my question (as soon as you get to the end ...) is about reporting systems.

For everyone who reads this entire post, I appreciate your time. For anyone who reads this post and answers decisions, experiences (or empathy!), I am grateful and grateful.

At work, I inherited the maintenance of an Access 2003 database containing about 250 reports (and thousands of supporting queries) that act as a reporting mechanism for our application.

All reports have VBA summaries in them for specific formatting or pulling additional information into the report. For this reason, we are completely locked on the Access platform, we cannot use tools such as BIDS to import Access report objects without interfering to make the report the same without VBA.

So, to get out of this Access solution, we need to spend some time reviewing each individual report. This means that we strive to choose the best long-term solution, as we will have to process each report regardless of the platform that we choose.

In addition, our customers have the choice of Microsoft Access or SQL Server as the database. This means that all of our SQL must be written with the lowest common denominator - JET SQL. We have room to maneuver to refuse support for Microsoft Access, but we will need to create a case for it. If the best reporting system that we can identify has strong SQL Server support but little or no Microsoft Access support, this will speed up the abandonment of Microsoft Access support as a database.

The overall reporting system implementation is rather mediocre, when we want to display the reports in our application, we start the Microsoft Access process, find its window and re-call it in our application, remove its window styles, and then use Access.Application COM to call some VBA, which creates linked databases in a database (either Microsoft Access MDB or SQL Server database) and then opens the required report. Probably the only supported part of the process is the use of open COM interfaces, the rest is an ugly hack. Other components in the application are no less impressive.

In order to “fix” our application, we have a new development plan, with the development of our application each year is divided into three parts.

• 4 months, updating our application to support the latest state legislation in our industry.
• 4 months providing an important new feature.
• 4 months "consolidation" (correction of what is broken)

We are now at # 3 (this year), and we really want to take advantage of the downtime to fix the application, refactoring the main components. We have three developers, and we want AppName v5.0 to be released in late 2012 (currently AppName v4.12). This gives us 36 months of development efforts for coordination between several components (user interface, basic database structure, reporting, etc.) for the three consolidation periods that we will have before. The sum of the components that we fix will give us v5.0.

We figured out what we would like to do with most of the components besides our reporting mechanism, and I send it to SO hoping to get good ideas or at least get a feel for what is required.

I have two ideas for improving our reporting system. Both of them are associated with a moderate amount of work, and there is one consideration that not a single solution is fully related: in addition to the reports that we develop, our clients also have the opportunity to request individual development of reports. They are customer-oriented, we take their Access database, supplement it with our report and return it to the client. There are hundreds of unique reports - unsuitable if we turned off the old system. (And we finally need to turn off the old system - we don’t know how much more we can work with the Microsoft Access window to look like an integrated report. We already have two different access path codes for Access 2003 and 2007. What to do, if we cannot crack the code for Access 2010, and all our customers should use Access 2007?)

For both ideas, we intend to stop supporting our current reporting system and let it work as long as it is out of service. Perhaps we can hack support for Access 2010 and Access 2014, and the customer reports that have been developed continue to fit into another 5 years. Over time, we moved the most commonly used reports from the old Access database to their new format.

Idea 1: Microsoft.Reporting.WinForms.ReportViewer

The first idea is to write a wrapper around the ReportViewer control as a reporting mechanism.

We need to move the project to C ++ / CLI (already on the cards), and instead of starting the whole process every time we needed to view the report, we could just create an instance of this control. The bonus of this is that the RDLC files containing the reports are much easier to control the version in Subversion than the Access 2003 database we currently have (we use Visual SourceSafe, because the tools for integrating SVN with Access do not work well with the size of our Access Databases). The visual designer for RDLC files RDLC also well integrated into Visual Studio.

This is an evolutionary rather than revolutionary change in the way the report is maintained, the ReportViewer control will accept the RDLC file with the report layout, and our application will take care of the data request. Since our database may be SQL Server or Microsoft Access, we still have to write plain JET SQL. We get better reporting (drilling looks good), stronger development tools and easier version control, but is it worth the effort?

Idea 2: SQL Server and SharePoint 2010 Reporting Services with Access Services

The second idea is to kill Access as a database platform and migrate all of our clients to SQL Server (we placed the instances of our application for those clients who do not have the skill set to install their own instances of SQL Server). As soon as they are ported, we will use the services SQL Server reporting as a reporting mechanism using the ReportViewer control in server rendering mode.

In addition to SQL Server Reporting Services, I’m interested in finding out if SharePoint 2010 can quickly migrate existing Access reports to a more manageable format using Access Services . We will take the Access report that the client uses, convert it into an Access web report, and make it available to them on the SharePoint site. This would be only for our hosting clients, but if we find a way to quickly process VBA from client reports, we can try out several hundred individual reports of our clients.

I am also interested in the possibility of using the access form to the web access interface to act as a portal for all our reports. In our application, we will have a web browser control that will provide customers with access to their own reports and our standard.

We would get all the benefits of Idea # 1 plus the ability to fully write Transact SQL, a reporting portal and (hopefully) a reasonable upgrade path for our own customer reports.

So my question is: am I going to do it right? Are these viable solutions for modern reporting systems or ridiculous? We have a strong preference for using the ReportViewer control either in client rendering mode, where our application processes data, or in server rendering mode in combination with SQL Server, but are there reporting systems like Crystal Reports that offer better reporting and better migration paths for our legacy Access reports?

If you had up to 36 months of developer time, how would you do it?

Answer 1

Okay, okay, nobody else is jumping, I give it.

Pretty interesting how you talk about a writer who is over 15 years old. At that time, the Access report writer was not compatible with the prior art. It was a weird mile ahead of everything else in the industry. Even today, many competing report authors do not have the concept of auxiliary reports that allow you to model relational data without resorting to code or even SQL. Then run programmable VBA, then the result will be very unique and powerful.

For access 2007, the report writer received some more pleasant updates in terms of layout controls, but here it will be few.

And for 2010, we can now display reports in a subform control. This feature has been added to facilitate the use of the new access control. Access 2010 has a new web browser control (works in forms or reports), as well as a new navigation control. Your messages indicate that the new navigation control and the web control are somehow related to each other, but they are completely different from each other.

Both the new web browser control and navigation controls can be used both in web applications and in 100% of client applications. Navigation management is good, since you can create this navigation by dragging and dropping reports onto the navigation control to create a list of reports to choose from (it’s smooth, light and nice). And with this navigation control, we can actually create some useful reporting interfaces.

As you noted for Access 2010, we now have web publication of access reports, and this feature is based on SQL Server Reporting Services (they are RDL reports). However, two important questions are here: VBA is not allowed inside web reports. And I also note that there is no automatic conversion utility that is built into access that will convert existing reports to web reports. Thus, in order to create a report that will be marked and published on the Internet, you need to specifically select a web report to achieve this goal. Thus, this answers and eliminates one of your question, which will help you convert existing reports to SQL-server, but the answer is no. Thus, Access does not help you convert existing reports to RDL reports on the Internet (as noted, Access uses RDL and sql reports for these web reports - these reports are also displayed on the client client side without conversion).

Access has a great path for web reporting through SharePoint, and Access Web is suitable for Office 365. However, keep in mind that this ability will not really help existing reports that you have.

In fact, one of the things I would look at if you are going to use the winforms report viewer is to change where the existing VBA report code will be moved? You did not mention this problem. As already noted, a very interesting and remarkable feature of these reports is that they have embedded VBA code. Often VBA will be used because SQL and something like RDL will NOT work, because none of these languages ​​(sql and RDL) is procedural code.

I cannot stress how important this concept is. Thus, this pretty much means that any replacements to the report record mean that the code should now be EXTERNAL from the reports and transferred to your application. Therefore, keep this issue in mind, because now that you are publishing new reports, you are also releasing a new procedural code that is NOT contained in these reports. This code should be part of your application (therefore, to release new reports, you will also use a new version of your software).

You are unlikely to find much that allows you to enter the procedural code inside the report, as you can with access. Thus, this code and report logic should now be built and maintained in your main application and outside of reports.

At the end of the day, I must point out the old proverb if it has not broken, and then do not change it. Access has been for a very long time, but we have seen significant investments from people in Redmond in this product over the past few years, so it shows no signs of death in the near future.

So, one of the possible assumptions is to maintain the status quo and keep going the way it works now. I mean, you stated that you still need to support JET so that you don't shy away from using most of the access anyway. So you should still use the JET engine. So, you just drop the reporting part, and you still use the JET data file.

However, assuming this decision has been made, I cannot really suggest which speaker should replace your access. Obviously, considerations for the next report author should have a seamless path to the network, even if they now appear on the desktop. Today it makes no sense to make large investments without any web considerations.

I truly believe that SQL Server Reporting Services is a good choice because of the ability of the Internet. And, as an access developer, we also have the ability to create web reports, but they also have an excellent effect on the access client on the desktop (and this works when you do not have a server and there are no problems with publishing these reports on the Internet conversion, or using them locally on the client). Thus, even if you are not using access, choose something that allows you to provide reports both on the desktop and on the Internet, as access 2010 allows.

I would consider creating a reporting system around some .net tools. This is probably not as good as the built-in reporting system within an existing application, but it will allow you to release new reports, and you do not have to touch the existing code base for each new report released. This release of new reports with a procedural code should be allowed. You can probably now release new reports without having to modify the main application, because these reports may contain code inside. I would like to use something that will allow you to create and release new reports, but you do not need to release a new version of your core software. You may no longer be able to use the code in reports, but you need to look at it somewhere and, hopefully, outside of your main application.

Answer 2

Wow, this is a big question, and Albert gave you a terphic answer.

Unfortunately, I do not believe that there are magic bullets to solve your problem. I used Microsoft Access from the first version and always believed that the most powerful feature is the report generator, especially when used with SQL Server. As you undoubtedly know, often there may be problems with damaged Access databases in a multi-user environment and SQL Server addresses that are issued very nicely.

To my way of thinking, the biggest problem with Access is that Microsoft released managed code (.Net) ten years ago, but Access is still a native application. In an ideal world, Microsoft would rewrite Access in C # using all the latest features, such as improved support for multiple processors, etc. Unfortunately, I do not expect this to happen in the near future.

Visual Basic for Applications (VBA) certainly far deviated from the "modern level" when it was introduced, but today I believe that most will agree that coding in VB.Net using Visual Studio is much more productive than continuing development in VBA.

By choosing a new report generator, you need to live for several years, maybe it would be useful to consider what the “ideal” report generator should look like over the next ten years?

Personally, I would like to:

1) All the great graphics and ease of skinning and branding that Silverlight provides.

2) Great support for multiprocessing (you should have noticed how the user interface thread in Access often appears “not responding” when running long queries or reports).

3) Support for multiple devices, such as mobile phones, iPad, etc. While desktops and websites dominate today, they are becoming more and more important (if for some reason they are not important to your customers in the future).

4) Support for modern programming techniques, such as test-based development, dependency injection, etc.

Please tell us what you decide.

Answer 3

This is a long shot, but is it possible to use Access to create a saved PDF file and display it in your application in the PDF viewer control, which is part of your application and not external? Or export to XML or something (I don’t know what XML export options are available for reports in recent versions of Access, if any)?

The fact is that you do not have to rewrite the logic of Access reports, but you would eliminate the fake implementation and replace it with something that was really built into your application.

What you would refuse is perhaps the parameters that the access user interface provides to the user, but I'm not sure how useful this is (I would not want these parameters to be available!).

In addition, you would save the reports to disk, but I’m not sure that this is a big part of any important problem, but it will completely depend on the context (I assume that you do not have 1000-page reports with heavy graphics, etc. )

Answer 4

You can take a look at ActiveReports with Data Dynamics. We use it in our reporting applications for documents such as invoices, and it is extremely flexible, much more than what you can achieve with MS reporting tools. For reports that are genuine reports, not documents, we use reporting services. Some time has passed since I had to transfer the access report to active reports, but you can do little in access that you cannot do in active reports. I am also sure that he has a decent tool for import reports. There is available a fully functional evaluation version for download, which, unless they change the situation, simply prints a watermark in the footer of the report, and does not expire after a fixed evaluation period. Well worth a look, I would say - Here is a link to their website

Answer 5

- , Microsoft, , . 36- , . .

• - ?
• - .
• - - -
• Write Tests - I/O , I/O.
• - /API .
• - , /API, .

36- , 3-6 /API, I/O 7-10 , (scrum/agile).

Access SQL- .

Answer 6

Crystal, Access (2 - 2007), SQL Reporting DevExpress, DevExpress. .net, Windows Forms, - ASP.net, WPF Silverlight. .net, . - . , , , , DX- , .

, , DX .

: http://devexpress.com/Products/Index/Reporting.xml