About replication from SQL to Access Mdb file

About replication from SQL to Access Mdb file

Post by Henry Ji » Tue, 20 May 2003 12:22:35


  Anyone did the replication from SQL Server 2000 to Access Mdb file? I need
your help.

  When I tried do that with SQL Merge Control, I got the following error

    The process could not initialize 'MsJet'. Check to see if the component
is registered correctly.

  I have no idea with this message, what should I do?

  The code is just like this:

    Set mobjMerge = New SQLMERGXLib.SQLMerge

    With mobjMerge
        'Set up the Publisher.
        .Publisher = "MyServer"
        .PublisherDatabase = "DB1"
        .Publication = "DB1"
        .PublisherSecurityMode = NT_AUTHENTICATION

        'Set up the Subscriber.

        .Subscriber = "MDBSUB"
        ' MDBFileSpecification would be something like
        .SubscriberDatabasePath = "C:\Temp\Test.mdb"
        .SubscriberDatasourceType = JET4_DATABASE
        .SubscriberSecurityMode = DB_AUTHENTICATION
        ' JetDatabaseUserID would be something like "Admin"
        .SubscriberLogin = "Admin"
        .SubscriberPassword = ""

        'Set up the subscription.
        .SubscriptionType = ANONYMOUS
        .SynchronizationType = AUTOMATIC

        'Create the database and subscription.
        '.AddSubscription EXISTING_DATABASE, NONE

        'Synchronize the subscription.
        .Initialize    ' << error occurs here
    End With




1. Replication from SQL to Access MDB to MDB

I have a SQL server that drives my WEB page.  I want to give
someone access to the data, but I don't want them to access
the live copy.  I'd like the copy to be in an Access 97 MDG

Currently, I simply open up Access97, create a new MDB, then
use File, GetExternalData, Import to import all of the tables
to the Access file.  Then I put it on the FTP site so he
can "take it".

I'd like to automate that process, and if possible, make it
more in realtime.

For example,

He's in another state, and can only access my site thru
TCP/IP.  If I create an Access MDB sitting on my web server
in Calif, can he "attach" to it over the network.  Or should
I flag that Access as "replicatable" and let him keep a replica
of the design master (the MDB) and then SQL Server keep the design
master up to date?

Previously I was shipping him a new MDB (he was downloading
on the ftp site) but every time I create a new MDB, I have
to spend 15 minutes in the Relationships panel joining all
of the files together so that he can query them.

Certainly someone has a better way then this

The Access docs seem to only discuss Access to Access and
the SQL docs only discuss SQL to Access if the Access file
is directly used by the person.  In this case, either he would
have to take a copy of it from me ftp site each time, or I
would have to have some way to trigger the SQL Server to
sync/replicate the data prior to him downloading the MDB file.

His connection over the net is 33.6, so direct attach from Access
on his machine to the MDB directly doesn't really seem viable
(the datasets are huge and large, making for alot of record
movement in the MDB)

-Mark Hripko

2. Probs using isqlw_wce in pocketpc emul.

3. Does user need Microsoft Access License to use EXE file that access .MDB file

4. OAS 9i stupid questions

5. Replication from Access MDB to SQL Server 2000

6. UDFs & Triggers

7. Concatinate .mdb files into single .mdb file

8. need help with "Order By" & "Union All" in the same query.

9. Zipping the mdb file in VB Coding while creating a new mdb file

10. Unable to Append Link table from MDB File to MDB file

11. * Backup SQL Server Tables to Access *.MDB file

12. Cannot import date fields into SQL 7 from Access .mdb file

13. Specifiying a Workgroup File When Using ADO to Open an Access .mdb file (OLEDB)