Results 1 to 4 of 4
  1. #1
    jsucupira is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    2

    TSQL problem on Access 2007 and UP

    Hello,
    After upgrading to office 2010 I am not able to make changes to my store procedures. They all work, but every time I try to modify it or even just saving without any modification I get an ADO error.

    Here is one of my store procedures:
    Code:
    Create PROCEDURE TestSP
    (
    @Course varchar(10) = '%', 
    @Location varchar(50) = '%', 
    @Term varchar(41) = '%', 
    @Type varchar(20)  = '%', 
    @Status Varchar(20) = '%'
    )
    as
    
    set nocount on
            
    
             SELECT DISTINCT Link, Session, Term, Type 
              FROM  dbo.PFMain
              WHERE   (Course LIKE @Course) AND (Location LIKE @Location) AND (Term LIKE @Term) AND (Type LIKE @Type) AND (Status LIKE @Status) 
    
    Return
    For this Store Procedure I get the following error: ADO error: Incorrect syntax near the keyword 'PROCEDURE'. Must declare the variable '@Status'.



    Thanks in advance for the help.

  2. #2
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    As far as i'm aware, you don't need to bracket any objects or variables...

    Below is the first part of a procedure i use in SQL Server:

    Code:
    
    ALTERPROCEDURE [dbo].[importahd]
    @PathFileName nvarchar(2000),
    @tblname nvarchar(50)
    AS
    BEGIN
    DECLARE @SQL nvarchar(2000)
    ....
    ....
    
    Note the DECLARE.

    Perhaps it's very different in TSQL, but hopefully this helped a bit.

    Good luck

  3. #3
    jsucupira is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    2
    Quote Originally Posted by Rixxe View Post
    As far as i'm aware, you don't need to bracket any objects or variables...

    Below is the first part of a procedure i use in SQL Server:

    Code:
    ALTERPROCEDURE [dbo].[importahd]
    @PathFileName nvarchar(2000),
    @tblname nvarchar(50)
    AS
    BEGIN
    DECLARE @SQL nvarchar(2000)
    ....
    ....
    
    Note the DECLARE.

    Perhaps it's very different in TSQL, but hopefully this helped a bit.

    Good luck
    Bracket or no bracket it still gives me the same error.

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,015
    Hi,

    I don't think it's the brackets. You don't need them, but I don't think they're harmfull. And in my knowledge you don't need a DECLARE section if you don't have any variables to declare.
    I had some troubles to after upgrading but that was after upgrading both the back-end database from SQL 2000 to 2008 and Access from 2007 to 2010.
    I found much depends on the version combination back-end/front-end version.

    I solved most of my problems by enabling the TCP/IP protocol in the SQL server Configuration manager.

    greeting
    NoŽlla

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with Access 2007
    By HatterasSolutions in forum Access
    Replies: 2
    Last Post: 11-03-2010, 05:00 PM
  2. Dlookup Problem in Access 2007
    By doquan0 in forum Programming
    Replies: 1
    Last Post: 08-27-2010, 05:41 AM
  3. Problem with label in Access 2007
    By Madraykin in forum Forms
    Replies: 4
    Last Post: 02-04-2010, 10:41 AM
  4. Access 2007 Users and Permissions Problem
    By botts121 in forum Security
    Replies: 3
    Last Post: 07-06-2009, 10:23 AM
  5. Problem using the combo box Access 2007
    By PATATE in forum Access
    Replies: 11
    Last Post: 05-23-2009, 06:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums