topic VBA run-time error 3706 in SAS Programming

VBA run-time error 3706

BU2B — Thu, 08 Mar 2018 20:55:41 GMT

Hi all,

I got a new laptop, it's Windows 10 64-bit and I'm running SAS 9.4 32-bit. I had VBA code to read in a SAS dataset into Excel that worked fine in SAS 9.2, but now I'm getting a run-time error 3706 - Provider cannot be found. Here's my old code. Am I missing a driver or does the code need to be modified? I already tried changing the SAS version in the code..

Sub VA3_I()

'*************************************************************************************************************
'This modules grabs the SAS output for the Initial VA1 Resettlement *
'*************************************************************************************************************

Dim obConnection As ADODB.Connection
Dim obRecordset As ADODB.Recordset
Dim i As Integer, strcount As Integer
Dim NSS As Range

Application.StatusBar = "Querying Initial VA1 Swing for CREP " & CREP_Name & "..."

Set obConnection = New ADODB.Connection
obConnection.Provider = "sas.LocalProvider.9.2"
obConnection.Properties("Data Source") = "\\nasdr\c_commodity_acctg\Workfiles\Steve\SAS\Datasets\CREPS\Initial VA1 RS"
obConnection.Open

Set obRecordset = New ADODB.Recordset
obRecordset.Open "etrm_crep_ini_va1_swing_" & Sheets("ProfileRS_Invoice").Range("C4").Value, obConnection, adOpenDynamic, adLockReadOnly, ADODB.adCmdTableDirect

Re: VBA run-time error 3706

daithi1990 — Thu, 08 Mar 2018 22:11:45 GMT

Run 32 bit excel over 64 bit may be the most ideal thing to do.

Re: VBA run-time error 3706

ballardw — Thu, 08 Mar 2018 22:24:52 GMT

@BU2B wrote:

Hi all,

I got a new laptop, it's Windows 10 64-bit and I'm running SAS 9.4 32-bit. I had VBA code to read in a SAS dataset into Excel that worked fine in SAS 9.2, but now I'm getting a run-time error 3706 - Provider cannot be found. Here's my old code. Am I missing a driver or does the code need to be modified? I already tried changing the SAS version in the code..

Sub VA3_I()

'*************************************************************************************************************
'This modules grabs the SAS output for the Initial VA1 Resettlement *
'*************************************************************************************************************

Dim obConnection As ADODB.Connection
Dim obRecordset As ADODB.Recordset
Dim i As Integer, strcount As Integer
Dim NSS As Range

Application.StatusBar = "Querying Initial VA1 Swing for CREP " & CREP_Name & "..."

Set obConnection = New ADODB.Connection
obConnection.Provider = "sas.LocalProvider.9.2"
obConnection.Properties("Data Source") = "\\nasdr\c_commodity_acctg\Workfiles\Steve\SAS\Datasets\CREPS\Initial VA1 RS"
obConnection.Open

Set obRecordset = New ADODB.Recordset
obRecordset.Open "etrm_crep_ini_va1_swing_" & Sheets("ProfileRS_Invoice").Range("C4").Value, obConnection, adOpenDynamic, adLockReadOnly, ADODB.adCmdTableDirect

Anything look odd in the highlighted text considering that you are now running SAS 9.4?

Re: VBA run-time error 3706

BU2B — Fri, 09 Mar 2018 13:25:39 GMT

Good morning,

Yeah that's not it. That was the first thing I tried. Our resident SAS guru is back in the office today. I'll see if he knows and will keep you posted. Thanks!

Steve

Re: VBA run-time error 3706

BU2B — Fri, 09 Mar 2018 15:31:48 GMT

Solved: Instead of "sas.LocalProvider.9.4", it's "sas.LocalProvider.9.43".

Re: VBA run-time error 3706

ballardw — Fri, 09 Mar 2018 21:56:44 GMT

@BU2B wrote:

Solved: Instead of "sas.LocalProvider.9.4", it's "sas.LocalProvider.9.43".

So the actual problem was in the line

obConnection.Provider = "sas.LocalProvider.9.2"

which you had indicated "Yeah that's not it. That was the first thing I tried"