none
Accessing MS Access database from PowerShell RRS feed

  • Question

  • I'm working on my first real PowerShell script. I'm converting an old VBScript to PowerShell. The script uses an Access database to track computer information. In looking for information on how to connect to the database, I'm coming across a number of methods for accomplishing this: ADO, OLEDB, ODBC. Which is the best for use with PowerShell 5 on Windows 10? Can I access it via SQL commands even though I'm not using SQL server? So many of the threads I find are nearly ten years old, and I don't want to use an out-of-date method.

    Thank you.

    Tuesday, October 18, 2016 3:48 PM

Answers

  • To use OleDB you must install the optional ACE drivers. ACE is the easiest way to use SQL with Access.

    With direct automation (COM) you will have to create a query object and execute it.

    With ACE drivers:

    function Test-DBAccess{
    Param(
         $fileName = 'C:\scripts\test.accdb',
    )
    
        $conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$filename;Persist Security Info=False")
        $cmd=$conn.CreateCommand()
        $cmd.CommandText="Select * from table1"
        $conn.open()
        $rdr = $cmd.ExecuteReader()
        $dt = New-Object System.Data.Datatable
        $dt.Load($rdr)
        $dt
    }


    \_(ツ)_/

    Tuesday, October 18, 2016 4:32 PM

All replies

  • Hi Ken,

    you can totally do this with various techniques. It's not my key expertise (as in: Did it twice, happy I won't have to again), so I can't tell you about the best solution, however OleDB may work, hijacking Access using ComObjects will also work. Either way, you can absolutely use SQL statements on Access using PowerShell.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, October 18, 2016 3:50 PM
  • To use OleDB you must install the optional ACE drivers. ACE is the easiest way to use SQL with Access.

    With direct automation (COM) you will have to create a query object and execute it.

    With ACE drivers:

    function Test-DBAccess{
    Param(
         $fileName = 'C:\scripts\test.accdb',
    )
    
        $conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$filename;Persist Security Info=False")
        $cmd=$conn.CreateCommand()
        $cmd.CommandText="Select * from table1"
        $conn.open()
        $rdr = $cmd.ExecuteReader()
        $dt = New-Object System.Data.Datatable
        $dt.Load($rdr)
        $dt
    }


    \_(ツ)_/

    Tuesday, October 18, 2016 4:32 PM
  • That works great.  Thank you.

    Please allow me to take this a step further.  Let's say the Select statement is looking for a particular record.  What is the best method of detecting that the record wasn't found?  I could test the value of a property of $DT, but wouldn't catching an exception be better?  I haven't been able to determine the appropriate error class to put on the Catch for it to work.

    	$conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$filename;Persist Security Info=False")
    	$cmd=$conn.CreateCommand()
    	$cmd.CommandText="Select * From Computers Where (((computers.[computer name]) = "Server1"));"
    	$conn.open()
    	$rdr = $cmd.ExecuteReader()
    	$dt = New-Object System.Data.Datatable
    
    	Try {
    
    		$dt.Load($rdr)
    		$dt
    
    	} Catch [System.Data.OleDb.OleDbException] {
    
    		Write-Host "Record not found"
    
    	}

    Nothing I've tried makes the Catch work for a record not found.

    I will be sure to mark you as the answer for the original question.  Thank you.


    Thursday, October 20, 2016 4:26 PM
  • $dt will be empty or:

    if($dt.Load($rdr){$dt }else{Write-Host 'no records found'}


    \_(ツ)_/

    Thursday, October 20, 2016 4:52 PM
  • Thank you.
    Thursday, October 20, 2016 5:06 PM