Allow Excel Macro to run SQLServer Express 2014 Stored Procedure which updates database

I am working using VBA macros in Excel (2007) to execute Stored Procedures (SP) in a SQL Server Express 2014 instance. When the SP is simply SELECTing data to return, the connection works fine. When it tries to update the database, it errors with the message: 'error 1004 the query did not run, or the database table could not be opened...'

A post on Tek-Tips pointed me to your website, but I can't work out what I need to change in my connection string to allow read/write access.

Here's the string that works for a Read-Only SP:
Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Data Source={pcname}\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID={pcname};Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog={DatabaseName}

Can anyone help me please?

0 answers