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
Run 32 bit excel over 64 bit may be the most ideal thing to do.
@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?
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
Solved: Instead of "sas.LocalProvider.9.4", it's "sas.LocalProvider.9.43".
@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"
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.