This are a few examples that attempt to demonstrate how
PowerShell can be used together with
ADO DB to create an
MS Access database, insert some data into a table and select it agein.
When I tried these example on a 64-bit Windows machine, I had to make sure that I ran a
32-bit PowerShell executable. With the 64-bit executable, I received a
Class not registered error message.
Creating an accdb file
#
# Make sure the correct bitness of PowerShell is running!!!!
#
$adoxCat = new-object -comObject ADOX.catalog
$curDir = get-location
$accessFile ="$($curDir)\test-db.accdb"
#
# Remove Access file if it already exists:
#
remove-item $accessFile -errorAction ignore
#
# OLE DB Provider string
#
$provider =
# "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=$accessFile"
$catalog = $adoxCat.create($provider) # | out-null
$tab_one = $catalog.execute(@'
create table tab_one (
num integer primary key,
txt varchar(20) not null
)
'@)
$adoxCat.activeConnection.close()
Inserting some values
$curDir = get-location
$accessFile ="$($curDir)\test-db.accdb"
$provider =
# "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=$accessFile"
$adoConnection = new-object -comObject ADODB.connection
$adoConnection.connectionString = $provider
$adoConnection.open()
$insertStmt = new-object -comObject ADODB.command
$insertStmt.activeConnection = $adoConnection
$insertStmt.commandText ='insert into tab_one values (:num, :txt)'
$insertStmt.commandType = 1 # adCmdText
$paramNum = $insertStmt.createParameter('num', 3, 1, 4) # 3 = adInteger, 1 = adParamInput, 4 the size
$paramTxt = $insertStmt.createParameter('txt', 200, 1, 20) # 200 = adVarchar, 1 = adParamInput, 20 the size
$insertStmt.parameters.append($paramNum)
$insertStmt.parameters.append($paramTxt)
$paramNum.value = 1; $paramTxt.value ='one' ; $insertStmt.execute() | out-null
$paramNum.value = 2; $paramTxt.value ='two' ; $insertStmt.execute() | out-null
$paramNum.value = 3; $paramTxt.value ='three'; $insertStmt.execute() | out-null
$adoConnection.close()
Selecting data
$curDir = get-location
$accessFile ="$($curDir)\test-db.accdb"
$provider =
# "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=$accessFile"
$adoConnection = new-object -comObject ADODB.connection
$adoConnection.connectionString = $provider
$adoConnection.open()
$recordSet = $adoConnection.execute(@'
select
num,
txt
from
tab_one
'@)
while (! $recordSet.eof) {
write-output "$($recordSet.fields('num').value) | $($recordSet.fields('txt').value)"
$recordSet.moveNext()
}
$adoConnection.close()