jet 4 stored procs

  • Thread starter Thread starter sparkane
  • Start date Start date
S

sparkane

I'm trying to create a stored procedure in access 2k with:

Public Function MakeProc() As Boolean
Dim sSQL As String

sSQL = "CREATE PROCEDURE procGetMaxAcctID (lID LONG) AS " & _
"select fAcctID from tAccount " & _
"where fAcctID = lID;"
CurrentDb.Execute sSQL
End Function


I keep getting error 3290, "Syntax error in Create Table statement." My
first guess is that there's something wrong with my Jet 4.0
installation. I have the files on my computer, however. Is there
something special I need to do to get access to recognize these files?
I have a reference to DAO 3.6 in my db references. Thanks in advance
for any help.
 
sparkane said:
I'm trying to create a stored procedure in access 2k with:

Public Function MakeProc() As Boolean
Dim sSQL As String

sSQL = "CREATE PROCEDURE procGetMaxAcctID (lID LONG) AS " & _
"select fAcctID from tAccount " & _
"where fAcctID = lID;"
CurrentDb.Execute sSQL
End Function


I keep getting error 3290, "Syntax error in Create Table statement." My
first guess is that there's something wrong with my Jet 4.0
installation. I have the files on my computer, however. Is there
something special I need to do to get access to recognize these files?
I have a reference to DAO 3.6 in my db references. Thanks in advance
for any help.
Click to expand...

Umm, Jet doesn't have Stored Procedures (never has).
 
Try

CurrentProject.Connection. Execute sSQL

Notes:

1. IIRC, a lot of new JET syntax elements don't work through the QBE or DAO
and you need to use ADO.

2. I am not sure whether you use MDB or ADP but with MDB, the Proc is
created but you can't see it in the Database Containers window (at least in
my A2K).

3. Note that the Execute statement above simply creates the Proc. To
actually run the Proc (and since the action is selection), you need to open
the (ADODB) Recordset using the Proc as the Source.
 
Try

CurrentProject.Connection. Execute sSQL

Notes:

1. IIRC, a lot of new JET syntax elements don't work through the QBE or DAO
and you need to use ADO.

2. I am not sure whether you use MDB or ADP but with MDB, the Proc is
created but you can't see it in the Database Containers window (at least in
my A2K).

3. Note that the Execute statement above simply creates the Proc. To
actually run the Proc (and since the action is selection), you need to open
the (ADODB) Recordset using the Proc as the Source.
Click to expand...

Thanks. I tried using ADO and it does work that way. I read about jet
4 sps but nothing of what I read explicitly stated that they wouldn't
work with DAO -- annoying.

It seems to me that the best use for the jet 4 sps would be to replace
insert or update queries, because it takes less code to run the sps than
such queries (just calling the proc with variables, as opposed to
defining the query w a querydef, defining the parameters and then
inserting the data you need to store into the parameters).

However, there's obviously a downside in that the sps appear difficult
to manage. The database needs special code just for the user to see
what procs may be defined. Is there an object in ADO from which I can
get a list of the existing procs? If there is, then at least I could
create a form which would allow management of sps.

spark
 
However, there's obviously a downside in that the sps appear difficult
Is there an object in ADO from which I can
get a list of the existing procs? If there is, then at least I could
create a form which would allow management of sps.
Click to expand...

I see that I can identify sps in the MSysObjects table. However a sp
doesn't seem to appear in the MSysQueries table.

spark
 
I see that I can identify sps in the MSysObjects table. However a sp
doesn't seem to appear in the MSysQueries table.
Click to expand...

oops, sorry. I can using the object id number. Sorry for all this
babble. My original questions still stands, regarding some ADO object
that provides easy access to sps. Thanks again.

spark
 
Sorry, don't know.

I still stick to DAO and QueryDef for JET back-ends (and ADO for MS-SQL
back-end or JET that is going to be converted to MS-SQL).

According to the general consensus, DAO is more efficient than ADO for JET
since DAO is especially built for JET while ADO is a general interface that
add another layer into the process.
 
Back
Top