VBA run-time error 3706

[BU2B]

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

[daithi1990]

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

[ballardw]

---

@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?

[BU2B]

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

[BU2B]

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

[ballardw]

---

@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"