Create Store Procedure

[charon]

1.)I'm using access with ODBC connection. I have tried to use create procedure, but seem like ODBC not supported, instead must use OLEDB


2.) ODBC seem like can't support Transaction (BeginTrans/CommitTrans)

Pls advice!

[charon]

May I Know can access allow to create store procedure??

I failed with this statement:

con.Execute "CREATE PROCEDURE showName AS SELECT * FROM Names "

[Roy Sinclair]

Apparently Access does support simple stored procedures like the one in your example.

[whammy]

Roy, I must say I really like your signature. ;)

[charon]

>>Apparently Access does support simple stored procedures like the one in your example.

If it does, why still has error occur:
con.Execute "CREATE PROCEDURE showName AS SELECT * FROM Names "
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

[whammy]

Got any links Roy?

I'd suggest a google search on this though charon, personally... that's what always helps me to meet a deadline!

Something like "Stored Procudures" + "Access" perhaps? :)

[charon]

whammy,

I did try to search yesterday night, I spent 4 hours just looking for related article.....but failed to get.....
Even has article saying that access can't support SP
Some said only MS access 2000 and 2002 be able to support......

[whammy]

Have you ruled out all other errors, used Option Explicit, etc.? I haven't had the need or occasion to use stored procedures with Access, and I'm still constantly learning T-SQL with SQL Server... so that's the first place I would start, by ruling out code erors.

I don't have any articles handy, but if Roy says they should be supported, I'd definitely take that seriously... I'll look for some tonight

[charon]

No error on coding, why?? as as long as I won't execute the CREATE PROCEDURE, it works great.
Thanks.....Hope can get some news from you...

[Roy Sinclair]

I opened Access 2000 brought up the help window, switched to the index tab and entered "procedure". Looking in the "Choose a topic" list I saw "CREATE PROCEDURE Statement" in the list and clicked on that. That's the basis for the answer I gave and how I got there. Since it says it can contain only one SQL statement it's obviously only good for very simple actions unlike the very complex actions you can perform in stored procedures with SQL Server but Charon's code looks very much like the example so it should be possible if Charon can just figure out why it's being rejected.

[charon]

hi Roy,

ya i know, I did try from the access help before, and I did study that statement. Just don't know why it can't be done. I tried to use OLEDB instead of ODBC, and produce result as below:

Microsoft JET Database Engine (0x80040E14)
Syntax error in CREATE TABLE statement.
/admin/storeproc.asp, line 32

[Roy Sinclair]

As a thought, make sure the version of the Jet engine and the Access database itself you are running on that system is a recent version. If they added that in Access 2000 but the version you're running against is Access 98 then...

[charon]

I'm using access 2000 with the lastest provider Microsoft.Jet.OLEDB.4.0, should be no problem.
I did try ODBC, same error statement [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

[whammy]

P.S. Post the code! :)

[charon]

DataPath = Server.MapPath("../../db/data.mdb")

'con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataPath & ";Jet OLEDB:Database Password=1111"
con.Open "DBQ=" & DataPath & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20;uid=;pwd=1111"

sql = "CREATE PRODECURE listName AS Select * From Names"
con.Execute(sql)

[Roy Sinclair]

If you cut and pasted that code the problem is simple, you mis-spelled procedure in your code. You have "prodecure" instead of "procedure" and that could be the whole problem :eek: .

Don't let it get you down though, most of us see what we "know" is there instead of what's truly there from time to time.

[charon]

Guess they are correct, access not support CREATE PROCEDURE, I got this error.
Operation is not supported for this type of object.

Have you tried??? Do you manage to create???

come back again with something new

Pls access and read:

http://www.programmersheaven.com/zone18/cat705/20405.htm

If they manage to create the store procedure, then i'm sure we can as well....I really don't want to give up...

[raf]

I'll try it this weekend. I'm not a big fan of stored procedures (see another thread of yours) but you've peaked my curiousity :D. I'll let you know if i have any luck.

[raf]

Well, i just wrote this in an asp page, and didn't get any errors on it, so i suppose it works:

<!--#include file='congranit.inc'-->
<%

conGranIT.Execute "CREATE PROCEDURE CountProducts AS Select Count(*) from orders"

conGranIT.Close
set conGranIT = nothing

%>


Try this and tell me if it gives an eror on your machine.