Merging Data into SQL Server

ActiveX controls simplify replication for remote users

[Editor's Note:Share your SQL Server discoveries, comments, problems, and solutions with other readers. Email your Reader to Reader contributions (400 words or fewer) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $100.]

If your mobile users aren't already demanding access to SQL Server data while they're on the road, they soon will be. Mobile users might need to add and edit records that they'll synchronize with the database when they return to the office. Or to get goods and services to customers faster, your users might need to enter new orders and immediately transmit them to the database from their remote site, which means attaching to your server through the Internet or a secure corporate VPN.

However, if your users are running an Access 2000 application with either the Microsoft Data Engine (MSDE) or a single-user version of SQL Server 7.0 on their Windows NT or Windows 9x laptops, you could run into problems. The graphical control that Access uses for replication provides limited functionality. For example, after you select Synchronize Now from the Access Tools, Replication menu, Access doesn't display a dialog box reporting run status or replication success or failure. In addition, a Visual Basic (VB) application won't contain built-in Access replication menus. And it's difficult to set up a TCP/IP connection in Access.

The SQLMerge and SQLDistribution ActiveX controls, however, let you seamlessly merge data with a back-end SQL Server implementation, dramatically simplifying data replication for mobile end users. Because you can use these controls in either VB or Access 2000, they're invaluable for development. As a bonus, you can use the controls to merge data with SQL Server across the Internet or an intranet.

Setting Up SQL Server

Let's see how to use the controls by using the Pubs database, which ships with SQL Server 7.0. From SQL Server Enterprise Manager, select Tools, Replication, then Create & Manage Publications. From the list of databases, select Pubs, then click Create Publication. If Pubs isn't available for replication on your server (the default), SQL Server needs to add some tables and stored procedures before proceeding. It also needs to add globally unique ID (GUID) fields to all your tables so it can track all changes subscribers make to the data.

You can then follow the wizard to create your publication, making sure you select Merge Replication and specify that all subscribers are SQL Server subscribers. From the Specify Articles screen, click Publish All to make all Pubs data available for replication. At the next screen's prompt for publication name, enter pubs_to_MSDE. On the next screen, select Yes, I will define data filters, then click Next. Because you want all data replicated, select No, I want to publish all the data, then click Next. Select the option to Enable anonymous subscribers. The final screen shows a default update schedule for the database schema. The schema snapshot ensures that you've properly initialized first-time subscribers with all published tables. You can use the default schedule but make sure you check Create the first snapshot immediately. After the wizard finishes creating the subscription, you can create your Access 2000 project.

Setting Up Access 2000

Populating the remote client with Pubs' tables and data is easy because SQLMerge does all the work. Before merging data changes, SQLMerge even ensures that all the tables and schema exist.

First, make sure MSDE or SQL Server has started on the client, then open Access 2000 and create a new project. Access will create a new file with the .adp (Access Data Project) extension instead of the familiar .mdb. Access uses .adp files to link to back-end data. Call your project pubs_local.adp, then click Create.

A wizard appears to help you create the SQL Server database on MSDE. Specify (local) as the server name and pubs_local for the database. When the wizard is finished, you'll have an empty Access container. When you add the SQLMerge ActiveX control to this project, you'll automatically populate the database with Pubs tables and data.

Before working with SQLMerge and SQLDistri-bution, make sure you set the proper references to the controls. From the Access 2000 database, select View, Code to access the Visual Basic Editor (VBE), then select Tools, References. You can select the Microsoft SQL Distribution Control 7.0 (sqldistx.dll) for transactional and snapshot replication or the Microsoft SQL Merge Control 7.0 (sqlmergx.dll) for merge or bidirectional replication.

One benefit to using SQL-Merge and SQLDistribution is the ability to track synchronization events. To capture these events, you need to create a class module that uses the controls' only event: Status. In the VBE, create a new class module that includes the code in Listing 1, then save the module as clsMerge. Note that, by default, Access 2000 will name your class module Class1. Until you explicitly save the module as clsMerge, you won't be able to use the object in other parts of your project.

The Status event will periodically send a simple message to Access's Immediate Window. After you're comfortable using the controls, consider adding the messages to a status form or a progress bar to make the application easier for users to work with.

Replication_Merge After creating the class, you can program the controls with little effort, as the Replication_Merge function in Listing 2 shows. The code first defines properties for the publisher, or back-end database. Note that the Publisher-SecurityMode property has two options: DB_Authentication and NT_Authentication. The former uses SQL Server to authenticate your login ID and password; the latter uses Integrated Security with an NT trusted connection. Because this example uses TCP/IP to illustrate replication across the Internet, it enforces DB_Authentication. The code then specifies the publication name, pubs_to_MSDE.

The next two lines of code:

.PublisherNetwork = TCPIP_SOCKETS.PublisherAddress = "www.yourcompany.com"

show that the replication function will use IP for the synchronization. However, the PublisherNetwork property will also accept DEFAULT_NETWORK and MULTI_PROTOCOL. Note that the PublisherAddress value can be either a URL or a specific IP address in the form '123.123.123.123'.

Next, you specify distributor name and authentication mode. Note that you have to specify distributor name only if your distributor resides on a different server from your publisher.

You're now ready to set the subscriber—or remote database —properties. For our example, SubscriberDataSource assumes an MSDE or SQL Server Desk-top edition on the remote user's laptop. However, this property also accepts Access 2000 (JET4_Database) databases and OLE DB (OLEDB_Data-Source) and ODBC (ODBC_ DSN) connections. Finally, you supply the user's login and password and specify a subscription type of ANONYMOUS. If your users run NT on their laptops, you can take advantage of SQL Server's trusted connection here and omit the login and password.

When you run the Replication_Merge function, all events and the percentage completed will display in the Immediate Window. When the function completes, a status line reports any updates that occurred. To confirm that the local MSDE or SQL Server database received the Pubs tables and data, switch to the Access 2000 container (Alt+F11 is a shortcut from VBE), click Tables, then use F5 to refresh the view. All Pubs tables should appear. Now open a table, change a record, close the table, and rerun Replication_Merge. You should find the remote SQL Server updated.

Before enabling your server for replication across the Internet, talk to your network administrators and DBAs about whether to allow anonymous subscriptions over IP. After all, you want to let users replicate data from remote databases to SQL Server without exposing your company's sensitive data in the process.