Script to Read MDB wildcard issue

PowerShell Help
1

I have a working script for getting a single entity of data from my target mdb but I cannot seem to get the select statement to wildcard and get more than one at a time. All that needs to change is the Eng.SerialPortMgr.PrinterPort needs numbers 1 through 4 at the end, any thoughts?

$path = "C:\temp\Config.mdb"
$adOpenStatic = 3
$adLockOptimistic = 3
$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset

$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
$Port1 = $rs.open("Select * from Options where OptionName='Eng.SerialPortMgr.PrinterPort1'", $cn,$adOpenStatic,$adLockOptimistic)
$rs.MoveFirst()
do {
$rs.Fields.Item("OptionName").value + " = " + $rs.Fields.Item("OptionData").value 
$rs.MoveNext()} 
until($rs.EOF -eq $True)
$rs.Close()
$cn.Close()

$Port1
2

Troubleshooting SQL statements that you build like that is hard. I suggest putting the query into a variable, and then outputting that to the screen prior to executing it. It’ll be a lot easier to help you find the problem if I can see the final query statement.

3

Looking at actual SQL scripts I used before in working tools the following is what I came up with for a corrected script string. It runs without error but does not return anything when ran in the entire script.

$Script = “Select OptionName, OptionData from Options where left (OptionName, 29)=‘Eng.SerialPortMgr.PrinterPort’”

$path = "C:\temp\Config.mdb"
$adOpenStatic = 3
$adLockOptimistic = 3
$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset
$Script = "Select OptionName, OptionData from Options where left (OptionName, 29)='Eng.SerialPortMgr.PrinterPort'"

Write-Host $Script

$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
$Port1 = $rs.open($Script, $cn,$adOpenStatic,$adLockOptimistic)

$rs.Close()
$cn.Close()

$Port1
4

Well first, does the query work as espected when doing it in e.g. Management Studio?

Also in SQL the ‘=’ is explicit.
Meaning it is looking for Eng.SerialPortMgr.PrinterPort explicitly, not Eng.SerialPortMgr.PrinterPort1 or Eng.SerialPortMgr.PrinterPort2 and so on (as far as I remember at least).

You use LIKE if you want to use wildcards.

But as mentioned earler, try it in Management Studio, if the query fails there it won’t work in your script either.

5

My understanding of that statement is I’m saying the left 29 characters are explicitly Eng.SerialPortMgr.PrinterPort the number would be ignored. This is on a Access database can you use SQL Management Studio on that? Never tried I guess

6

Ahh, not sure if Access do it differently.

Haven’t really used Access but I’m sure that there are some tools for Access that let you try the query directly?

I doubt though that it will work that way, since what the statement would do is take the optionname and do a Left 29 on it.
Then explicitly test that against ‘Eng.SerialPortMgr.PrinterPort’.

What do you get if you do:

“Select OptionName, OptionData from Options where OptionName LIKE ‘Eng.SerialPortMgr.PrinterPort*’”

7

executes without error but returns no results again

8

So, a couple of things:

Access uses % as a wildcard, not *.

We’ve also kind of moved out of the realm of this being a “PowerShell” question :). You might find better assistance on a site like StackOverflow, where you’ve got developers working with the database technology every day. Just a suggestion - you’re more than welcome to continue here.

However, I can tell you that nearly any experienced developer is going to suggest taking your query into some kind of query tool, like SSMS would be for SQL Server. In the case of Access, that might mean Access itself. Troubleshooting “the query doesn’t return anything” is nearly impossible straight from PowerShell.

9

Well if it helps, I have the script sorted and for sure working if queried directly in Access which is the following…

SELECT Options.OptionName, Options.OptionData
FROM Options
WHERE OptionName LIKE ‘Eng.SerialPortMgr.PrinterPort*’

which returns the correct data in Access so I added it to my script variable but no return in PowerShell

$path = "C:\temp\Config.mdb"
$adOpenStatic = 3
$adLockOptimistic = 3
$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset
$Script = "SELECT Options.OptionName, Options.OptionData FROM Options WHERE OptionName LIKE 'Eng.SerialPortMgr.PrinterPort*'"

$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
$Port = $rs.open($Script,$cn,$adOpenStatic,$adLockOptimistic)

$rs.Close()
$cn.Close()

$Port
10

got it working

$path = "C:\temp\Config.mdb"
$adOpenStatic = 3
$adLockOptimistic = 3
$cn = new-object -comobject ADODB.Connection
$rs = new-object -comobject ADODB.Recordset

$cn.Open("Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $path")
$SawConfigPorts = $rs.open("SELECT Options.OptionName, Options.OptionData FROM Options WHERE OptionName LIKE 'Eng.SerialPortMgr.PrinterPort%'",$cn,$adOpenStatic,$adLockOptimistic)
$rs.MoveFirst()

do {
    $rs.Fields.Item("OptionName").value + " = " + $rs.Fields.Item("OptionData").value; 
        $rs.MoveNext() } 
        until($rs.EOF -eq $True)

$rs.Close()
$cn.Close()

$SawConfigPorts