Many VBA (Visual Basic for Applications) programmers many not be familiar with ways to connect to SQL Server from within Microsoft Office Products like MS-ACCESS, Excel or Word. It is especially useful for database application developers to know how to use VBA to connect to SQL Server and runs some SQL or insert a record. In this post I’ll show a way to create a stored procedure in SQL Server. I’ll then show how to use VBA to connect to the SQL Server. Additionally I’ll show how to use VBA in order to run a SQL Server stored procedure.

In a prior post I demonstrated that a sql server linked table offers performance benefits over using traditional native access tables in some situations where the sql connection is made in a favorable (fast) network environment. Every environment is different, that is the reason for testing. To see an example of testing ms-access tables versus sql server tables (both linked in a MS-Access 2007 database), you can check out the post here.

In that article I uses straight sql to perform the updates, inserts and deletes. However the preferred method is using sql server stored procedures. Creating a routine to perform a simple act like inserting a record is fast is easy. Here is some code to do just that.


CREATE PROCEDURE [dbo].[AddCustomer]
-- Add the parameters for the stored procedure here
@CustomerID nchar(5), @CompanyName nvarchar(40),@ContactName nvarchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for procedure here
insert into dbo.Customers (CustomerID,CompanyName,ContactName)
values
(@CustomerID,@CompanyName,@ContactName)

END

GO

Now that you have the code to do an insert, you can switch to MS-Access and go to the Visual Basic Editor. You can do this if you already have routine named ‘modTestRoutines’ based on the prior post. Otherwise, you can go to ‘Create, Macro, Module’ and you will end up in the Visual Basic Development Environment. Here you want to make sure you have a reference set up to use ADODB. This library allows us to run the stored procedure in Microsoft access.

Set a reference to the adodb 2.8 object as in the screenshot below.

set a reference to adodb 2.8 object library in Visual Basic, Tools, References

After you set a reference to this object library you can dimensionalize ADODB variables used to create connection and command objects.

How do you do that?

First, in the Visual Basic development environment that comes with ms-access 2007, Create a new module. Then Create a subroutine as follows.


Sub AddCustomer(CustID As String, CompanyName As String, ContactName As String)

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection

conn.ConnectionString = “Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=MYTEST-PC\SQLEXPRESS”

conn.Open

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

cmd.ActiveConnection = conn
cmd.CommandText = “AddCustomer”

cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter(“@CustomerID”, adWChar, adParamInput, 5)
cmd.Parameters(“@CustomerID”).Value = CustID

cmd.Parameters.Append cmd.CreateParameter(“@CompanyName”, adWChar, adParamInput, 40)
cmd.Parameters(“@CompanyName”).Value = CompanyName

cmd.Parameters.Append cmd.CreateParameter(“@ContactName”, adWChar, adParamInput, 30)
cmd.Parameters(“@ContactName”).Value = ContactName

cmd.Execute

conn.Close

Set conn = Nothing

Set cmd = Nothing

End Sub

code>

Now that you have both a sql server stored procedure and a VBA sub routine to execute the stored procedure, you can test it out. Note you can change ‘MYTEST-PC’ with your own server name.

In the immediate window type in

AddCustomer "test", "testco","testContact"

and then hit enter.

The routine should add a record.

You can modify the module modTestRoutines to test this out and see if you notice a performance gain. Test this by replacing the insert statement with a call to the AddCustomer subroutine.

In this post we used VBA to call a SQL Server Stored Procedure by first connecting to the SQL Server, and then using an ADODB command object to execute a stored procedure. We also demonstrated how to add parameters and set the values. Additionally we used the immediate window to run the VBA sub routine that executes the SQL Server Stored Procedure.