Getting Data at MSAccess using Powershell

How to get data from MSACCESS table using powershell?
Nher moralesPowerShell BeginnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael B. SmithExchange & Active Directory ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.