Getting Data at MSAccess using Powershell

Nher morales
Nher morales used Ask the Experts™
on
How to get data from MSACCESS table using powershell?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Managing Consultant
Commented:
You use ODBC.

Here are some functions I wrote for a project that should get you started. It has the added capability of also working with MS-SQL and MySQL (assuming you've loaded the proper parts of ODBC).  You may need to update the driver names for your computer (especially matching 32-bit vs. 64-bit drivers to match with Access).

#
# dbODBC.ps1
#


Set-Variable dbAccess -force -option ReadOnly -value 1
Set-Variable dbMSSQL  -force -option ReadOnly -value 2
Set-Variable dbMySQL  -force -option ReadOnly -value 3

#
# Microsoft Office Access Example
#
Set-Variable dbChoice -force -option ReadOnly -value $dbAccess
Set-Variable dbName   -force -option ReadOnly -value PowerMLM.mdb
Set-Variable dbSource -force -option ReadOnly -value Not-Applicable
Set-Variable dbUser   -force -option ReadOnly -value Not-Applicable
Set-Variable dbPass   -force -option ReadOnly -value Not-Applicable

#
# Microsoft SQL Server Example
#
#Set-Variable dbChoice -force -option ReadOnly -value $dbMSSQL
#Set-Variable dbName   -force -option ReadOnly -value PowerMLM
#Set-Variable dbSource -force -option ReadOnly -value .\MSSMLBIZ
#Set-Variable dbUser   -force -option ReadOnly -value Not-Applicable
#Set-Variable dbPass   -force -option ReadOnly -value Not-Applicable

#
# MySQL Example
#
#Set-Variable dbChoice -force -option ReadOnly -value $dbMySQL
#Set-Variable dbName   -force -option ReadOnly -value sampledb
#Set-Variable dbSource -force -option ReadOnly -value localhost
#Set-Variable dbUser   -force -option ReadOnly -value root
#Set-Variable dbPass   -force -option ReadOnly -value password

# for Access, dbName is assumed to be located in $pwd
# for MySQL, dbSource should be the IP address or FQDN of the computer running MySQL server
# for MSSQL, dbSource should be the name plus the instance of MS-SQL (e.g., SERVER\Instance2)
#            if not present or "Not-Applicable", then "(local)\Default" is presumed
# for MSSQL, if dbUser is something other than "Not-Applicable" then Windows Auth is used
#

$global:MLMconnection = $null
$global:MLMcommand    = $null
$global:MLMresult     = $null

function dbOpen
{
	$global:MLMconnection = New-Object System.Data.Odbc.OdbcConnection

	$global:MLMcommand = New-Object System.Data.Odbc.OdbcCommand
	$global:MLMcommand.Connection = $global:MLMconnection

	switch ($dbChoice)
	{
		$dbAccess
		{
			$cStr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=$pwd/$dbName;"
		}
		$dbMSSQL
		{
			$cStr = "Driver={SQL Server};Server=$dbSource;Database=$dbName;"
			if ($dbUser -eq "Not-Applicable")
			{
				$cStr += "Integrated Security=SSPI;"
			}
			else
			{
				$cStr += "UID=$dbUser;PWD=$dbPass;"
			}
		}
		$dbMySQL
		{
			$cStr = "Driver={MySQL ODBC 3.51 Driver};SERVER=$dbSource;DATABASE=$dbName;"
			if ($dbUser -ne "Not-Applicable")
			{
				$cStr += "UID=$dbUser;PWD=$dbPass;"
			}
		}
		Default
		{
			throw ("Unknown value of dbChoice $dbChoice can't open db $dbName")
		}
	}

	log "dbOpen:" ("ConnectionString='" + $cStr + "'")

	$global:MLMconnection.ConnectionString = $cStr
	$global:MLMconnection.Open()
	if ($global:MLMconnection.State -ne [System.Data.ConnectionState]::Open)
	{
		throw ("Cannot open $dbName")
	}
}

function dbPrepareResult
{
	if ($global:MLMresult)
	{
		if ($global:MLMresult.Gettype().Name -ne "Int32")
		{
			$global:MLMresult.Close()
		}
		$global:MLMresult = $null
	}
}

function dbClose
{
	dbPrepareResult
	if ($global:MLMcommand)
	{
		$global:MLMcommand.Cancel()
		$global:MLMcommand = $null
	}

	if ($global:MLMconnection)
	{
		if ($global:MLMconnection.State -ne [System.Data.ConnectionState]::Closed)
		{
			$global:MLMconnection.Close()
		}
		$global:MLMconnection = $null
	}
}

function dbExecute([string]$command)
{
	dbPrepareResult
	if (!$global:MLMcommand)
	{
		dbOpen
	}

	log "dbExecute:" ("command='" + $command + "'")

	$global:MLMcommand.CommandType = [System.Data.CommandType]::Text
	$global:MLMcommand.CommandText = $command

	$global:MLMresult = $global:MLMcommand.ExecuteReader()

	if ($MLMresult)
	{
		log "dbExecute: " ("result type=" + $MLMresult.gettype())
	}
	else
	{
		log "dbExecute: " "null result"
	}

	return $global:MLMresult
}

function dbExecuteNonQuery([string]$command)
{
	dbPrepareResult
	if (!$global:MLMcommand)
	{
		dbOpen
	}

	log "dbExecuteNonQuery:" ("command='" + $command + "'")

	$global:MLMcommand.CommandType = [System.Data.CommandType]::Text
	$global:MLMcommand.CommandText = $command

	$global:MLMresult = $global:MLMcommand.ExecuteNonQuery()

	if ($MLMresult)
	{
		log "dbExecuteNonQuery: " ("result type=" + $MLMresult.gettype())
	}
	else
	{
		log "dbExecuteNonQuery: " "null result"
	}

	return $global:MLMresult
}

#
# dbQuote
#
# This routine ensures that a table or column name is properly
# quoted for the database platform being utilized. Unlike the
# column values, $word may not be NULL.
#
function dbQuote( [string] $word )
{
	if( !$word )
	{
		throw ( "dbQuote: table/column name may not be null." )
	}
	# square brackets work for MS-SQL and MS-Access
	$start = '['
	$end   = ']'

	if( $dbChoice -eq $dbMySQL )
	{
		$start = $end = '`'
	}

	$result = $start + $word + $end

	log "dbQuote:" ( "result='" + $result + "'" )

	return $result
}

#
# dbDate
#
# This routine ensures that a date variable is formatted properly
# for the database platform being utilized, and if the variable is
# null, that a NULL is returned.
#
function dbDate($d)
{
	if ($d)
	{
		$date = $d -as [DateTime]
		switch ($dbChoice)
		{
			$dbAccess
			{
				$result = "#" + $date.ToString() + "#"
			}
			$dbMSSQL
			{
				$result = "'" + $date.ToString() + "'"
			}
			$dbMySQL
			{
				## MySQL demands a certain date-format
				$result = "'" + $date.ToString("yyyy-MM-dd HH:mm:ss") + "'"
			}
			Default
			{
				throw ("Bad dbChoice value $dbChoice in dbDate")
			}
		}
	}
	else
	{
		$result = "NULL"
	}

	log "dbDate:" ("dbChoice=" + $dbChoice + ", result='" + $result + "'")

	return $result
}

#
# dbText
# 
# This routine ensures that a text/string/varchar variable is properly quoted
# or a NULL is returned for an empty string.
#
# Note that some database platforms do not support a zero length string.
#
function dbText($s)
{
	if ($s)
	{
		$str = $s -as [string]
		if ($str.Length -gt 0)
		{
			$result = "'" + $str.Replace("'", "''") + "'"
		}
		else
		{
			$result = "NULL"
		}
	}
	else
	{
		$result = "NULL"
	}

	log "dbText:" ("result=" + $result)

	return $result
}

#
# dbInt
#
# This routine ensures that a integer/number variable is properly returned. If
# the value is zero or NULL then a zero is returned.
# 
# Note that some database engines to not support a NULLable integer.
#
function dbInt($i)
{
	if ($i)
	{
		$ii = $i -as [int]
		$result = $ii.ToString()
	}
	else
	{
		$result = "0"
	}

	log "dbInt:" ("result=" + $result)

	return $result
}

#
# dbBool
#
# This routine ensures that a boolean/yes-no variable is properly returned. If
# the value is $false or NULL then False is returned - otherwise True.
#
function dbBool($b)
{
	if ($b)
	{
		$bb = $b -as [bool]
		if ($bb)
		{
			$result = "True"
		}
		else
		{
			$result = "False"
		}
	}
	else
	{
		$result = "False"
	}

	if ($dbChoice -eq $dbMSSQL)
	{
		if ($result -eq "True")
		{
			$result = "1"
		}
		else
		{
			$result = "0"
		}
	}

	log "dbBool:" ("result=" + $result)

	return $result
}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial