September 21, 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.

  1. 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'
  2. Set up login
    EXEC sp_addlinkedsrvlogin

    @rmtsrvname = N'AccessDB',
    @useself = N'False',
    @rmtuser = N'Admin',
    @rmtpassword = ''
  3. 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.

No comments:

Post a Comment