Re: Command Line Query Tool for Access DB
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Sun, 31 Dec 2006 10:37:04 +0000
Hi Gregg,
If you want to use a stored Access query that requires a parameter (e.g.
PARAMETERS [MinimumHeight] Single;
SELECT * FROM MyTable
WHERE Height >= [MinimumHeight]
) AFAIK you can't do it without using Access. But there's no problem if
your script can concatenate the parameter(s) into a SQL SELECT
statement. You can use OLE and DAO or ADODB as in my previous example,
but use the SQL to open a recordset which you iterate through and print
to STDOUT. Alternatively, and perhaps better, use ODBC. I haven't tried
that in VBScript, but here's an example in Perl:
#start of script
use strict;
use Win32::ODBC; #not sure how well this handles Unicode...
die "ODBC_Select.pl: extracts data from an MDB database to STDOUT.
Syntax:
perl ODBC_Select.pl mdbfile SQL [[SQL]...]
mdbfile: filespec of mdb database file
SQL: a SELECT statement
" unless $ARGV[1];
my $mdb = shift @ARGV; #database file to work on
my $Conn = new Win32::ODBC(
"Driver={Microsoft Access Driver (*.mdb)};Dbq=$mdb;");
die "Couldn't establish connection to $mdb: " unless defined $Conn;
foreach my $SQL (@ARGV) {
if ($Conn->Sql($SQL)) {
warn "SQL '$SQL' failed.\n";
warn "Error: " . $Conn->Error() . "\n";
next
} else {
my $firstrow = 1;
while($Conn->FetchRow()) {
my %Data = $Conn->DataHash();
my $line = "";
if ($firstrow) { #print field names
$line .= "$_\t" foreach sort keys %Data;
chop $line;
print STDOUT "$line\n";
$line = '';
$firstrow = 0;
}
$line .= "$Data{$_}\t" foreach sort keys %Data;
chop $line;
print STDOUT "$line\n";
}
}
};
$Conn->Close;
#end of script
On Sat, 30 Dec 2006 15:25:00 -0800, Gregg Dotoli
<GreggDotoli@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I need to query the file and have standard output saved. Launching Access is
overkill and inefficient. I want to run the query with a parameter. I don't
understand how a question like mine is strange. You sound like one of those
windows people who get lost at the DOS prompt.
"Gregg Dotoli" wrote:
Does anyone know if MS Access comes with a tool for batch file access to an
MDB file? I have a simple 5 field MDB and want to run queries at the shell
level.
If not, what good tools are available?
Thanks,
Gregg Dotoli
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- Prev by Date: Re: Help -- Is my there any way to not count duplicate values here
- Previous by thread: Re: Command Line Query Tool for Access DB
- Next by thread: Creating a simple query with many secondary tables
- Index(es):
Relevant Pages
|
