powershell/microsoft access/db2 RRS feed

  • Question

  • Good evening!

    This site has been a great help to me as I expand my skills. Thank you!

    I have a problem that combines a number of things that have been discussed here.

    I use Powershell for automation of different Access functions for my day job. We use db2. I can use powershell to fully automate queries that pull data from db2. Separately, I can use powershell to manipulate data in local access databases without accessing db2.

    However, I can't pull data from db2 that I need to complete access queries that also use local access DBs. Does that make sense? Logically it seems there would be a way to run queries in a local Microsoft access db that also require data from db2.

    This is a sample of my script:

    $connectstring = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=mydatabase.accdb;DSN=xxxxxxxxxx;Uid=xxxxxxxxxxx;Pwd=xxxxxxxxxxxx"
    $sql = "my query that pulls data from a microsoft access 2010 db and db2 tables behind a dsn;"
    $conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
    $cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
    $da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
    $dt = New-Object system.Data.datatable
    $da.SelectCommand = $cmd;
    $RowsReturned = $da.Fill($dt);
    $wshell = New-Object -ComObject Wscript.Shell
    $wshell.Popup("xxxxxxx DB2 returned $RowsReturned rows") 
    #Write-Host $RowsReturned;

    Can anyone offer some advice to access (no pun intended) DB2 *while* running a Microsoft Access query in a locally stored db?

    Thank you!

    Thursday, July 20, 2017 12:13 AM


  • To create queries that access both Access tables and DB2 tables you must attach the DB2 tables as linked tables in the Access DB file.  Once  you have linked the tables you can use PowerShell to execute a multi-table query. (join, lookup, etc)  The query will see all tbled nd li nked tbles as belonging to Access.

    For assistance with setting up 'linked" tables in Access post in the MSAccess developers forum.


    Thursday, July 20, 2017 12:36 AM

All replies