Command Line Query Tool for Access DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Gregg said:
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?
Click to expand...


That's a rather strange question. First, an mdb file is a
Jet database (Jet is a database engine that is included in
the Access box).

The absolute best tool for interacting with a Jet (and many
other types of databases) is Access. There are other, less
powerful, tools for executing queries such as Excel, Word
and ???. However, the problem is not running a query, it is
displaying the query's dataset. An Access form is (or the
query's datasheet - yuck) is ideal for displaying the data
in a nicely formatted, controlled way.

I am certain that there are many ways to do what you ask,
but I strongly recommend that you use the batch script to
run Access and let Access run the query as a form's
RecordSource. Setting this up is relatively easy using a
command line argument to specify the form (or query - yuck).
 
Gregg said:
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
Click to expand...

Almost the same thing: in the MDB create a macro that executes a query
(or several queries, if you like), then from the command line, call
<your MDB> /x <your macro>.
 
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.
 
I need to query the file and have standard output saved.
Click to expand...

If you're querying an Access database and don't want to use Access to
do so, consider using MSQuery.

John W. Vinson[MVP]
 
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
 
Back
Top