I needed to track down all the access DB’s and wasn’t sure how to do it.

One of our developers came up with a script which I modified a bit.

Original credit goes to Ravi Konaparthi for the script.

This script will look for Access Services DB’s in a SharePoint 2010 Farm.
(This version only looks at a given Web application, but you can easily modify it to search everything – have a look at the comments)

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null) 
{
	Write-Host "Loading SharePoint Powershell Snapin"
    Add-PSSnapin "Microsoft.SharePoint.Powershell"
}

#Foreach loop to loop through all webs, and compare the web template and write findings to .csv file.

function Get-AccessDB()
{
    log "In Get-AccessDB"
    $url = getURL
    #NOTE - if you want to look at ALL Sites and not just a specific web application, just remove -webapplication $url from the line below.
    $sites=Get-SPSite -webapplication $url  -limit all
    foreach($Site in $sites)
	{
		log "Inspecting Site $($site.url)"
		foreach($web in $site.AllWebs)
		{
			log "inspecting web $($web.url)"
			if ($web.WebTemplate -like "*ACCSRV*")
			{
				log "Found Access DB in $($web.url)" "Green"
				$hash = @{"[URL]"=$web.Url;}
				New-Object PSObject -Property $hash | Sort-Object
			}
		}
	$site.Dispose()
	}
}

function log($txt, $color)
{
  if ($color -eq $null) { $color = "White" }
  write-host $txt -foregroundcolor $color
}

function getURL()
{
    switch(hostname)
	{
		"SPDEVPC" {$machineURL = "http://yourDevURL.yoursite.com"}
		"SPStagePC" {$machineURL = "https://yourStgURL.yoursite.com"}
		"SPProdSrv" {$machineURL = "https://yourProdURL.yoursite.com"}
		default {write-host "This should be run from either SPDEVPC, SPSTAGEPC or SPPRodSRV - Press any key to exit this script"; read-host; exit}
	}
	return $machineURL
}

Get-AccessDB | Export-csv D:\accessdb.csv