Need advice on best way to approach this

I'm investigating replication or DTS as a possible solution to my
problem. I'm looking to replicate data from one server to another,
I want all of the columns of one table PLUS another column from
another table to transfer to the other server.
For example on Server A, I have a table foo. Table Foo has column1
and column2. Also on Server A I have a table bar. Table bar has a
column1 and a column3. Server B has a table called test which has
column1, column2 and column3.
I want to replicate to the test table on Server B,
foo.column1, foo.column2, bar.column3
where foo.column1 = bar.column1
Is this possible?
I looked into replicating a view, but this requires the underlying
tables to reside at the subscriber which I do not want.
Can you replicate the output of a stored procedure to another
server? I see references to replicating stored procedures, but can't
figure out how to implement this. The only options I have for
articles are Tables and Views, why don't I see any stored procedures
as articles to publish?
Maybe it makes more sense just to use DTS?
You don't talk about whether this is a one shot deal or if you want to run this multiple times or how large the table is.
If its a one shot deal you should use DTS. Its faster than replication.
If your table is small and changes infrequently you could do this with snapshot or transactional replication.
If your table is large and you want some level of real time synchronization you should use transactional replication.
You can extend transactional replication to do lookups and replicate additional columns by creating a custom sync object. To do this create your own creation script using sp_addarticle.
Here is an example of how to do this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Need advice on approach

I am new to DTS/SSIS and would like some input on an approach to solve a problem.

I have a solution using SQL Server 2005 and another legacy solution running on Access. Status records are written to a table in SQL Server and as they are written, I need to write a record to a table in Access. This needs to be as real-time as possible.

I thought about writting a managed stored procedure in C# so I could also access MS Access, but someone pointed me to DTS.

Records are constantly written to the status table and need to remain in there even after they are processed.

Can you recommend an approach or an article to read?

Thanks,

Steve

This could be done with SSIS with a little work but I think if your are talking about a very small amount of data that needs to be written to access each time, then I think the SP idea is better or something in general from the SQL engine to Access rather than involving SSIS. So, you may want to repost in one of the more general SQL forums.

Now if you have SSIS already involved in the pitcure some where, then the SSIS idea might still be worth investigating. For example if the part of your story "Status records are written to a table in SQL Server " was itself being done by SSIS, then enhancing the SSIS scope could be a good approach.

good luck