从 MS Access 运行 SqlServerr 作业

• 下载 ISD_ex1 - 31,000 KB

引言

本文的目标是从 Microsoft Access 运行 SQLSERVER 作业。

背景

首先，您应该创建作业。在本例中，我创建了一个作业，用于将数据从 sqlserver 表提取到 Excel 表格，然后我创建了一个 Access 数据库，其中包含一个小型窗体和一个按钮来运行此作业，我将其命名为“trstp”。这里的想法是创建一个 ODBC 连接并运行预定义的存储过程 (sp)“sp_start_job”，然后将作业名称作为参数传递给此 sp。

使用代码

Option Compare Database

Private Sub Command7_Click()
Dim sServer As String
Dim sUser As String
Dim sPWD As String
Dim sDatabase As String

Dim DBcon As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objparameter As New ADODB.Parameter
Dim objRs As New ADODB.Recordset

sServer = "(local)"
sDatabase = "msdb"
sUser = "sa"
sPWD = "123456"

DBcon.ConnectionString = "Provider=sqloledb;" & _
"server=" & sServer & ";uid=" & sUser & ";pwd=" & sPWD & ";database=" & sDatabase
DBcon.CursorLocation = adUseClient
DBcon.Open

objparameter.Direction = adParamInput
objparameter.Type = adVarChar
objparameter.Size = 5
objparameter.Value = "testp"
objCmd.Parameters.Append objparameter

objCmd.ActiveConnection = DBcon
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_start_job"
Set objRs = objCmd.Execute ' objCmd.Execute for no resultset

' Set objRs.ActiveConnection = Nothing
Set objCmd = Nothing
DBcon.Close

End Sub