[dba-SQLServer] [dba-sqlserver] transactional SP involving tables from two SQL Db - Web Service

Hi,

I am in desperate need for some input advice as to the following:

Background - simplified:
Access 2003 application with SQL2005 Db backend
The application is processing requests for short term vacancies (Booking) on
behalf of a remote Client, identifying suitable Person to fill the vacancy
(Placement).
Bookings are placed - via an application on the remote Client's distributed
intranet - into a table on Client's SQL2005 Server.
Our application is querying this table throughout the day in order to bring
new Bookings into our SQL db table
We also perform updates on a Status Field
Once a Placement has been identified (via text to speech automated phone
calls to potential Person filling the vacancy - but that's a different
story) we write the data to a new record on the remote Client's Placement
table

on remote client SQL Server:
tblremoteBooking
tblremotePlacement

on our local SQL Server:
tblBooking
tblPlacement

We have been given access to the tables on the remote Client Server via a
userprofile/login that gives us read,insert and update rights to these two
tables

We access the tblremoteBooking table via ODBC linked table to the Access
database window.
We then do query join between tblremoteBooking and tblBooking to get all new
bookings from the client into our table

Other updates and inserts happens via ADODB connection

Issues / Challenges:

1)
There is currently a push from Client's IT people to revoke the direct table
access and give us a web service for interacting with the tables - I don't
like that because I know very little about web services.


2)
I'd like to be able to perform joins etc between the remote booking table
and our local one in a SP on the sql db - rather than doing it in an access
query on odbc linked tables in the Access database window.

Similarly I'd like to be able to create a transactional SP that involves
update / insert to both remote and local tables with rollback so that if one
of the update / insert queries fail we can roll back the whole transaction
and give the data the remote and local tables in sync.

 What's required to access tables on a remote SQL Db inside a local SQL Db
in a stored procedure ?

3)
Another developer has created a web portal for our casual relief staff to go
online and have a look at Bookings on the web portal  and accept a suitable
booking.
All is tested and ready to go except for the web app to have acces to the
client's table for doing an insert to the placement table and an update to
the booking table.
Again, here we are inserting and update to both our 'local' tables and the
client's table so we want the same stored procedures put in place to ensure
data is in sync....


What to do?

Assuming that the Client would give us access the the remote tables, can
someone give pointers to how to establish connection to these tables from
our local SQL Db so we can manipulate these tables in a Stored Procedure.

Assuming that we are going with a web service for reading and writing to the
remote tables - can we then still access these tables in a SP - or will we
have to do this elsewhere (other coding environment) .... and remember we
are doing this in VBA !

Any comments / advice appreciated

Regards
borge