All started when I wanted to see if I could use a compiled WB script on work PC for processing with ACE Provider. Ran a Powershell script on that machine to list providers and ACE wasn't there. But I noticed when I examined an Excel data link to Access I had set up, the Ace.12.0 Provider was referenced. So I went through creating a WB exe and copying it over and can use ACE there now.
Curious, wanted to see if I could replicate the PS script with the CLR, as when I ran the PS script on my home PC where WB is installed, it showed all Providers including ACE and Jet.
Best I can do is display the Column Names, but not the values. Appreciate if someone can show the error of my ways. BTW: a plan B would be to enumerate via WMI or the registry but I want to stick with CLR
; Winbatch 2020A - List OLEDB Providers
; Stan Littlefield - January 11, 2020
;====================================================================================
ObjectClrOption("useany", "System.Data")
oProv = ObjectClrNew('System.Data.OleDb.OleDbEnumerator')
list = oProv.GetElements()
cList=""
Foreach r in list.Rows
Foreach c in list.Columns
cList = cList:c.ToString():","
Next
cList=cList:@LF
Next
oProv=0
Message("Providers",cList)
Exit
Maybe something along the lines of ....
cList = cList:c.ColumnName:r.Item(0):",":r.Item(1):",":",":r.Item(2):",":",":r.Item(3):","
This probably won't format like you want and isn't complete but might put you on the right track .
Jim
Thanks Jim;
I originally looked at r.Item[0] instead of Item(0) and got a little discouraged there. Took out the Foreach columns. Still don't understand why ACE shows up on my personal PC but not my work one, although ACE can be called there.
; Winbatch 2020A - List OLEDB Providers
; Stan Littlefield - January 11, 2020
;====================================================================================
ObjectClrOption("useany", "System.Data")
oProv = ObjectClrNew('System.Data.OleDb.OleDbEnumerator')
list = oProv.GetElements()
cList=""
n=list.Columns.Count
Foreach c in list.Columns
cList = cList:c.ToString():","
Next
cList=cList:@CRLF
Foreach r in list.Rows
For i=0 To n-1
cList = cList:r.Item(i):","
Next
cList=cList:@CRLF
Next
oProv=0
Message("Providers",cList)
Exit
You have likely already thought of this but could your "provider missing" problem be a bitness issue? You would get a different list executing your script as 32-bit as opposed to 64-bit. The Jet provider is only available as 32-bit.
Quote from: td on January 13, 2020, 08:32:03 AM
You have likely already thought of this but could your "provider missing" problem be a bitness issue? You would get a different list executing your script as 32-bit as opposed to 64-bit. The Jet provider is only available as 32-bit.
Correct sir! I used 64 bit PS and either it or .NET on a 64-bit system only enumerate Providers that support 64-bit. Running the 32-bit PS on a 64-bit system showed all providers.
So, here is neat trick for 64-bit: open notepad, immediately save as test.udl (should create a UDL icon ). double click to open the udl and the Providers tab will show 64-bit providers. Open notepad again and paste in C:\Windows\syswow64\rundll32.exe "C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll",OpenDSLFile <path to test.udl> and save as test.bat. Execute the batch file and see larger provider list