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"
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.