将 Ms Access 应用程序转换为 .Net 应用程序有哪些好的技术？
我们有一个已有 12 年历史的 Ms Access 应用程序，用于我们的核心库存仓储和发票系统。 它已经在 SQL Server 后端上运行，但所有“逻辑”、表单和报告都在 Access 中。 在经历了将库存交易从非时间性转变为时间性所需的大量维护污泥之后，我意识到有一天我需要将其转换为代码，以便我可以在更可维护和可测试的环境中更好地管理逻辑。
有哪些技术可以让我以可管理且高效的方式将其转换为 .Net 应用程序？
一种想法是将查询转换为存储过程，然后将应用程序转换为 Adp 项目。但我仍然不知道如何处理表单和报告。
另外，如果这很重要的话，我是我公司唯一的开发人员。
We have a 12-year-old Ms Access app that we use for our core inventory warehousing and invoicing system. It IS already running on an SQL Server backend, but all the "logic", forms and reports are in Access. After experiencing the massive amounts of maintenance sludge it took to turn inventory transactions from non-temporal to temporal, I realized that I need to someday convert this thing into code so I can better manage the logic in a much more maintainable and testable environment.
What are some techniques that would allow me to convert it into a .Net application in a manageable and efficient manner?
One idea was to convert the queries to stored procedures, then convert the app into an Adp project.But I am still clueless as to how to handle the forms and reports.
Also, I am the only developer for my company, if that matters.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
由于您已经拥有带有一些业务逻辑的 asp.net，因此您可以将其打开以作为 Web 服务（asmx 文件）进行访问。 Google 获取 Microsoft Office Web Services Toolkit 适合您的访问版本（xp/2003 等），这将为您编写 vba 代理类来调用 Web 服务。 您可以通过代码（用于读取和写入控件的 vba）将 Web 服务数据绑定到表单，或者使用来自 Web 服务的数据创建本地临时表并使用常规访问绑定。
根据您最熟悉的内容（代码/tsql），您可以将逻辑放入存储过程或业务逻辑层或混合层（两者）。 我发现测试代码比存储过程更容易，并且喜欢不绑定到 sql server 来实现业务逻辑，即如果您想要更改数据库或想要在没有数据库的情况下离线开发/测试组件。 新的 .net 功能（例如 LINQ）具有相当好的性能，因此您不必依赖存储过程来进行数据库活动。
保留访问前端用户界面，直到将所有业务逻辑/数据访问重构为 Web 服务。 然后，如果需要，您可以创建一个使用 Web 服务的 ASP.NET 应用程序或一个 winform 应用程序。 （暂时远离 wpf，作为一个 ui，因为它是一个陡峭的学习曲线，并且还没有可以与访问数据表视图进行比较的数据网格。）
报告
访问报告可以升级到sql server报告服务（报告中的vba不会升级，最好在存储过程中编写一些tsql）。 如果您没有完整的 sql server 产品，您仍然可以使用 reportviewer 控件来编写报告（请参阅 http:// www.gotreportviewer.com/）在asp.net（或具有标准版本或更高版本的Visual Studio的winform）中绑定到ado.net数据集。
其他选项：
您可以编写 .net dll 并使用 com 互操作。 这种方法允许您逐渐开始编写功能。 不要使用 .net ui，例如 winform，因为它不能很好地与 access ui 配合使用。 您可以编写业务逻辑或数据访问逻辑，然后从 vba 调用这些类。 如果需要，您可以将此代码移至 asp.net 或 Web 服务。
要排除的事情：
我不喜欢使用并排版本编写新应用程序的方法。 作为单个开发人员，您需要担心的事情已经够多了。 您最终可能会在两个版本中添加功能并调试两个版本而不是一个版本。
vb6 表单互操作不适用于访问。
正如所说，ADP 已经死了。 （我从来不喜欢它们，因为我经常使用本地表来优化性能，并且它们只能通过代码调用而不是链接）
您也许可以使用 Visual Basic 升级向导（在Visual Studio），但它不会扩大所有内容（例如 dao/ado 代码到 ado.net 代码），并且不会创建针对 .net 优化的代码，并且根据设计的设计可能不容易编写单元测试vba 代码。 我建议重写代码（如果您认真对待测试看看是否喜欢它，请尝试测试驱动开发）。
As you already have asp.net with some business logic you could open this up to access as a web service (asmx files). Google for the Microsoft Office Web Services Toolkit for your version of access (xp/2003 etc.) and this will write vba proxy classes for you to call the web service. You can bind web service data to the forms through code (vba to read and write to controls) or create local temp tables with data from the web service and use regular access binding.
Depending on what you are most comfortable with (code/tsql) you can put logic in stored procedures or in a business logic layer or hybrid (both). I find it easier to test code than stored procedures and like not being bound to sql server for business logic i.e. if you want to change the database or want to develop/test components offline without a database. New .net features such as LINQ have pretty good performance so you don't have to rely on stored procedures for database activities.
Keep the access front end user interface until you have refactored all your business logic/data access to web services. You can then create an asp.net app that consumes the web services or a winform app if you want. (Stay clear of wpf, as a ui, for the time being as it is a steep learning curve and doesn't yet have a datagrid that can compare to the access datasheet view.)
Reports
The access reports can can be upsized to sql server reporting services (vba in reports doesn't upsize and it is better to write some tsql in stored procedures). If you don't have the full sql server product you can still use the reportviewer control to write you reports (see http://www.gotreportviewer.com/) in asp.net (or winform with the standard version or up of Visual Studio) binding to ado.net datasets.
Other options:
You can write .net dlls and use com interop. This approach allows you to start writing functionality gradually. Don't use .net ui e.g. a winform as it won't play nicely with access ui. You could write business logic or data access logic and then call these classes from vba. You can then move this code to asp.net or web services if required.
Things to rule out:
I don't like the approach of writing a new app with side by side versions. As a single developer you have enough to worry about. You will probably end up adding features in both versions and debugging two versions rather than one.
The vb6 forms interop does not work for access.
ADP as stated is pretty dead. (I never liked them as I often use local tables to optimize performance and they can only be called through code and not linked)
You may be able to convert your vba modules and class modules to vb.net using The Visual Basic Upgrade Wizard (in visual studio) but it doesn't upsize everything (e.g. dao/ado code to ado.net code) and doesn't create code that is optimized for .net and may not be easy to write unit tests on depending on the design of the vba code. I recommend rewriting the code (try Test Driven Development if you are serious about testing to see if you like it).
我有一个类似的问题，并通过在 Access 前端创建版本化部署系统（抓取并提取 CAB 文件）来解决它，找出所需的 AppDomain 操作以便能够将正确的 CLR 版本加载到 Access 进程中，加载.config 文件，并双向发布数据。
它使用标准 C DLL 调用，因此不需要 COM 注册，但遗憾的是没有 Unicode 支持。
发送命令 - 通用，我可以用它完成所有事情。
Open Form - 旨在成为 DoCmd.OpenForm 的“直接”替代品
Open Report - 旨在“直接”替代 DoCmd.OpenReport
因此，请制作一份新报告，或将现有报告迁移到 SSRS，制定格式标准，然后在 Access 中将 DoCmd.OpenReport 更改为 netDoCmd.OpenReport。
遵循命名约定来了解从何处加载报告，以及提取报告所需数据的标准方法。
现在，我正在在容量允许的情况下或在需要更改时迁移一份表格或报告。
因为谁可以停止功能开发一年来一次性完成所有工作呢？
但 MDI 无法正常工作。 我认为围绕 SetParent 和 UPDATE_UISTYLE 我仍然需要做一些工作，
所有这些都使得 UI 能够在进程中并在 Access 的窗口中。 我建造一切
在 DLL 中，最后一步是创建一个加载“第一个表单”的 EXE，并用它来替换 Access 前端。
I have a similar problem, and addressed it by creating a versioned deployment system in the Access front end (grab and extract a CAB file), figuring out the required AppDomain manipulation to be able to load the correct CLR version into the Access process, load a .config file, and post data both ways.
It uses standard C DLL calls, so no COM registration required, but also sadly no Unicode support.
Send Command - Generic, I could have done everything with this.
Open Form - Intended to be a "drop in" replacement for DoCmd.OpenForm
Open Report - Intended to be a "drop in" replacement for DoCmd.OpenReport
So make a new report, or migrate an existing one to SSRS, make the format standard, and then change DoCmd.OpenReport to netDoCmd.OpenReport in Access.
Follow a naming convention to know where the reports are to load from, and a standard method for pulling the data needed for the report.
Now I am migrating one form or report as capacity permits, or when a change is requested to it.
Because who can stop feature development for a year to do it all in one hit?
MDI doesn't work properly though. I think there's some work I still need to do around SetParent and UPDATE_UISTYLE
All of this makes the UI in process and in window with Access. I build everything
in DLLs, and the final step will be to create an EXE that loads the "first form" and use that to replace the Access front end.
从长远来看，转换为adp并不是一个好的解决方案——这项技术已被微软放弃。
如果您想切换到 .net（为什么？您有理由青睐 .net 吗？）我建议您开始阅读一些内容，尝试创建一些简单的应用程序，然后开始将此数据库转换为应用程序的任务。
但是……
我认为你和公司需要考虑这个项目涉及的风险。 如果您在管理层需要一些尚不存在的报告的一周内生病了，会发生什么？ 我建议您寻找一家小型的本地软件开发公司，他们会很乐意帮助您。 也许您可以安排您继续担任“首席开发人员”并仅将它们用作后备。
Converting to an adp will not be a good solution in the long term - this technology is abandoned by Microsoft.
If you want to switch to .net (why? do you have a reason to favour .net?) I suggest you start some reading, try to create some simple apps and then start the task of converting this database to an application.
But...
I think you and the company need to think about the risks involved in this project. What will happen if you get sick, just in the week that management needs some reports that don't already exist? I would suggest that you seek a small local software development company, they will be glad to help you. Maybe you can arrange that you continue to be the 'lead developer' and only use them for back-up.
我会考虑查看互操作表单工具包。 据我了解，这个工具使得在 VB6 中使用 .NET 表单变得非常容易，所以也许它也可以在 Microsoft Access 中使用？ 如果是这样，它可能会帮助您以增量方式将应用程序迁移到 .NET。 经过快速搜索，我无法找到任何有关将其与 Microsoft Access 一起使用的指南，因此，如果这最终是一条死胡同，我深表歉意。
I would consider looking at the Interop Forms Toolkit. As I understand it, this tool makes it quite easy to use .NET forms from within VB6, so perhaps it can also be used from within Microsoft Access? If so, it may help you migrate the application to .NET in an incremental fashion. Doing a quick search, I was unable to find any guides on using it with Microsoft Access, so I apologise if this turns out to be a blind alley.
简短的回答：迁移似乎并不容易自动化。
我的猜测是，您最好的选择是一次重写（并安装）系统一个部分，即使（可能）它迫使您的用户并排运行新旧版本一段时间以使用不同的位的功能。 您可以通过仔细考虑要迁移的功能以及迁移的顺序来最大程度地减少麻烦。
例如，您可能有一位用户，其工作角色要求他或她全天仅使用一个屏幕。 如果您首先迁移该屏幕及其附带功能，则该用户可以立即使用新系统并离开旧系统，从而减少维护负担。
所以这些只是基于没有太多信息的一些想法。 无论如何，我希望这会有所帮助。
Short answer: the migration doesn't seem like something easily automated.
My guess is that your best bet is to rewrite (and install) the system one piece at a time, even if (perhaps) it forces your users to run the old and new versions side-by-side for a while to use different bits of functionality. You can minimize that hassle by careful consideration of which features to migrate and in which order.
For example, you might have one user whose job role requires him or her to use just one screen all day. If you migrate that screen first with accompanying functionality, that user can be on the new system immediately and leave the old one behind, reducing your maintenance load.
So those are just some ideas based on not too much information. I hope this helps anyway.