Executing a stored procedure in SQL Server 1

[rushdib]

Hi,
How do I execute a stored procedure in SQL Server from Access 2000 without using Access project? The tables are ODBC linked to SQL Server.

Thanks in advance,

Rushdi

 

[NilsBevaart]

You have to use an ActiveX (ADO) connection for this. This can be done using VBA.
Access Help has examples on how to make a connection and how to send the required T-SQL message to the SQL Server

For more information on ADO connections, also see

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

Nils Bevaart

 

[cheerio]

This is my code using DAO. In effect, I have one procedure to control execution of stored procedures.

Public Sub Execute_SQL_SP(ByRef SPname As String, _
                          ByRef StatusMessage As String, _
                          ByRef TimeoutSecs As Integer, _
                          ByRef ErrMsgTitle As String)
Dim qdf As QueryDef
Dim txt As String
Dim minutes As Integer

On Error GoTo Error_Execute_SQL_SP
Set qdf = CurrentDb().CreateQueryDef("")

txt = StatusMessage & " - timeout "
If TimeoutSecs > 60 Then
    minutes = Int(TimeoutSecs / 60)
    txt = txt & CStr(minutes) & " minutes "
    If TimeoutSecs - 60 * minutes <> 0 Then
        txt = txt & CStr(TimeoutSecs - 60 * minutes) & &quot; seconds&quot;
    End If
Else
    txt = txt & CStr(TimeoutSecs) & &quot; seconds&quot;
End If
Application.Echo True, txt
DoCmd.Hourglass True

With qdf
    .Connect = GetConnect()
    .ReturnsRecords = False
    .sql = &quot;Exec &quot; & SPname
    .ODBCTimeout = TimeoutSecs
End With
qdf.Execute

Exit_Execute_SQL_SP:

Set qdf = Nothing
Application.Echo True
DoCmd.Hourglass False
Exit Sub

Error_Execute_SQL_SP:
txt = &quot;An error occurred: &quot; & Str(Err.Number) & _
        vbCrLf & Err.Description
MsgBox txt, vbCritical + vbOKOnly, ErrMsgTitle
Resume Exit_Execute_SQL_SP
End Sub

In the above replace GetConnect() with the ODBC connection string for the MS SQL database.

This was developed for Access 97. For Access 2000 I would make references to the DAO library explicit eg

Dim qdf As DAO.QueryDef

Ken