DATA Step, Macro, Functions and more

VBA run-time error 3706

Reply
Contributor
Posts: 41

VBA run-time error 3706

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

Occasional Contributor
Posts: 17

Re: VBA run-time error 3706

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

Highlighted
Super User
Posts: 13,507

Re: VBA run-time error 3706


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?

Contributor
Posts: 41

Re: VBA run-time error 3706

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

Contributor
Posts: 41

Re: VBA run-time error 3706

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

Super User
Posts: 13,507

Re: VBA run-time error 3706


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

Ask a Question
Discussion stats
  • 5 replies
  • 141 views
  • 0 likes
  • 3 in conversation