Execute a SQL Server stored procedure from MS Access

sql vba stored-procedures sql-server-2012 ms-access-2013
Joanna picture Joanna · Apr 12, 2015 · Viewed 9.1k times · Source

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!!!

Answer

Gord Thompson picture Gord Thompson · Apr 12, 2015

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:

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

EXEC dbo.addContact N'Thompson'

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

EXEC dbo.addContact N'O''Rourke'

Related questions

Accessing SQL Database in Excel-VBA

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007): Sub GetDataFromADO() 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = …

Read More
How to insert values into the database table using VBA in MS access

I've started to use access recently. I am trying to insert a few rows into the database; however, I am stuck as it is throwing an error: Too few parameters. I have a table test with only one column in …

Read More
MS Access - execute a saved query by name in VBA

How do I execute a saved query in MS Access 2007 in VBA? I do not want to copy and paste the SQL into VBA. I rather just execute the name of the query. This doesn't work ... VBA can't find the …

Read More