Run SQL proc from MS Access (my favourite way)

This is my new favourite way to call SQL procedure using VBA (in my case, from MS Access). You will note the use of the ReturnMsg parameter – I get my text for success, info or errors directly from the procedure. For an example, see the previous post on this blog.

Screen.MousePointer = 11
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDBName;Data Source=MyServerName"

cmd.ActiveConnection = cn
cmd.CommandText = "MySchema.MyProcName"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@queryDateParam", adDate, adParamInput, , cboQueryDate.Value) 'example input parameter
cmd.Parameters.Append cmd.CreateParameter("@ReturnMsg", adVarChar, adParamOutput, 500) 'example output parameter (see previous post for use example)
cmd.CommandTimeout = 0
MsgBox cmd.Parameters("@ReturnMsg").Value
Screen.MousePointer = 0

SQL procedure example for OPENROWSET with TRY CATCH and TRAN

I am posting this example for two reasons. Firstly, it is a great template that I have been using a lot of late so my blog is naturally a good place to store such things. But I also wanted to get this example public because of the time I spent myself looking for a way to catch errors when using the OPENROWSET linked server. As you will see, the answer is in dynamic SQL.

create proc proc_MyProcName (@ReturnMsg varchar(max) output) as begin

     --declare @ReturnMsg varchar(max) --for testing

     declare @dSQL nvarchar(max) -- use dynamic SQL to catch possible errors with linked CSV file
     set @dSQL = '
                  insert into tbl_MyTable
                  select *
                  from OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=D:\DBServerFolder\;HDR=Yes;FORMAT=Delimited(,)'', ''SELECT * FROM [MyImportFile.csv]'')
               -- for excel, use
               -- from OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source="D:\DBServerFolder\TheFile.xlsx"; Extended properties=Excel 12.0'' )...Sheet1$
     begin try
          begin tran
          -- my other statements
          -- ... ... ...
          exec sp_executesql @dSQL
     end try

     begin catch
          if @@TRANCOUNT > 0 begin
               rollback tran
               set @ReturnMsg = 'An error ocurred and the transaction was rolled back.' + char(13) + char(13) + 'The SQL error msg is: ' + ERROR_MESSAGE()
               return -1
               --print @ReturnMsg --for testing
     end catch

     if @@TRANCOUNT > 0  begin
          commit tran
          set @ReturnMsg = 'My success message.'
          return 0
          --print @ReturnMsg --for testing