Gustav Brock
Gustav at cactus.dk
Thu Jun 19 03:15:44 CDT 2008
Hi Borge You can't do an engine level transaction that spans both SQL Server and JET (your local database). So you will have to write your code like a higher level transaction and catch lost connections and the like during an update. If the tables at the SQL Server are not too big, an ODBC connection and linked tables are fine and, indeed, very simple to handle as you can use normal Access queries. However, if you have an ODBC connection you can also run a pass-through query which - as you probably know - is run on the SQL Server. Very handy and speedy for the update and insert of data on the server. I see no reason why to move further creating stored procedures for the simple tasks you describe but it can be done, of course. Again, you can call these via ODBC. I'm not sure how to interface a Web Service with Access 2003. With .Net it is "piece of cake". I see no advantages in this for you, so unless management decide for this I would stick with ODBC and/or ADODB. /gustav >>> pcs.accessd at gmail.com 19-06-2008 02:13 >>> 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