Post reply

Replication to Access

  • November 15, 2002 at 5:46 am

    #79177

    Has anyone set up replication to Access 2000/97 from SQl 2000? I have tried this and it should be so easy, but when I try to push a subscription, there is nowhere to select the Access database, even if I create a linked server. If you can do it, please post up info.

    Paul Ibison

    Paul.Ibison@blueyonder.co.uk


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • November 15, 2002 at 6:13 am

    #441468

    How to setup merge replication between SQL Server 7.0/2000 and Jet (Access 2000) subscribers?

    Here are the steps you should follow to setup merge replication from SQL Server to Jet subscribers:

    Create a blank Access 2000 database, that will act as a aubscriber

    Create a linked server to this Access database using sp_addlinkedserver. Here's an example:

    EXEC sp_addlinkedserver

    @server = 'MyAccess',

    @provider = 'Microsoft.Jet.OLEDB.4.0',

    @srvproduct = 'OLE DB Provider for Jet',

    @datasrc = 'C:\MyAccess.mdb'

    GO

    Configure a login to access the linked server, using sp_addlinkedsrvlogin as shown below:

    EXEC sp_addlinkedsrvlogin 'MyAccess', 'false', NULL, 'Admin', NULL

    GO

    To configure the linked server as a subscriber, open Enterprise Manager, go to Tools -> Replication -> Configure Publishing, Subscribers, and Distribution -> Subscribers tab

    Click on New Subscriber

    Select Microsoft Jet 4.0 database (Microsoft Access)

    Click OK

    Select Linked Server, in this case MyAccess

    In the login box, type in Admin, leave password Blank

    Click OK and again OK to close all dialog boxes

    To create a publication that allows Jet susbcribers, make sure you do the following while creating the publication in Enterprise Manager:

    Check the box against, 'Some Subscribers will be Microsoft Jet 4.0 databases (Microsoft Access)' in the 'Specify Subscriber Types' screen of Create Publication Wizard

    Setup the subscription to the Jet subscriber, just as you would normally do for SQL Server subscribers

    From http://vyaskn.tripod.com/repl_ans5.htm

  • November 15, 2002 at 7:11 am

    #441478

    Thanks for the reply. Something strange here, as I don't get the option : 'Some Subscribers will be Microsoft Jet 4.0 databases (Microsoft Access)' in the 'Specify Subscriber Types' screen of Create Publication Wizard. I'm using SQL 2000 EE sp2, so it's not that, but there must be something I've missed in configuration. Any ideas? Can other people do it?

    Paul Ibison

    Paul.Ibison@blueyonder.co.uk


    Paul Ibison
    Paul.Ibison@replicationanswers.com

  • November 15, 2002 at 7:31 am

    #441481

    Apologies - have got it working now and it was something silly. I addes all my subscribres ages ago on the distributor, and ever since then when I create a publication, I haven't needed to initialise them, so I forgot that this was part of the process. It works fine now and thanks for the help.

    Paul Ibison

    Paul.Ibison@blueyonder.co.uk


    Paul Ibison
    Paul.Ibison@replicationanswers.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply