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.


Popular posts from this blog

മലയാളത്തില്‍ മൊത്തം എത്ര അക്ഷരങ്ങള്‍ ഉണ്ട്?

Convert Number To Words in SQL Sever

Crystal reports load report failed: Could not load file or assembly CrystalDecisions.Web, Version=10.2.3600.0