Running SQL Server job from Access

I rewrote several Access programs to run as a single SQL stored procedure on
SQL Server. A job created in DTS executes the SQL stored procedure. This
job is executed from a batch file as listed below:
.....
Dim RetVal
Dim strPathFile
strPathFile = "C:\BTExe.cmd"
RetVal = Shell(strPathFile,1)
......
As soon as the code is submitted it returns the return value. Is there any
way to have the job operate as if it was being run within Access ? For
example, display the hour glass until the job completes. Is there a
recommended procedure to run jobs from Access on SQL server, which prevents
the user from using the system until the job completes ?"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
>I rewrote several Access programs to run as a single SQL stored procedure
>on
> SQL Server. A job created in DTS executes the SQL stored procedure. This
> job is executed from a batch file as listed below:
> .....
> Dim RetVal
> Dim strPathFile
> strPathFile = "C:\BTExe.cmd"
> RetVal = Shell(strPathFile,1)
> ......
> As soon as the code is submitted it returns the return value. Is there
> any
> way to have the job operate as if it was being run within Access ? For
> example, display the hour glass until the job completes. Is there a
> recommended procedure to run jobs from Access on SQL server, which
> prevents
> the user from using the system until the job completes ?
>
Why don't you just run the stored procedure directly from Access. You can
do this with a pass-through query or in code.
David|||Can you give an example of the code and would the procedure run the same wa
y
a procedure runs in Access in that the user cannot use the system until the
procedure is complete ?
"David Browne" wrote:

> "rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
> news:143795DE-B03D-418E-99D3-A946461A3016@.microsoft.com...
> Why don't you just run the stored procedure directly from Access. You ca
n
> do this with a pass-through query or in code.
> David
>|||Hi rmcompute
Here is a real live example. Access will stop when it hits cmd.execute and
not continue until the stored Procedure executes.
Sub DoSomething
dim cmd as ADODB.Command
dim conn as ADODB.Connection
Set cmd = New ADODB.Command
'Assuming I want to connection to the SQLAlerts database and my sa
password is "sapassword".
strconnect = GetConnectionString(strSQLServerName, "SQLAlerts",
"sa", "sapassword", False)
set conn = New ADODB.Connection
conn.ConnectionTimeout = 15
conn.Open strconnect
Set cmd.ActiveConnection = conn
cmd.CommandText = " record_sql_server_database_backup_histor
y" '
My Stored Procedure Name
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@.sql_server_database_id",
adInteger, adParamInput, , lngSqlServerDatabaseID)
cmd.Parameters.Append cmd.CreateParameter("@.run_date", adDBDate,
adParamInput, , Now)
cmd.Parameters.Append cmd.CreateParameter("@.last_full_backup",
adDBDate, adParamInput, , dtLastFullBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_incremental_backup", adDBDate, adParamInput, ,
dtLastIncrementalBackup)
cmd.Parameters.Append
cmd.CreateParameter("@.last_transactionlog_backup", adDBDate, adParamInput, ,
dtLastTransactionLogBackup)
cmd.Execute
END SUB
Function GetConnectionString(strSQLServerName As String, strDatabaseName As
String, strUsername As String, strPassword As String, blnNTAuthentication As
Boolean)
If blnNTAuthentication Then
GetConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=" &
strDatabaseName & ";Data Source=" & strSQLServerName
Else
Dim strPassword 'As String
GetConnectionString = "Provider=SQLOLEDB.1;Password=" &
strPassword & ";Persist Security Info=True;User ID=" & strUsername &
";Initial Catalog=" & strDatabaseName & ";Data Source=" & strSQLServerName
End If
End Function
-Dick Christoph
"rmcompute" <rmcompute@.discussions.microsoft.com> wrote in message
news:06EB646F-DD8F-40C6-AEA2-B612E6D7A3AB@.microsoft.com...
> Can you give an example of the code and would the procedure run the same
> way
> a procedure runs in Access in that the user cannot use the system until
> the
> procedure is complete ?
>
> "David Browne" wrote:
>