stored procedures

Hi, I know that you can use VBA in Access for the creation of stored procedures, but I am not sure on the syntax. Is it as simple as VB, where I just write a procedure and give it paramaters, or does it need more of an SQL style syntax? I also need to know how I would run these stored procedures from VB. If it can be done through DAO, that would be my preferred method, but any sensible implementation will work for me. Thanx for the help.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I am thinking that the best way to answer your question is to give you a code example.  Also, when calling stored procedures, to keep the performance up and make use of OUTPUT parameters, try using ODBC Direct.  It uses the same libraries as DAO so you need not add any new references.  Also, to learn more about how to write stored procedures, get a Transact-SQL book or check out Books Online that comes shipped with SQL Server.

Here is example code of creating a stored procedure in the NorthWind database (for SQL Server 7 -- if you have SQL Server 6.5 you will have to change the procedure to access pubs or your own user database):

Private Sub CallQDsp()
    Dim qd As QueryDef
    Dim sConnect As String
    Dim sSQL As String, sServer As String
    Dim sLoginID As String, sPassword As String
    Dim cn As Connection
    Dim ws As Workspace
    sLoginID = "sa"
    sPassword = vbNullString
    sServer = "DAREK"
    'The type parameter sets the workspace to ODBCDirect
    Set ws = DBEngine.CreateWorkspace("NewODBCDirectWS", sLoginID, _
                sPassword, dbUseODBC)

    'Set the default Workspace type to ODBCDirect
    DBEngine.DefaultType = dbUseODBC
    'Define the DSN-less connection
    sConnect = "ODBC;Driver=SQL Server;UID=" & sLoginID & _
        ";PWD=" & sPassword & _
        ";Server=" & sServer & _
    'Open the new connection
    Set cn = ws.OpenConnection("", dbDriverComplete, False, sConnect)
    'Create the new procedure
    sSQL = "Create Proc CountStateRows " _
        & "(@state char(2), @rows Int Output) As " _
        & "Select @rows = Count(*) From authors Where state = @state"
    cn.Execute sSQL
    'Call the procedure
    sSQL = "{ Call CountStateRows (? ,?) }"
    Set qd = cn.CreateQueryDef("", sSQL)
    qd.Parameters(0).Value = "UT"
    qd.Parameters(1).Direction = dbParamOutput
    MsgBox "Number of rows: " & qd.Parameters(1).Value
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
By the way, you can also create pass-through queries in Access and type in the stored procedure syntax directly here (as well as calling the stored procedure).

....also, I guess the example I gave IS using pubs and NOT Northwind!!
BeauTAuthor Commented:
OK, will this work with Access too? And also, should I still use ODBCDirect if I am working against an Access database?
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

<<will this work with Access too>>
Absolutely, try it.

<<should I still use ODBCDirect if I am working against an Access database>>
Possibly, although I am not sure what you would gain since I believe the purposes of ODBCDirect was to be able to use external databases and their functionality directly.
BeauTAuthor Commented:
ok, but see, I'm wondering: shouldn't I stick with Jet since I am going through Access?  Or is there a certain advantage for using stored procedures by going through ODBCDirect?
You can not create stored procedures via JET.  You can create stored procedures by creating pass-through queries to bypass JET to go to SQL Server or use ODBCDirect (as mentioned above).  JET uses File Server technology in that processing happens on the local computer, not on the server.  Thus, since stored procedures are not supported by JET you have to call a stored procedure by pass-through queries or ODBCDirect or ADO in VBA.  However, since ODBCDirect does not use any new references (it uses the same object model as DAO which is the default Data Access object model in Access 97) it is very easy to use this -- instead of ADO.
Also, since ODBCDirect bypasses JET it is substantially quicker and also provides you with the ability to use server syntax instead of ones that JET understands.  Whenever you have SQL Server as a backend (and thus are storing large amounts of data) you should be avoiding JET as much as possible since it will slow things down and cause undesired issues like using up extra connections without you knowing it.
I hope this all makes sense, any other questions please ask.  Just be aware that I only check this site a couple of times a day so may not answer your questions right away.
BeauTAuthor Commented:
ok, this definitely seems helpful.  And I am convinced enough to try going through ODBCDirect now.  But there is still one question in my mind: Will your example above (your very first post) work with Access97, or would it need to be changed? I am not very well versed in the intricacies of databases and DAO and the like, but I have a middling knowledge. I would assume that at least the 'Driver' portion of the connection string would have to be changed, but I wouldn't know what to put there. If you could tailor that example to work with an Access97 database, then I think I will have all I need.
First of all, just copy and paste the code I gave you... it should work as is.

As to the other part...
Well, you got me thinking so I went to my trusty SQL Server 7 Developer's Guide by Otey & Conte (I highly recommend it!) book to give you the below exerpt -- basically, ODBCDirect can only be used to connect to ODBC data sources.

"...if your database application is primarily intended to access SQL Server, then you would be better off using Jet's close cousin, ODBCDirect, as a basis for building your database applications..."

"ODBCDirect was developed to address some of the shortcomings that crop up when using standard DAO to access ODBC-based data sources like SQL Server.  ODBCDirect is somewhat of a cross between DAO and RDO.  ODBCDirect uses an object model that's very similar to DAO. Like DAO, ODBCDirect makes use of Workspace, Database, Recordset, Querydef, and Field objects.  Like RDO, ODBCDirect is an object layer over ODBC, and it does not use the Microsoft Jet Database Engine.  In fact, ODBCDirect can only be used to connect to ODBC data sources; it can't be used to connect to local Get databases.  ODBCDirect also supports most of the advanced ODBC abilities that are provided by RDO.  Like RDO, ODBCDirect supports using ODBC cursors, as well as prepared SQL Statements, parameterized queries, and stored procedure return parameters.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.