Upsizing nested Access queries. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Upsizing nested Access queries.

Hi, I’m looking for some advice if possible. I’ve been asked to look at upsizing 20+ Access 2000 databases to a SQLServer[2000]with an Access 2003 .adp front-end. The Access databases are reporting information from a bespoke transactional database. Currently the Access databases are refreshed daily via an odbc from the transaction database but this is becoming more and more unreliable. I can quite happily build the core of the system on the server but the problem is the shear amount of reports and queries that are being used. There are litterally hundreds of reports and thousands of queries. The reports usually feed off of a set of nested queries sometimes 15 or more deep. The transaction database is into the low millions of records on some tables. For speed purposes i understand that a stored procedure with a single SQL statement would be the fastest way to retrieve the data using memory resident temp tables. However i’m struggling to get my head round the amount of work it’ll take to redesign all the queries. Does anyone know if there is a tool that could at least extract the totality of the SQL from the nested access queries if not actually create the sproc?
The alternative i can think of would be to use nested SQL statements to create temp tables and perform the joins on them sequentially dropping each table as the next one is created. I could more easily extract the SQL from each Access query and with a bit of modification run them in this way, however i’m led to believe that the performance is slower. Any advice would be gratefully received. Thanks for your time. Karma
I hope you’ve got relevant information from Access forums, as you can see this is SQL SErver related forum.
If you still need help let us know. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing.
If you’re very lucky those Access queries are ANSI compliant. Starting with Access 2002 ("XP") they added the option that all queries in the MDB must use "SQL Server Compatible Syntax (ANSI 92)" – but of course your system is Access 2000, meaning there will be non-compliant syntax. You’ll run into Jet-specific keywords, like DISTINCTROW, there probably will be some VBA functions and lots more goodies that will require reprogramming in T-SQL.