pull information from database?

[gotnetdude]

I have been asked to develop an application that will pull information from an MS Access database that is used to capture information for robotic painting. Load the information to an MS SQL2K database for a 2.0 .Net Web app.

Would like some suggestions on the best approach. I was thinking of writing a WinForm application, but not sure if that's the best choice. The application I am to write will need to run daily and does not require forms or user interface. Job will be scheduled run the application.

On a related, but somewhat different issue. The MS Access database is on workstation that has been added to a domain. I shared out the folder and gave the folder a generic domain user read permission. If this was a web application I would give the application authenticate as the domain user through IIS. How do I get a WinForm application to authenticate as a domain user to share network resources?

 

[jmcilhinney]

This a data access question and belongs in the Data Access forum. Moved.

You should use ADO.NET to retrieve the data from Access and then probably SQL DMO to insert it into SQL Server. There are ADO.NET tutorials all over the place. Some are specific to Access but any are relevant as the code will be analogous. SQL DMO is a COM-based library designed to allow you to automate SQL Server. It has been replaced by the .NET-based SMO for SQL Server 2005. You can find information about using SQL DMO on MSDN.

 

[gotnetdude]

WinForm VB.Net Appl needs permission to Shared Network Folder

Thanks for the information in the previous post.

My main question was to make sure that my thinking with regard to my over all approach was correct. I was thinking of developing a WinForm application to pull the information off the MS Access DB and load it to the SLQ2K DB. The WinForm application would be scheduled to run once a day. Wanted to see if others thought that was the best approach or what the alternatives are. I was considering doing this through DTS and SQL on the SQL2K DB side of things, but my boss preferred that I write an application that handled the process. I wish we had SQL05 installed here. I am pretty sure that I could have developed an application in SQL05 directly.

Secondly, I am fairly confident that I can handle setting up the database connection to the MS Access and SQL2K DBs. However, I was wondering how a WinForm app authenticates to a shared network folder as a domain user? In a web app I would do this through IIS. How is this done or is it possible to do the same thing with a WinForm app? The Robotic application need full control of the Access database folder and everyone else only needs read.

 

[gotnetdude]

WinForm Impersonation

Assuming developing a WinForm application is the way to go. Can anyone tell me how to setup the application to Impersonate a demon user. I plan to develop the WinForm application using VS05.

 

[gotnetdude]

Since I plan to run this job through a job scheduler would I be better off developing this application as a console app?

 

[gotnetdude]

Not much interaction so far at this forum, but thought I would post this info to share. Went with a console app. I just completed testing and everything appears to be working great. I also figured out my issue with permissions to the share. Pretty simple actually, when I mapped the drive it asked me for user credentials and that was it.

I was wondering if anyone could explain to me the benefit of using a Windows Service in this case?

 

[jmcilhinney]

A Windows Service runs constantly and doesn't rely on an interactive user. A Console app will start, do its thing then close. If your app is only doing one thing on a regular interval without interaction with the user or other processes then a Console app sounds appropriate. A WinForms app has a GUI. If you don't intend to interact with the user then a GUI is useless overhead.

 

[cjard]

I'm thinking.. I'm nearly positive that SQL Server can make a remote database connection to access... then literally the transfer would be soemthing like:

SELECT * FROM [dbo].remotedb.sometable INTO localtable

 

[gotnetdude]

Thanks for the feedback. I ran into a bit of an issue trying to make the window service work. I using the same code I used in the console program to get the data from the Access db on a remote workstation. I created an xsd which I’m using to fill a datatable. The query works fine when I preview it in design mode, but it fails when the service executes the fill. I am thinking this is some kind of permissions issue. When I created the service I gave it an account type of local. Should I be using Network Service? How do I get the windows service to use network shared resources.

I haven't had a chance to try the remote sql, but I plan to give it a try soon...

 

[cjard]

Afaicr, .NET apps cannot access files/databases on network shares by default, so you will need to give the assembly some trust.. But i'm not sure what..

 

[gotnetdude]

I was able to successfully get the vb.net console app to read the remote access database. The windows service application is failing due to permissions. I have tried several thing to this point. I setup the service as local system, network services and user account types, but I keep running into the same problem. When I look at the properties of the service it only sees the local workstation. I believe that this is due to the fact I need to add my development workstation (which contains the service I'm running) to the domain. I working on getting it added. Hopefully that will get me past the issues I'm having. I also gave the service identy impersonation from the app.config of the domain user. I also gave the domain user permissions to the share where the access db is at

 

[gotnetdude]

Man the oledb parameter driven xsd in .net 2.0 that's no fun, but hey they work if you pock the right things in the right places. It's nice figuring things out on your own, but once in a while it nice to find something out from others. I can't believe that I am the first guy to have a window service that needs shared network resources. It's is even harder for me to believe that this is not possible. Any thoughts?