Search code examples
sql-servervbaexcelexcel-2013

Run SQL Query From VBA

I am using this syntax to run a SQL Server 2008 stored procedure from Excel 2013. Everything executes as it should on one PC, but if I attempt to run it on a second PC it errors out with an

OLEDB Connection error

Shouldn't since I am hardcoding the servername, username, and password the syntax run w/o a hitch on any PC?

Function RunSQLServerProc()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
con.Open "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=Database;User Id=userid;Password=password;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con
cmd.CommandText = "TestProc"
Set rs = cmd.Execute(, , adCmdStoredProc)
End Function
Solution

  • There are two ways to connect to SQL Server through VBA
    1) Integrated Security=SSPI;
    2) Provide a valid username & password through the VBA

    When you use SSPI, the windows login credentials of the logged in user (i.e. of the Excel workbook) will be used.

    Now for you, since you are not wanting to use windows credentials you need to provide a SQL server logon with the User ID and password you specify, and that must also be associated with an account in the database with the privileges that you need.