How can I interact with MS Access using a programming language like Perl?

[Doomster]

In a previous job, we had Perl scripts which could interact with a SQL
DB using Perl's DBI module We were able to programmatically add,
delete and query tables using this module.

Is it possible to do something similiar with Perl? (and not VBA)???

The reason I ask is because I want to use Access instead of MySQL
because
with Access, I can design Forms and Queries to display data relatively
quickly. With MySQL, I'd have to use third party or open source SW.

I am planning on grabbing baseball statistics from websites using Perl
(the Perl LWP module). With the raw data, I will process it and then I
want to use Perl to add the data to the MS Access DB. This might mean
adding records to a table to modifying existing records in a table. Is
there a way to interfact with Access DB using Perl?

Doomster

 

[Albert D. Kallal]

You can use the ADO library with Peral, and thus you can use a mdb.

You will have to have JET installed on the pc, and having done so, then you
can use the DBI module...

"JET" used to be a separate download in the download section. I don't know
where it went to, but if you got ms-access installed, then you got JET
installed, and should thus be able to use a mdb file with perl.

Just do a web search for

perl ado jet

 

[Guest]

Jet is installed and updated with Windows.

You can connect to Jet using an ODBC or OLEDB
connector (No native JDBC available), or using the
ADO or DAO COM objects.

So you can connect using Perl's DBI - or you have
a choice of other methods.

(david)

 

[David W. Fenton]

In a previous job, we had Perl scripts which could interact with a
SQL DB using Perl's DBI module We were able to programmatically
add, delete and query tables using this module.

Is it possible to do something similiar with Perl? (and not
VBA)???

The reason I ask is because I want to use Access instead of MySQL
because
with Access, I can design Forms and Queries to display data
relatively quickly. With MySQL, I'd have to use third party or
open source SW.

I am planning on grabbing baseball statistics from websites using
Perl (the Perl LWP module). With the raw data, I will process it
and then I want to use Perl to add the data to the MS Access DB.
This might mean adding records to a table to modifying existing
records in a table. Is there a way to interfact with Access DB
using Perl?

Albert has provided you with a lead on this, so I won't address it.

I just want to be sure that you understand (you seem to) that you
will only be able to interact with the tables and queries, and not
with the forms and reports, through Perl.

Well, I guess it would be theoretically possible to use COM from
Perl, but it would be every messy, and very unlikely to be allowed
by an ISP.

 

[Doomster]

David,

I just want to use Perl to get data from websites (baseballs stats),
then input them into the DB. This I want to automate because inputting
data into the MS Access DB is labor intensive.

The queries and using forms will be done manually.

This works fine. I just got to get it to work with JET.

Trust me, there will be further questions from me on this board



Doomster

 

[David W. Fenton]

I just want to use Perl to get data from websites (baseballs
stats), then input them into the DB. This I want to automate
because inputting data into the MS Access DB is labor intensive.

The queries and using forms will be done manually.

This works fine. I just got to get it to work with JET.

Trust me, there will be further questions from me on this board

Well, if they are about desiging your Access application, this is
the right place to ask.

But if it's about how to use Perle to scrape websites, you're not
going to find much help here, as most of the issues you'll encounter
are likely to be peculiar to Perle's way of working with whatever
data interface to your Jet data you use (ODBC or ADO). The Jet end
of that is pretty much completely trivial, with hardly any problems
specific to Jet that you'd likely find help with in this newsgroup.

I would say you'd be better off asking those questions in a Perle
forum, where it's quite likely that people have worked with data
stored in Jet MDBs.

 

[david epsom dot com dot au]

Well, I guess it would be theoretically possible to use COM

from Perl

$objConn = Win32::OLE->new('ADODB.Connection');
$objConn->open($strConn);
$objRS = $objConn->execute($strSQL);
(david)