Use powershell to create queries in MS-Access

Question

I need to automate some data extraction from a Microsoft Access DB. The DB is provided to me by a third party, and so I do not have control over what is in the DB when it is received. I need to use Powershell to automate extraction of data from the DB.

Is there any way to use powershell to create a query in the accessDB? This is, essentially, the essence of the code I am looking for:

$l_dbPath = "C:\Path\To\dataBase.accdb"
$l_accessApp = new-object -com access.application
$l_accessApp.OpenCurrentDatabase($l_dbPath)

$l_query = "SELECT SomeTable.SomeField From SomeTable;"
$l_queryName = "Export_Query"
$l_accessApp.CurrentDB().CreateQueryDef($l_queryName, $l_query)

$l_outputFile = "C:\temp\output.csv"
$e_acExportDelim = 2 #$l_accessApp.Enumerations.AcTextTransferType.acExportDelim #arg.  this does not seem to exist...
$e_HasFieldNamesYes=-1
$l_exportSpec = ""
$l_accessApp.DoCmd.TransferText($e_acExportDelim,$l_exportSpec,$l_queryName,$l_outputFile,$e_HasFieldNamesYes)
$l_accessApp.CloseCurrentDatabase()

However, the line $l_accessApp.CurrentDB.CreateQueryDef($l_queryName, $l_query) fails, because $l_accessApp.CurrentDB() returns null, not the current DB. I can find no way to access CreateQueryDef from Powershell.

I have looked at Programmatically Build Access Query, Create a query dynamically, Use Powershell to create access 2007 Queries?, and other posts, but found nothing that works.

Thanks for any help.

EDIT: It turns out that the code as I had it above does, in fact, work! The issue all along was simply that I did not have a machine on which I had both DAO and Access installed. After testing the separate components (using DAO, and using Access) on different machines, and getting IT support to give me a machine with DAO and Access both actually installed, the above code does in fact work. (With MS Access installed and loaded, but DAO not installed, $l_accessApp.CurrentDB() returns $null. With DAO installed, it returns a DBEngine object, as expected.)

why do you want to create a query inside the database? just connect to it and do your own extraction.. ie: loop through the rows/columns and create the text file.. — O. Gungor
– O. Gungor, Commented Jun 21, 2017 at 18:32
There are other reasons I cannot do as you suggest. Suffice it to say that what I really need, what I am looking for, is how to access CreateQueryDef from powershell through $l_accessApp.OpenCurrentDatabase($l_dbPath) — David I. McIntosh
– David I. McIntosh, Commented Jun 22, 2017 at 14:59
RyanL has provided below the exact answer I was searching for, as well as a few more that may be useful to others with similar issues. Thanks. — David I. McIntosh
– David I. McIntosh, Commented Jun 26, 2017 at 12:39

RyanL · Accepted Answer · 2017-06-23 13:16:50Z

1

Creating a querydef:

$dbe =new-Object -comobject DAO.DBEngine.120
$path="c:\path\to\db.mdb"
$db = $dbe.opendatabase($path)

$l_query = "select table1.* from table1"
$l_queryName="testquery"
$l_outputFile="z:\test.csv"

$db.CreateQueryDef($l_queryName, $l_query)
$db.closedatabase


$l_outputFile = "z:\test.csv"
$e_acExportDelim = 2 
$e_HasFieldNamesYes=-1
$l_exportSpec = ""

$l_accessApp = new-object -com access.application
$l_accessApp.OpenCurrentDatabase($path)
$l_accessApp.DoCmd.TransferText($e_acExportDelim,$l_exportSpec,$l_queryName,$l_outputFile,$e_HasFieldNamesYes)
            $l_accessApp.CloseCurrentDatabase()

$l_accessApp.CloseCurrentDatabase()

answered Jun 23, 2017 at 13:16

RyanL

1,2762 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David I. McIntosh Over a year ago

My current code now, almost exactly. Thanks. If only I could figure out why neither $l_accessApp.CurrentDB nor $l_accessApp.DBEngine(0) work.

David I. McIntosh Over a year ago

See EDIT to question above: $l_accessApp.CurrentDB() does in fact work, when you get DAO and MS Access both properly installed.

RyanL · Accepted Answer · 2017-06-21 21:55:42Z

1

You may want to give something like this a try. You're extracting information into a CSV without creating a query definition in MS Access.

$OpenStatic = 3
$LockOptimistic = 3

$path = "C:\Path\To\dataBase.accdb"

$connection = New-Object -ComObject ADODB.Connection
$RecordSet = New-Object -ComObject ADODB.Recordset

$connection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$Path" )

$Query = "SELECT SomeTable.SomeField From SomeTable;"

$RecordSet.Open($Query, $Connection, $OpenStatic, $LockOptimistic)

$RecordSet | Out-File  z:\output.csv

answered Jun 21, 2017 at 21:55

RyanL

1,2762 gold badges10 silver badges16 bronze badges

Comments

RyanL · Accepted Answer · 2018-09-21 12:32:50Z

0

A different approach. Are you married to the idea of actually creating a query def? My understanding is you're after the results...is this not the case?

#db path
$DBPath = "c:\path\to\db.mdb"

#SQL statement to run
$SQL = "select table1.* from table"

#Output file location
$Output = "z:\test.csv"

#Connection
$Con= New-Object -TypeName System.Data.OleDb.OleDbConnection

#Connection string
$con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= $path"

#create command and fill dataset
$ConCommand= $Con.CreateCommand()
$ConCommand.CommandText = $SQL
$OAdapt = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $ConCommand
$DS = New-Object -TypeName System.Data.DataSet
$OAdapt.Fill($DS)

#Export
$DS.Tables[0] | export-csv $Output -NoTypeInformation
$Con.Close()

edited Sep 21, 2018 at 12:32

answered Jun 23, 2017 at 13:03

RyanL

1,2762 gold badges10 silver badges16 bronze badges

1 Comment

Owain Esau Over a year ago

This approach worked for me, just need to change $connection to $con

khalidmehmoodawan · Accepted Answer · 2020-07-17 07:47:33Z

0

ExportSpecification is what that needs to be played here in order to get some desired output. This specification has to be created from within Microsoft Access using a wizard.

Using exact paths and specification names, later you have to run above script to get the desired output.

answered Jul 17, 2020 at 7:47

khalidmehmoodawan

6791 gold badge8 silver badges22 bronze badges

Comments

RyanL · Accepted Answer · 2017-06-22 17:05:07Z

-1

Can you use DAO?

$OpenStatic = 3
$LockOptimistic = 3


$dbe =new-Object -comobject DAO.DBEngine.120
$db = $dbe.opendatabase("C:\Path\To\dataBase.accdb")

$Query = "SELECT SomeTable.SomeField From SomeTable;"

$rs=$db.openrecordset($query)

$rs| Out-File  z:\output.csv

edited Jun 22, 2017 at 17:05

answered Jun 22, 2017 at 16:59

RyanL

1,2762 gold badges10 silver badges16 bronze badges

2 Comments

David I. McIntosh Over a year ago

$rs| Out-File z:\output.csv does not output the records.

David I. McIntosh Over a year ago

RyanL: if you fix the final line of this answer, it might be more useful to others. Thanks.

Collectives™ on Stack Overflow

Use powershell to create queries in MS-Access

5 Answers 5

2 Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related