Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 30th, 2003, 12:52 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stored Procedures not available in the designer

Why is it that Stored Procdures are not available when using the View or Stored Procedure Desginer in Access 2002?

I have nested queries in my MDB and I am re-creating them in an ADP but when I tyre to "pull" them in all I see are Tables, Views and Functions.

Thanks for your help,
Old September 30th, 2003, 04:22 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Problem is the upsizing tool simply doesn't try to upsize all your Access queries. Many will have to be manually recreated. The main purpose of the upsizing tool is to deal with your data, that is, tables. For instance, all your data types should map correctly. Expect to have some problems with everything else.

Here is a small litany of problems you may encounter with queries:

1. The following queries are ignored: crosstab queries, parameterized action queries, action queries that contain nested queries,SQL pass-through queries, SQL DDL queries, union queries, queries that reference values on a form.

2. SELECT queries are upsized to VIEWS, but SELECT queries that contain an ORDER BY clause will often fail because Views don't support them.

3. Some action queries may be upsized but not work.

4. Form values used as parameters are a problem because all processing is performed on the server and SQL Server has no way of evaluating form values (can't see them). These queries often get upsized to user-defined functions.

5. The asterisk (*) no longer works as a wild card (you need '%')

6. Parameter queries are upsized to inline functions. (it appears that all parameter queries are upsized to functions).

7. Queries that contain user-defined functions are not upsized.

8. SQL Server and Access use different date ranges.

9. Access's concatenation operator is '&', SQL Server's is '+'.

That should give you a general idea of what's potentially involved in the migration. Definitely not as simple as one might hope.

Stored Procedures that you write on SQL Server (via the Designer of using SQL Server's client tools) will be available in the .adp interface.



Old October 2nd, 2003, 09:01 AM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

If you are trying to view them from an mdb, you will not be able to. You can only view stored procedures residing on a SQL Server from an adp file.


Similar Threads
Thread Thread Starter Forum Replies Last Post
stored procedures MunishBhatia SQL Server 2005 4 April 12th, 2008 01:39 AM
Stored Procedures itHighway SQL Server 2000 3 November 23rd, 2005 10:08 AM
Stored Procedures jazzcatone Classic ASP Databases 0 August 28th, 2005 02:57 PM
Stored Procedures stu9820 ASP.NET 1.x and 2.0 Application Design 2 January 15th, 2005 04:09 AM
Using Stored Procedures lintacious Classic ASP Basics 1 August 12th, 2003 04:06 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.