locked
Writing a table to a MS Access database from Powershell RRS feed

  • Question

  • Hey guys, me again...I'm trying to wrap up a project here and i have a powershell app writing to a SQL database just fine. The last piece is writing to a MS Access database table. For the life of me i cannot find anything out there that really points me in the right direction.

    I know my database name/path and i know the table/headers i need to write my data too.

    Anyone have a link to a quick dirty tutorial or can someone point me in the right direction ?

    Thanks as always

    Rich


    Rich Thompson

    Monday, June 16, 2014 4:54 PM

Answers

All replies

  • Use ADO to write to Jet tables.

    It works exactly like SQLServer but we use the Jet provider and the OleDB client.

    Like this:
    $conn=New-Object System.Data.OleDb.OleDbConnection('Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Script\mydb.mdb')
    $conn.Open()

    After that it is just SQL.


    ¯\_(ツ)_/¯


    • Edited by jrv Monday, June 16, 2014 5:11 PM
    Monday, June 16, 2014 5:05 PM
  • Here is an example using the ACE drivers:

    $mdbFile='c:\scripts\test.mdb'
    $connString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$mdbFile"
    $conn=New-Object System.Data.OleDb.OleDbConnection($connString)
    $conn.Open()


    ¯\_(ツ)_/¯

    • Marked as answer by Oliver Lipkau Tuesday, June 17, 2014 1:00 PM
    Monday, June 16, 2014 5:11 PM
  • am i doing something wrong here ?

    $conn=New-Object System.Data.OleDb.OleDbConnection('Provider=Microsoft.Jet.OLEDB.12.0; Data Source=\\CPSFS001\TestCofA\test.accdb')
                $conn.Open()
                $insert_stmt = "INSERT INTO dbo_so_shprimg_pvs (so_hdr_key, so_ship_key, imgfileName, so_shprimg_crtdt) VALUES ('$order','$shipid','$name','$timestamp')"
                $cmd = $conn.CreateCommand()
                $cmd.CommandText = $insert_stmt
                $cmd.ExecuteNonQuery()
                $conn.Close()

    Works on my SQL side... hmmm


    Rich Thompson

    Monday, June 16, 2014 5:35 PM
  • What error messages are you getting?

    ¯\_(ツ)_/¯

    Monday, June 16, 2014 5:45 PM
  • Note that you cannot use ADO to insert into a linked table.

    ¯\_(ツ)_/¯

    Monday, June 16, 2014 5:45 PM
  • I get no error message. I created a blank database with a table called dbo_so_shprimg_pvs and headers called so_hdr_key, so_ship_key, imgfileName, so_shprimg_crtdt. I'm doing a test write-host prior so i know my variables are good. 

    Rich


    Rich Thompson

    Monday, June 16, 2014 6:00 PM
  • It works fine for me.  You have to be getting some kind of an error or something else you are doing is not being noted.


    ¯\_(ツ)_/¯

    Monday, June 16, 2014 6:09 PM
  • The Microsoft.Jet.OLEDB.12.0 provider is not registered on the local machine. I also tried Ace and the same thing ? 

    Rich Thompson

    Monday, June 16, 2014 6:45 PM
  • I am running Windows 7 Pro 64 bit with 32 bit MS Access. Could this be the issue ?

    Rich Thompson

    Monday, June 16, 2014 6:47 PM
  • Access 2007 and later do not come with drivers.  You need to install them..

    http://www.microsoft.com/en-us/download/details.aspx?id=13255


    ¯\_(ツ)_/¯

    • Marked as answer by Oliver Lipkau Tuesday, June 17, 2014 12:59 PM
    Monday, June 16, 2014 7:01 PM
  • I installed 64 bit Access and all is good. Thank you so much for your help I learned alot in the last day from you.

    much appreciated.

    Rich


    Rich Thompson

    Tuesday, June 17, 2014 12:09 PM
  • Very good.  Glad it worked and that you are set.


    ¯\_(ツ)_/¯

    Tuesday, June 17, 2014 3:22 PM