Francisco Tapia
fhtapia at gmail.com
Thu Jun 19 12:14:36 CDT 2008
Borge, I'm a big fan of using webservices when they make sense. You mentioned a Client DB and your DB. I suspect that running ODBC is taxing the network connection thus the requirement to go with webservices. You can call webservices from Access using the soap object but they can be problematic. I will dig up some sample code that I used to use and post later today. You can also find similar code on google. If you prefer the database to make the call I have a procedure that I can share that also made a call to a webservice in order to process data. In our sitaution a webservice was a more likable solution since I could not link to the other database as it was a Universe db, and they did not have the ODBC links setup and was too time consuming instead a webservice turned out to be nicer because I could call and post directly to the system each new order, the order contained an xml of the entire order as typed into the access database. If you succeed in setting up a linked server which would be ideal but you'll need to be careful not to run your queries so open ended, you'll most likely want to ensure that you are not exceeding the Client IT's threshold for data throughput. but each call to the linked server is pretty much described as SELECT * FROM LinkedServer.Database.Owner.TableName For example: SELECT * FROM ClientServer.ClientDB.dbo.tblremoteBooking On Wed, Jun 18, 2008 at 5:13 PM, Borge Hansen <pcs.accessd at gmail.com> wrote: > 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 > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > -- -Francisco http://sqlthis.blogspot.com | Tsql and More...