MS Access互操作 - 数据导入 [英] MS Access interop - Data Import

查看:138
本文介绍了MS Access互操作 - 数据导入的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用exe来将SQL导出到Access,我们不想使用DTS,因为我们有多个客户端,每个客户端都导出不同的视图,并且设置和维护DTS包的开销太大。

I am working on a exe to export SQL to Access, we do not want to use DTS as we have multiple clients each exporting different views and the overhead to setup and maintain the DTS packages is too much.

*编辑:这个过程每晚都为许多客户自动化,因此整个过程必须在存储过程中的游标内启动和控制。这是因为必须按导出的每个项目过滤数据。

* This process is automated for many clients every night, so the whole process has to be kicked off and controlled within a cursor in a stored procedure. This is because the data has to be filtered per project for the export.

我已经尝试了很多方法将数据从SQL中导入Access并且最有希望的一直在使用访问互操作并运行

I have tried many ways to get data out of SQL into Access and the most promising has been using Access interop and running a

doCmd.TransferDatabase(Access.AcDataTransferType.acImport...

我遇到了一个问题,我从视图导入,并手动运行导入,似乎视图无法开始快速返回数据,所以访问弹出一个MessageBox对话框,说它已超时。
我认为这也发生在互操作中,但因为它是隐藏的,方法永远不会返回!

I have hit a problem where I am importing from views, and running the import manually it seems the view does not start returning data fast enough, so access pops up a MessageBox dialog to say it has timed out. I think this is happening in interop as well, but because it is hidden the method never returns!

有什么方法可以阻止弹出这个消息,或者增加导入命令的超时?

Is there any way for me to prevent this message from popping up, or increasing the timeout of the import command?

我目前的攻击计划是平铺视图进入表格,然后从该表格中导入,然后放弃展平的表格。

My current plan of attack is to flatten the view into a table, then import from that table, then drop the flattened table.

对于如何解决此问题的任何建议感到高兴。

Happy for any suggestions how to tackle this problem.

编辑:

关于我在做什么的更多信息:

Further info on what I am doing:

我们有多个客户端,每个客户端都有一个标准数据模型。其中一个模块是访问导出器(sproc)。它读取要从参数表导出然后导出的视图。视图按项目过滤,并为每个项目创建一个访问文件(每个视图都有项目字段)

We have multiple clients which each have a standard data model. One of the 'modules' is a access exporter (sproc). It reads the views to export from a parameter table then exports. The views are filtered by project, and a access file is created for each project (every view has project field)

我们正在运行SQL 2005并且没有转向SQL 2005很快,我们可能会在几个月后跳到2008年。

We are running SQL 2005 and are not moving to SQL 2005 quickly, we will probably jump to 2008 in quite a few months.

然后我们有一个模块执行作业,它在每个数据库上执行配置的模块。在此模块执行中有许多导入/导出/其他作业,并且访问导出器必须能够适合此框架。所以我需要一个通用的SQL - > Access导出器,可以通过我们的参数框架进行配置。

We then have a module execution job which executes the configured module on each database. There are many imports/exports/other jobs that run in this module execution, and the access exporter must be able to fit into this framework. So I need a generic SQL -> Access exporter which can be configured through our parameter framework.

目前sproc调用我写的exe文件,我的exe通过interop打开访问权限,我知道这对于服务器是坏的但是模块执行被写入,因此一次只执行一个模块,因此该过程永远不会一次运行多个实例。

Currently the sproc calls a exe I have written and my exe opens access via interop, I know this is bad for a server BUT the module execution is written so only a single module is executing at a time, so the procedure will never be running more than one instance at a time.

推荐答案

我已经确定了这样做的方法。

I have settled on a way to do this.

http://support.microsoft.com/kb/317114 介绍了启动访问过程的基本步骤。

http://support.microsoft.com/kb/317114 describes the basic steps to start the access process.

我已将Process变为类变量而不是ShellGetApp方法的局部变量。这种方式当我调用Quit函数进行访问时,如果它因任何原因没有关闭我可以明确地终止进程。

I have made the Process a class variable instead of a local variable of the ShellGetApp method. This way when I call the Quit function for access, if it doesn't close for whatever reason I can kill the process explicitly.

app.Quit(Access.AcQuitOption.acQuitSaveAll);
if (!accessProcess.HasExited)
{
    Console.WriteLine("Access did not exit after being asked nicely, killing process manually");
    accessProcess.Kill();
}

然后我在这里使用了一个方法超时函数来给访问调用一个超时。如果它超时我也可以杀死访问过程(超时可能是由于弹出一个对话框窗口,我不希望该过程永远挂起。我在这里得到了超时方法。

I then have used a method timeout function here to give the access call a timeout. If it times out I can kill the access process as well (timeout could be due to a dialog window popping up and I do not want the process to hang forever. I got the timeout method here.

实施C#通用超时

这篇关于MS Access互操作 - 数据导入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆