provider not found error message powershell+access2010+oledb
hello all,
open error when attempting use oledb connect local microsoft access 2010 database. have win 7 64 bit, office 2010 32 bit, access 2010 redistributable installed.
script output error message (some config info requested in script):
dot net library information
major minor build revision
----- ----- ----- --------
4 0 30319 296
***************
powershell_ise.exe.config file
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup uselegacyv2runtimeactivationpolicy="true">
<supportedruntime version="v4.0" />
</startup>
</configuration>
***************
database path validated
exception calling "open" "1" argument(s): "provider cannot found. may not
be installed."
at c:\users\snowstorm\documents\scripts\test access.ps1:55 char:3
+ $connection.open("provider= microsoft.ace.oledb.12.0;data source=$db" )
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ categoryinfo : notspecified: (:) [], methodinvocationexception
+ fullyqualifiederrorid : commethodtargetinvocation
hoping , not end i've missed quote or something. i've commented resources , references in script preable.
powershell script:
<#
script basis:
http://technet.microsoft.com/en-us/magazine/2009.05.scriptingguys.aspx?pr=blog
for open error message in connection string can determine library by:
$psversiontable.clrversion
http://ppe.blogs.msdn.com/b/psssql/archive/2012/08/09/powershell-and-alwayson-gotcha-exception-setting-quot-connectionstring-quot-quot-keyword-not-supported-applicationintent-quot.aspx
to use net 4.x library in ise
$pshome\powershell_ise.exe.config
follow instructions in link
http://stackoverflow.com/questions/2094694/how-can-i-run-powershell-with-the-net-4-runtime
missing data connectivity libraries resolved at:
download microsoft access database engine 2010 redistributable from official microsoft download center
downloaded 32bit access engine
>>provider argument of connectionstring property “microsoft.ace.oledb.12.0”
http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255
#>
function check-path($db)
{
if(!(test-path -path (split-path -path $db -parent)))
{
throw "$(split-path -path $db -parent) not exist"
}
else
{
if(!(test-path -path $db))
{
throw "$db not exist"
}
}
"database path validated"
} #end check-path
function connect-database($db, $tables)
{
#$adopenstatic = 3
#$adlockoptimistic = 3
$connection = new-object -comobject adodb.connection
$connection.open("provider= microsoft.ace.oledb.12.0;data source=$db" )
# update-url
} #end connect-database
function get-url
{
}
function update-url
{
"updating url"
$urlinformation = get-url
foreach($urlinfo in $urlinformation)
{
$recordset.addnew()
$recordset.fields.item("url") = get-date
$recordset.update()
}
} #end update-url
# *** entry point script ***
cls
"dot net library information"
$psversiontable.clrversion
"***************"
"powershell_ise.exe.config file"
get-content $pshome\powershell_ise.exe.config
"***************"
$db = "c:\users\snowstorm\documents\web hosting project\icecapapi\icecapapi.accdb"
$tables = "url"
check-path -db $db
connect-database -db $db -tables $tables
powershell config information script:
cls
"get-host output"
get-host
"$psversiontable output"
$psversiontable
yields:
get-host output
name : windows powershell ise host
version : 3.0
instanceid : 72aac89c-092f-4d99-8b79-aabc73edebad
ui : system.management.automation.internal.host.internalhostuserinterfa
ce
currentculture : en-us
currentuiculture : en-us
privatedata : microsoft.powershell.host.ise.iseoptions
isrunspacepushed : false
runspace : system.management.automation.runspaces.localrunspace
system.management.automation.psversionhashtable output
key : wsmanstackversion
value : 3.0
name : wsmanstackversion
key : pscompatibleversions
value : {1.0, 2.0, 3.0}
name : pscompatibleversions
key : serializationversion
value : 1.1.0.1
name : serializationversion
key : buildversion
value : 6.2.9200.16398
name : buildversion
key : psversion
value : 3.0
name : psversion
key : clrversion
value : 4.0.30319.296
name : clrversion
key : psremotingprotocolversion
value : 2.2
name : psremotingprotocolversion
get aceoledb.dll information script:
<#
get oledb library information
#>
cls
sl $env:windir
get-childitem -include aceoledb.dll -path $env:windir -recurse -force |format-list |out-file $env:userprofile\desktop\acereport.txt
yields:
directory: c:\windows\installer\$patchcache$\managed\000041091d009040000000
0000f01fec\14.0.4763
name : aceoledb.dll
length : 383904
creationtime : 3/23/2010 11:55:00 am
lastwritetime : 3/23/2010 11:55:00 am
lastaccesstime : 1/5/2013 12:45:57 pm
versioninfo : file: c:\windows\installer\$patchcache$\managed\00
0041091d0090400000000000f01fec\14.0.4763\aceoledb.dll
internalname: aceoledb
originalfilename: aceoledb.dll
fileversion: 14.0.4760.1000
filedescription: microsoft access database engine ole db
provider
product: microsoft office 2010
productversion: 14.0.4760.1000
debug: false
patched: false
prerelease: false
privatebuild: false
specialbuild: false
language: language neutral
directory: c:\windows\installer\$patchcache$\managed\0000411911000000000000
0000f01fec\14.0.4763
name : aceoledb.dll
length : 383904
creationtime : 3/23/2010 10:55:00 am
lastwritetime : 3/23/2010 10:55:00 am
lastaccesstime : 7/9/2012 8:37:37 pm
versioninfo : file: c:\windows\installer\$patchcache$\managed\00
004119110000000000000000f01fec\14.0.4763\aceoledb.dll
internalname: aceoledb
originalfilename: aceoledb.dll
fileversion: 14.0.4760.1000
filedescription: microsoft access database engine ole db
provider
product: microsoft office 2010
productversion: 14.0.4760.1000
debug: false
patched: false
prerelease: false
privatebuild: false
specialbuild: false
language: language neutral
directory: c:\windows\installer\$patchcache$\managed\0000411911000000000000
0000f01fec\14.0.6029
name : aceoledb.dll
length : 383904
creationtime : 12/27/2010 11:49:22 pm
lastwritetime : 12/27/2010 11:49:22 pm
lastaccesstime : 7/10/2012 8:43:08 pm
versioninfo : file: c:\windows\installer\$patchcache$\managed\00
004119110000000000000000f01fec\14.0.6029\aceoledb.dll
internalname: aceoledb
originalfilename: aceoledb.dll
fileversion: 14.0.6015.1000
filedescription: microsoft access database engine ole db
provider
product: microsoft office 2010
productversion: 14.0.6015.1000
debug: false
patched: false
prerelease: false
privatebuild: false
specialbuild: false
language: language neutral
thank assistance.
given fact have win7 x64 , office 2010 32-bit, i'm wondering if perhaps have 32-bit database drivers installed. in situation, running script 64-bit powershell return error.
could explicitly launch powershell (x86) , run script there confirm behavior?
Windows Server > Windows PowerShell
Comments
Post a Comment