从MS Access执行SQL Server存储过程

I use MS Access 2013 and SQL Server 2012. I have connected my SQL Server database to MS Access. I connect to SQL Server via SQL Server Authentication. I want to execute a stored procedure with a value entered into a textbox in one of my forms. I have been trying to do it for ages now but nothing that I found on this website works for me. Can anyone please help me and give me some tips as to how to write a basic VBA code to execute the procedure? Please help!!!

我使用MS Access 2013和SQL Server 2012.我已将SQL Server数据库连接到MS Access。我通过SQL Server身份验证连接到SQL Server。我想执行一个存储过程，其值在我的一个表单中输入到文本框中。我一直试图这么做多年，但我在这个网站上找到的任何东西都不适合我。任何人都可以帮助我，并给我一些关于如何编写基本的VBA代码来执行该程序的提示？请帮忙！！！

2 个解决方案

#1

Probably the most straightforward way is to create a temporary pass-through query using a DAO.QueryDef object. If you have an existing linked table in Access then you can use its .Connect property (ODBC connection information). All you need to do is set the .SQL property of the QueryDef to call (EXEC) the stored procedure, like this:

可能最直接的方法是使用DAO.QueryDef对象创建临时传递查询。如果在Access中有现有链接表，则可以使用其.Connect属性（ODBC连接信息）。您需要做的就是设置QueryDef的.SQL属性来调用（EXEC）存储过程，如下所示：

Option Compare Database
Option Explicit

Private Sub Command2_Click()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    Set cdb = CurrentDb
    Set qdf = cdb.CreateQueryDef("")
    ' get .Connect property from existing ODBC linked table
    qdf.Connect = cdb.TableDefs("dbo_myContacts").Connect
    qdf.sql = "EXEC dbo.addContact N'" & Replace(Me.Text0.Value, "'", "''") & "'"
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set cdb = Nothing
End Sub

So, for example, if the Text0 text box contains Thompson then the QueryDef will execute

因此，例如，如果Text0文本框包含Thompson，则QueryDef将执行

EXEC dbo.addContact N'Thompson'

and if the text box contains O'Rourke then the QueryDef will execute

如果文本框包含O'Rourke，则QueryDef将执行

EXEC dbo.addContact N'O''Rourke'

#2

After Trial and error this one worked for me

经过试验和错误，这个对我有用

 > Private Sub UpdateItems_Click()
    >     Dim cdb As DAO.Database, qdf As DAO.QueryDef
    >     Set cdb = CurrentDb
    >     Set qdf = cdb.CreateQueryDef("")
    >     ' get .Connect property from existing ODBC linked table
    >     qdf.Connect = cdb.TableDefs("dbo_AccesLinkedTable").Connect
    >     qdf.SQL = "EXEC dbo.YourStoreProcedure"
    >     qdf.ReturnsRecords = False
    >     qdf.Execute dbFailOnError
    >     Set qdf = Nothing
    >     Set cdb = Nothing
    > 
    >  MsgBox "Records Updated!"
    > 
    > End Sub

#1

Option Compare Database
Option Explicit

Private Sub Command2_Click()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
    Set cdb = CurrentDb
    Set qdf = cdb.CreateQueryDef("")
    ' get .Connect property from existing ODBC linked table
    qdf.Connect = cdb.TableDefs("dbo_myContacts").Connect
    qdf.sql = "EXEC dbo.addContact N'" & Replace(Me.Text0.Value, "'", "''") & "'"
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set cdb = Nothing
End Sub