Hi
My first post here so apologies if this has been covered before.



I'm a long standing Access developer but this is the first time I have had to perform this particular task. Briefly, I have taken ownership of a huge SQL database with circa 1500 tables that uses various Access frontends to pull and push data.

I have a problem which should be straight forward but there is obviously some "idiosyncracy" that I'm missing. We're using 2007 with a SQL 2005 backend, and the job was to 'simply' upgrade the backend to SQL 2008. So far, the SQL database has been created, indexes and stats updated and have confidence in the SQL database. However, the Access front end pulls some data from a DB2 system, locally processes it and then pushes it into SQL. Currently the Prod system in Access / SQL 2005, processes the whole 250 query proceduer in 1hr 45m, now with exactly the same setup but in 2008, takes 6 hours.

The spec of the new test box is much better than the current Prod box, so no concerns there. I've ruled out any network issues, as this box is used for quite a few production databases.

I think it is a communication problem between the way Access is processing data at the Client side. Taking a very simple append as an example from a local table into SQL 2008, which took in Prod (2005) took 20 mins to append 1.2M records, now takes over 2 hours to complete.
I have changed the driver to user Native Client 10.0 rather than SQL Server driver as previously.

I'm not in a position to start moving over the queries to SSIS as there is simply too many, or infact re-develop in an ADP, so I have to work with what I've got, annoyingly.

Any suggestions what may cause the process to run so slowly? Anything to check or suggestions?

I was under the impression that SQL 2008 and 2005 were very similar so wasn't expecting to run into any issues. There is something different about the way that Access must process the data or the connection to SQL that is causing the problem.

Any help appreciated.

Chris