Posts

Showing posts from September, 2010

Synchronize data between MS Access and SQL Server database using Linked Server

I used linked server to export data from an access database to Sql server database at regular interval. I feel it is much easier rather than creating a windows service/scheduled job.

Add a linked server
EXEC sp_addlinkedserver
@server = N'AccessDB',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'C:\SourceData.mdb'Set up login
EXEC sp_addlinkedsrvlogin

@rmtsrvname = N'AccessDB',
@useself = N'False',
@rmtuser = N'Admin',
@rmtpassword = ''Now you can exceute sql statement with this access database.
Eg: simple select statement:-SELECT * FROM OPENQUERY(AccessDB,  'SELECT * FROM Order')In my case I wrote an insert statement in side sql job with scheduled time interval.

Web survey

WebSurvey is an ASP.NET server control that helps developers quickly and easily generated web-based surveys from an XML file

Creating a Windows Service Application

Just thought of sharing a very nice article, explaining how to create a windows service using visual studio
http://msdn.microsoft.com/en-us/library/zt39148a.aspx

Admin privilege is required to follow all the steps in the article.