ant has asked for the wisdom of the Perl Monks concerning the following question:

Hi,
I am planning on connecting to a microsoft access database sitting on an
nt server (with activestate perl installed) accross a network from a unix box.
I have installed the Perl Modules and configured the ODBC settings as discussed
in 'Programming the Perl DBI', and I am confident that I will be able to connect to
the database.
My problem is actually querying the data on the the database. Do I issue the queries
in SQL as part of the database handle.
If there are already MIcrosoft Access queries on the database can these be fired from
the Perl script and the results returned?

Many thanks in advance to any answers or information returned.
Ant

Replies are listed 'Best First'.
RE: Accessing Microsoft Access Databases
by mdillon (Priest) on Nov 09, 2000 at 22:12 UTC
    when using ODBC to query data from an Access database, tables and "queries" (views in every other database system) are equivalent. you have to write queries in SQL either way.

    suppose you have a query called "TotalSales". to get the results through ODBC, you would use this SQL: 

    SELECT *
FROM TotalSales;

    [download]
    you would do the same with a table called "TotalSales".

    i'm not sure how the placeholder stuff in DBI interacts with ODBC, since the underlying datasource is potentially quite exotic (though not in the case of Jet/Access).

[reply]
[d/l]
Re: Accessing Microsoft Access Databases
by Fastolfe (Vicar) on Nov 09, 2000 at 21:00 UTC
    All the DBI module does (in this context) is send SQL queries to the server. If your SQL server is capable of activating stored procedures/queries via SQL, then yah, just send that SQL as part of your DBI request. I'm afraid I'm not very fluent with Access, but I'm sure your Access documentation will show you how to do this via SQL/ODBC. Perhaps another poster can elaborate further if they know, but otherwise, consult your Access docs. Hope this helps. 
    $sth = $dbh->prepare('SELECT * FROM STORED_PROCEDURE WHERE ID=?') or d
+ie "prepare: $!";
$sth->execute($id);
...

    [download]
[reply]
[d/l]
RE: Accessing Microsoft Access Databases
by ant (Scribe) on Nov 10, 2000 at 16:00 UTC
    Thanks for the replies and information shared.
    ant
[reply]
Re: Accessing Microsoft Access Databases
by ashok (Sexton) on Nov 10, 2000 at 00:39 UTC
    Hi , It looks you are familiar with accessing databases using Perl. I want to install Perl on my Win98 computer. I do not have any C compiler on my computer. Also I want to use Access/Oracle database using Perl using ADO. Can you pl. guide me from where/how/what to download? Thanks Ashok
[reply]
      You might check here, where your question has already been answered...
[reply]

Back to Seekers of Perl Wisdom