<% 'Option Explicit Response.Buffer = True Private Sub SQLExec(byVal sql) dim c set c = OpenDatabase(database_full_path) c.Execute sql c.Close set c = Nothing Response.Write "

PROCEDURE ADDED!

" End Sub dim tmp : tmp = Request.Form("SQL") If left( ucase( trim( tmp ) ), 16 ) = "CREATE PROCEDURE" OR _ left( ucase( trim( tmp ) ), 14 ) = "DROP PROCEDURE" Then Call SQLExec( trim( tmp ) ) Else %>




<% End If %> [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <6a68ace3.0408192354.685b8aef@posting.google.com> [ref] => <6a68ace3.0408182038.62b9d52b@posting.google.com> <#ZJyFCfhEHA.2812@tk2msftngp13.phx.gbl> <6a68ace3.0408191601.73b8380e@posting.google.com> [htmlstate] => on_nl2br [postusername] => Mike [ip] => mike_newsgroups [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 11 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Access 2000 and creating stored procedures - ASP Database

Access 2000 and creating stored procedures - ASP Database

I have Access 2000 and I'm trying to used stored procedures. I'm trying to create the procedure from ASP to avoid the query editor in Access from mangling my SQL. I keep getting the same error message: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement. /revised/trade_3.asp, line 81 I reduced the stored procedure down to a very simple one: cn.Execute "create procedure a (p1 string) as select * from Mini where name = p1" which I copied out of a post by someone else on this group but I get ...

  1. #1

    Default Access 2000 and creating stored procedures

    I have Access 2000 and I'm trying to used stored procedures. I'm
    trying to create the procedure from ASP to avoid the query editor in
    Access from mangling my SQL. I keep getting the same error message:

    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE
    statement.
    /revised/trade_3.asp, line 81

    I reduced the stored procedure down to a very simple one:

    cn.Execute "create procedure a (p1 string) as select * from Mini where
    name = p1"

    which I copied out of a post by someone else on this group but I get
    the same error.

    I even tried this:

    cn.Execute "create procedure a as select * from Mini"

    but I get exactly the same error. it seems like Access 2000 doesn't
    recognize "create procedure" but I've found many articles with
    examples using this statement. Do I have some outdated component, or
    am I doing something wrong?

    Thanks,
    Mike
    Mike Guest

  2. #2

    Default Re: Access 2000 and creating stored procedures

    Read http://www.aspfaq.com/show.asp?id=2214 & search for "Stored
    Procedure" (without the quotes of course).

    This will give you more information about access.

    Also http://www.aspfaq.com/show.asp?id=2201 has some information about
    that topic.

    Don't forget about http://www.aspfaq.com/show.asp?id=2080 as it lists
    some other vital data for you as well.

    Mike wrote:
     

    joker Guest

  3. #3

    Default Re: Access 2000 and creating stored procedures

    Mike wrote: 

    You cannot use ODBC for this. You have to use the native Jet 4.0 OLEDB
    provider. A simple OLEDB connection string looks like this:

    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" &
    "Data Source= p:\ath\to\database.mdb"
    cn.open sConnect
     

    I would surround the p1 with brackets: " ... where [name] = [p1]"


    You can also use ADOX for this task. Do a search at
    msdn.microsoft.com/library for details.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  4. #4

    Default Re: Access 2000 and creating stored procedures

    joker <com> wrote in message news:<eoTtv#phx.gbl>... 

    Thanks for the response, but I checked out all of those before writing
    to the newsgroup. Could you elaborate why you think those are
    relevant to my "CREATE TABLE" error?


    I found a Microsoft example of creating a stored procedure here:
    http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q202/1/16.asp&NoWebContent=1

    Here's what they do:

    strProc = "Create Procedure qryCustByCity " & _
    "(prmCity varchar) as " & _
    "select * from Customers where City = prmCity"

    CurrentProject.Connection.Execute strProc

    Here's my version:

    sql = "Create Procedure FindMini (miniToFind integer) as Select * from
    Mini where id = miniToFind"

    cn.Execute sql


    And I get:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE
    statement.
    /revised/trade_3.asp, line 89


    I found in this article that I have the latest version of jetsql which
    includes a security update "Vulnerability in the Microsoft Jet
    Database Engine could permit code execution". Is it possible that
    they have disabled some aspect of jetsql that I need to create
    procedures from ASP?
    http://support.microsoft.com/default.aspx?kbid=239114&product=access2000

    Could someone else try running the above code in asp using Access
    2000?

    Thanks,
    Mike
    Mike Guest

  5. #5

    Default Re: Access 2000 and creating stored procedures

    Create the query in access instead of using ASP to do it.

    Mike wrote:
     
    >
    >
    > Thanks for the response, but I checked out all of those before writing
    > to the newsgroup. Could you elaborate why you think those are
    > relevant to my "CREATE TABLE" error?
    >
    >
    > I found a Microsoft example of creating a stored procedure here:
    > http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q202/1/16.asp&NoWebContent=1
    >
    > Here's what they do:
    >
    > strProc = "Create Procedure qryCustByCity " & _
    > "(prmCity varchar) as " & _
    > "select * from Customers where City = prmCity"
    >
    > CurrentProject.Connection.Execute strProc
    >
    > Here's my version:
    >
    > sql = "Create Procedure FindMini (miniToFind integer) as Select * from
    > Mini where id = miniToFind"
    >
    > cn.Execute sql
    >
    >
    > And I get:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    > [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE
    > statement.
    > /revised/trade_3.asp, line 89
    >
    >
    > I found in this article that I have the latest version of jetsql which
    > includes a security update "Vulnerability in the Microsoft Jet
    > Database Engine could permit code execution". Is it possible that
    > they have disabled some aspect of jetsql that I need to create
    > procedures from ASP?
    > http://support.microsoft.com/default.aspx?kbid=239114&product=access2000
    >
    > Could someone else try running the above code in asp using Access
    > 2000?
    >
    > Thanks,
    > Mike[/ref]

    joker Guest

  6. #6

    Default Re: Access 2000 and creating stored procedures

    Also stop using ODBC read both of the following pages.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/ado_deprecated_components.asp

    http://www.aspfaq.com/show.asp?id=2126

    You also missed the note in the summary which says "You must use the
    Visual Basic Environment with ActiveX Data Objects (ADO) or the Database
    Definition Language (DDL)." This is another reason to stop using ODBC
    as it is NOT supported for this feature. Also ODBC has been listed on
    the "Deprecated Components" for the MDAC starting with ADO version 2.8.

    Mike wrote:
     
    >
    >
    > Thanks for the response, but I checked out all of those before writing
    > to the newsgroup. Could you elaborate why you think those are
    > relevant to my "CREATE TABLE" error?
    >
    >
    > I found a Microsoft example of creating a stored procedure here:
    > http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q202/1/16.asp&NoWebContent=1
    >
    > Here's what they do:
    >
    > strProc = "Create Procedure qryCustByCity " & _
    > "(prmCity varchar) as " & _
    > "select * from Customers where City = prmCity"
    >
    > CurrentProject.Connection.Execute strProc
    >
    > Here's my version:
    >
    > sql = "Create Procedure FindMini (miniToFind integer) as Select * from
    > Mini where id = miniToFind"
    >
    > cn.Execute sql
    >
    >
    > And I get:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    > [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE
    > statement.
    > /revised/trade_3.asp, line 89
    >
    >
    > I found in this article that I have the latest version of jetsql which
    > includes a security update "Vulnerability in the Microsoft Jet
    > Database Engine could permit code execution". Is it possible that
    > they have disabled some aspect of jetsql that I need to create
    > procedures from ASP?
    > http://support.microsoft.com/default.aspx?kbid=239114&product=access2000
    >
    > Could someone else try running the above code in asp using Access
    > 2000?
    >
    > Thanks,
    > Mike[/ref]

    joker Guest

  7. #7

    Default Re: Access 2000 and creating stored procedures

    I just also noticed the VB being used is not vbscript but VBA which is a
    part of access. This means you may have to make some changes between
    the two languages.

    Mike wrote:
     
    >
    >
    > Thanks for the response, but I checked out all of those before writing
    > to the newsgroup. Could you elaborate why you think those are
    > relevant to my "CREATE TABLE" error?
    >
    >
    > I found a Microsoft example of creating a stored procedure here:
    > http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q202/1/16.asp&NoWebContent=1
    >
    > Here's what they do:
    >
    > strProc = "Create Procedure qryCustByCity " & _
    > "(prmCity varchar) as " & _
    > "select * from Customers where City = prmCity"
    >
    > CurrentProject.Connection.Execute strProc
    >
    > Here's my version:
    >
    > sql = "Create Procedure FindMini (miniToFind integer) as Select * from
    > Mini where id = miniToFind"
    >
    > cn.Execute sql
    >
    >
    > And I get:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    > [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE
    > statement.
    > /revised/trade_3.asp, line 89
    >
    >
    > I found in this article that I have the latest version of jetsql which
    > includes a security update "Vulnerability in the Microsoft Jet
    > Database Engine could permit code execution". Is it possible that
    > they have disabled some aspect of jetsql that I need to create
    > procedures from ASP?
    > http://support.microsoft.com/default.aspx?kbid=239114&product=access2000
    >
    > Could someone else try running the above code in asp using Access
    > 2000?
    >
    > Thanks,
    > Mike[/ref]

    joker Guest

  8. #8

    Default Re: Access 2000 and creating stored procedures

    Mike wrote: 
    Why are you ignoring my response? I clearly explained that CREATE PROCEDURE
    is not supported by the Access ODBC driver and that you need to switch to
    the Jet 4.0 OLEDB provider to use it.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  9. #9

    Default Re: Access 2000 and creating stored procedures

    "Bob Barrows [MVP]" <SPAMcom> wrote in message news:<#phx.gbl>... 
    >
    > You cannot use ODBC for this. You have to use the native Jet 4.0 OLEDB
    > provider. A simple OLEDB connection string looks like this:
    >
    > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" &
    > "Data Source= p:\ath\to\database.mdb"
    > cn.open sConnect

    >
    > I would surround the p1 with brackets: " ... where [name] = [p1]"
    >
    >
    > You can also use ADOX for this task. Do a search at
    > msdn.microsoft.com/library for details.
    >
    > Bob Barrows[/ref]


    It works! Thanks guys! Is there a way of viewing the stored
    procedures in Access 2000? I looked under Queries but the stored
    procedure I created isn't there. How do you know how many you have,
    what their names are, etc...?

    -Mike
    Mike Guest

  10. #10

    Default Re: Access 2000 and creating stored procedures

    Mike wrote:
     
    >
    >
    > It works! Thanks guys! Is there a way of viewing the stored
    > procedures in Access 2000? I looked under Queries but the stored
    > procedure I created isn't there.[/ref]

    No, saved queries created from external applications are not visible within
    Access.
     

    Use ADOX to enumerate them. I think Aaron has an introductory article about
    using ADOX on his site (aspfaq). I'm sure you can also find articles at
    4guysfromrolla, aspin, etc.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  11. #11

    Default Re: Access 2000 and creating stored procedures

    Hmm OK after I figure out what ADOX is...

    Here's some code for adding stored procedures from a web page (so in
    Access your sql doesn't get mangled!!!) I found it on the net and
    adapted it... sorry can't find the page its from but it works
    wonderfully.

    <!--#include file="settings.inc"-->
    <!--#include file="utilityfunctions.inc"-->

    <%
    'Option Explicit
    Response.Buffer = True

    Private Sub SQLExec(byVal sql)
    dim c
    set c = OpenDatabase(database_full_path)
    c.Execute sql
    c.Close
    set c = Nothing
    Response.Write "<h1><big>PROCEDURE ADDED!</big></h1>"
    End Sub


    dim tmp : tmp = Request.Form("SQL")
    If left( ucase( trim( tmp ) ), 16 ) = "CREATE PROCEDURE" OR _
    left( ucase( trim( tmp ) ), 14 ) = "DROP PROCEDURE" Then
    Call SQLExec( trim( tmp ) )
    Else
    %>
    <FORM ACTION="./addsp.asp" METHOD=POST>
    <INPUT TYPE=SUBMIT VALUE="Add Procedure"><BR><BR>
    <TEXTAREA NAME="SQL" COLS=65 ROWS=45></TEXTAREA><BR><BR>
    <INPUT TYPE=SUBMIT VALUE="Add Procedure"></FORM>
    <%
    End If
    %>
    Mike Guest

  12. #12

    Default Re: Access 2000 and creating stored procedures

    > Use ADOX to enumerate them. I think Aaron has an introductory article about 

    Here it is:
    http://www.aspfaq.com/show.asp?id=2037

    It searches for a string... is it possible to search for "" and find
    all of them? I don't know if I left some garbage stored procedures in
    my database by accident...
    Mike Guest

Similar Threads

  1. Replies: 1
    Last Post: September 27th, 01:51 PM
  2. Replies: 0
    Last Post: September 4th, 01:34 PM
  3. stored procedures in access
    By Bob Barrows in forum ASP Database
    Replies: 0
    Last Post: July 25th, 06:34 PM
  4. Calling stored procedures from Access modules
    By Rik Hess in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:44 PM
  5. Stored procedures and UNION (SQL Server 2000)
    By Marius Horak in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 7th, 01:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139