Solved

How do I filter rows in SQL Server Merge Replication pull subscription using SQL Merge object in MS Access VBA?

Posted on 2008-10-17
4
726 Views
Last Modified: 2013-11-27
Consider a real estate application where real estate agents carry laptops loaded with SQL Server Express 2005 and Microsoft Access as the application front-end.  A server in the main office runs SQL Server 2005.  I am holding a large amount of data on the central server but the agents are only working with a subset of the data while out of the office, since SQL Server Express has a 4GB storage limit.

In a test situation (with a small central database under 4GB), I can successfully perform a merge synchronization initiated from within Access using the SQL Merge object.  This provides me with an up-to-date copy of all central database tables on the laptop and also updates the central database with any changes made on the laptop while disconnected from the LAN.  So far so good.

What I need to know is how to perform dynamic, client-controlled row-filtering (if I am using the right term) such that, at any given time, the laptops only contain data pertaining to properties within a certain set of zip codes, chosen by the laptop user prior to synchronization.  The data is held, of course, in a number of tables, each having foreign keys linked to a primary key in the main properties (homes) table, where the homes' columns include zip code.  There may tables of photos, document images, an events log, etc.  As I see it, the steps would look something like this:

1) Laptop database starts out "empty".  User selects zip codes 55501 and 55503 from a listbox and clicks a sync button.

2) Merge takes place and all laptop tables are filled ONLY with rows (from central db) associated with homes in the selected zip codes.

3) User disconnects from LAN.  Edits are made out in the field regarding conditions of property, photos are added, etc.

4) Laptop, reconnected to LAN back at office, again merges with central database, updating the central database with any changes or additions that have been made -- possible even with homes newly entered having, say, zip code 55602.

5) User now wants to head out again, with all current data for zip code 55503 (already on laptop from step 1) and 55507 (not yet on laptop).

6) At this point, all 55501 data is deleted from laptop, and either all 55503 data would be deleted from laptop and then recreated, or perhaps is simply synchronized.  55507 data is added to laptop.

7) Laptop user disconnects and heads for the field with only 55503 and 55507 data -- no 55501 or (new) 55602 data on board.

Any suggestions (with specific VBA code, if possible) on how to set up the row-filtering part of this, including the elimination of no-longer-desired (after sync) data from the laptop?  (I can manage the listbox and buttons.)  Thanks!

0
Comment
Question by:AutomateMyOffice
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22745142
I never use replication, but remembered seeing this question while I was researching something else. Here's a link and I hope that it helps you out.

http://www.replicationanswers.com/

Jim
0
 

Author Comment

by:AutomateMyOffice
ID: 22745286
jmoss111...

Thanks.  This is a potentially useful site that I spent some time in earlier today.  Unfortunately, I have not found anything there that specifically adresses the parts of this problem that hang me up.  

I think that, using some knowledge I have gained since posting this, I might be able to filter and obtain specific rows from the central db using parameterized row filters and user-defined functions, but I am still searching for what to do with the local rows that I no longer need.  Seems like if I simply delete them, they will end up deleted on the central db when I sync.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22745996
That sounds logical; I've never had a need to do any replication. I just remembered seeing this question while doing research on another subject. I was not proposing this as a solution; just trying help out if I could.

Regards,

Jim
0
 

Accepted Solution

by:
AutomateMyOffice earned 0 total points
ID: 22941723
After working a while with this situation I discovered that the sync process first updates the primary server with any changes I have made on the laptop, then makes the entire set of records on the laptop line up with any row filters I have put in place.  So, therefore, the local records that lie outside the filter are automatically deleted, freeing up the space to be reused.

Also, here is an article that supplies the code I needed to run this from Access VBA:
http://www.sqlmag.com/Article/ArticleID/8244/sql_server_8244.html

Further research also came up with .HostName property for the MergeControl in VBA, which sends a value of your choice to the server which can then control the parameterized row filtering through use of system function HOST_NAME() on the server.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question