Manash

Hi,

We have VSTO application for excel and using Oracle DB as database. Now for one requirement we need to insert data into local MSAccess file. For doing so we are using importxml facility of dataset as follows:

Access.Application accessApplication = new Access.Application();

accessApplication.OpenCurrentDatabase(MSAccessDataManager.LocalDBPath, false, MSAccessDataManager.LocalDBPassword);

accessApplication.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;

string filePath = localFilePath + \\AD_RESIDUAL_PRODUCTS.xml;

dataSet.Tables["AD_RESIDUAL_PRODUCTS"].WriteXml(filePath);

accessApplication.ImportXML(filePath, Access.AcImportXMLOption.acAppendData);

filePath = localFilePath + "\\AD_RESIDUAL_COSTS.xml";

dataSet.Tables["AD_RESIDUAL_COSTS"].WriteXml(filePath);

accessApplication.ImportXML(filePath, Access.AcImportXMLOption.acAppendData);

accessApplication.Quit(Access.AcQuitOption.acQuitSaveAll);

 

But we are facing a strange problem as most of the time it works. But in some of the machine, the Process is stuck during ImportXML operation and data is imported partially and then it hangs.

Can anyone please help what wrong we are doing. Do we need to do anything special for this

Any kind of help appreciated.




Re: Visual Studio Tools for Office ImportXML hangs in VSTO application

Cindy Meister

Hi Manash

Why wouldn't you use Visual Studio's data tools (ADO.NET) to work with the Access database This would certainly be faster (and less prone to problems) than automating the Access application.

When your process hangs, is the Access application visible on the screen I don't see that you make it visible... Most often, when an Office application exhibits the behavior you describe, it's displaying a message of some kind and waiting for a response from the user. I'm guessing this is the case, and that there may be a problem with the data you're trying to "feed" into the table. Try putting accessApplication into your code, right after creating the Access application object and take a look.






Re: Visual Studio Tools for Office ImportXML hangs in VSTO application

Manash

Hi,

Actually we have data in Oracle database and then we have retrieved data in ADO.NET dataset. For storing that data locally we are using MSAccess database. COuld you please give more details how we can store ADO.NET dataset in MSAccess in a faster way. We thought it's ImportXML will do this but then we faced this problem.

Regarding the code, we found that when it hangs in the task manager there's an MSAccess application running and we manually need to kill that application.

Thanks for your help.






Re: Visual Studio Tools for Office ImportXML hangs in VSTO application

Cindy Meister

Hi Manash

<<Regarding the code, we found that when it hangs in the task manager there's an MSAccess application running and we manually need to kill that application.>>

Yes, of course there is. You've started the application, after all, and because your code can't continue (because Access is showing a message) it doesn't reach the point in the code where you call the Quit method. But did you try putting accessApplication.Visible = true; in your code, as I suggested, to help you trouble-shoot what the problem is that's causing it to hang

<<Actually we have data in Oracle database and then we have retrieved data in ADO.NET dataset. For storing that data locally we are using MSAccess database. COuld you please give more details how we can store ADO.NET dataset in MSAccess in a faster way. We thought it's ImportXML will do this but then we faced this problem.>>

You need to ask this in the ADO.NET forum. Basically, you'd use ADO.NET to create a connection to the Access database, copy the data into a dataset for that connection, then update.