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

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

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

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



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal

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

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)

"Doomster" <doomster@gmail.com> wrote in message
news:1127542675.527008.108640@z14g2000cwz.googlegr oups.com...[color=blue]
> 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
>[/color]

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

"Doomster" <doomster@gmail.com> wrote in
news:1127542675.527008.108640@z14g2000cwz.googlegr oups.com:
[color=blue]
> 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?[/color]

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.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

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

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

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

"Doomster" <doomster@gmail.com> wrote in
news:1127605713.820899.116210@z14g2000cwz.googlegr oups.com:
[color=blue]
> 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[/color]

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 W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

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

> Well, I guess it would be theoretically possible to use COM[color=blue]
> from Perl[/color]

$objConn = Win32::OLE->new('ADODB.Connection');
$objConn->open($strConn);
$objRS = $objConn->execute($strSQL);
(david)
"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns96DBAA1CC17A8dfentonbwaynetinvali@216.196. 97.142...[color=blue]
> "Doomster" <doomster@gmail.com> wrote in
> news:1127542675.527008.108640@z14g2000cwz.googlegr oups.com:
>[color=green]
>> 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?[/color]
>
> 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.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]