Calling Stored Procedures

[Jechols]

Does anyone know of a good book/site to quickly learn the fundamental steps of calling stored procedures from Access? Thanks so much.

 

[NoCoolHandle]

Access doesn't have storedprocs..

It has functions and sub procs...

What do you want?

 

[RiverGuy]

That depends on some variables. If you turn ANSI SQL on, I believe Access will allow you to write stored procedures. If memory serves me correctly, you also categorize saved queries as stored procedures in VB.Net to get them working with the OLEDB provider. This may only be non-select queries.

 

[NoCoolHandle]

Wow@! Sorry.. didn't know that. What version of Access can do that?

 

[RiverGuy]

I have no idea which version it started with.

 

[dswitzer]

You can run other queries(sp's) besides select as well -- INSERT/UPDATE/APPEND etc...

I copied this from somewhere and would give credit if I knew it....when you need to call an sp in access:

Call execute_storedProc("sp_name")

which calls this (ExecuteNonQuery tells it not to expect any return values -- just runs the sp's):

Friend Sub execute_storedProc(ByVal sp As String)
        Dim nextsortOrder As Integer
        Dim cmd1 As New OleDb.OleDbCommand
        With cmd1
            .Connection = objConn
            .CommandType = CommandType.StoredProcedure
            .CommandText = sp
        End With
        objConn.Open()
        nextsortOrder = cmd1.ExecuteNonQuery()
        objConn.Close()
End Sub

 

[Jechols]

Thanks so much for all the help!

Jennifer