s there a module to acess Micorsoft Access datafiles?

S

Stan Brown

I find myself with the eed to obtain some data stored in Microsoft Access
format files from a perl script ruinning on a FreebSD box.

What's the best way to do this?

The files are actually on a Windows machien, but I can mount the partiton
using Samba.
 
B

Brian Helterline

Stan Brown said:
I find myself with the eed to obtain some data stored in Microsoft Access
format files from a perl script ruinning on a FreebSD box.

What's the best way to do this?

The files are actually on a Windows machien, but I can mount the partiton
using Samba.

Try the DBD:ODBC Module
 
W

William Alexander Segraves

William Alexander Segraves said:
Win32::ODBC

Retracted. I see FreeBSD (which I originally missed), not Win32, as
platform.

Bill Segraves
 
W

William Alexander Segraves

Stan Brown said:
In <[email protected]> "William Alexander Segraves"

Stan, please note I retracted this statement, on seeing what appeared to be
your objective.
Correct me if I'm wrong, but wouldn't that have to run on the Windows machine?

Yes. In addition, you'd have to set up a DSN on the Win32 machine to make it
possible for Win32::ODBC to access the files. See
http://www.roth.net/odbc/odbcfaq.htm for, well, FAQ stuff, on Win32.
Perhaps I did not mention, the idea is to not have to do any more to the
Windows box than mount it's share to get access to the files?

I understand. I wouldn't be at all surprised if you could do it that way. I
just haven't done it myself.
Or am I on the wrong track here? I certainly will be the first to admit
that I know nothing about the Windows side of things!

You may take a look at _Programming the Perl DBI_, Descartes, Alligator and
Bunce, Tim, "ODBC from Perl", pp. 167-172 for how to move a Perl script that
uses Win32::ODBC from Win32 to another platform.

If you want to try setting up a Win32 box to provide access to MS Access
files, you might try downloading and installing IndigoPerl, free from
www.indigostar.com, on the Win32 box. Note that you don't need to have MS
Access installed on the Win32 box to be able to access an MS Access file,
although it would certainly be helpful, especially if you need to generate
MS Access files for testing.

A couple of interesting projects I've encountered that may be helpful to
you:

1. #GUFE: Generic but Usable Front End
#by Aaron Weiss ([email protected]) for the Web Developers' Virtual Library
# http://www.wdvl.com

2. # ODBC query - requires PERL 5.003_7 or higher and Win32::ODBC
# Author Alan Johnston - modified by WAS 11May2002
# Downloaded from http://www.roth.net/perl/scripts/scripts.asp?ODBCQry.pl

I'd certainly *like* to be in your situation, as my preference, at times,
would be to know as little as possible about Windows.

Cheers.

Bill Segraves
 
S

Stan Brown

In said:
Stan, please note I retracted this statement, on seeing what appeared to be
your objective.


Yep, but I only saw the first post before sending the question.

Yes. In addition, you'd have to set up a DSN on the Win32 machine to make it
possible for Win32::ODBC to access the files. See
http://www.roth.net/odbc/odbcfaq.htm for, well, FAQ stuff, on Win32.
I understand. I wouldn't be at all surprised if you could do it that way. I
just haven't done it myself.
You may take a look at _Programming the Perl DBI_, Descartes, Alligator and
Bunce, Tim, "ODBC from Perl", pp. 167-172 for how to move a Perl script that
uses Win32::ODBC from Win32 to another platform.
If you want to try setting up a Win32 box to provide access to MS Access
files, you might try downloading and installing IndigoPerl, free from
www.indigostar.com, on the Win32 box. Note that you don't need to have MS
Access installed on the Win32 box to be able to access an MS Access file,
although it would certainly be helpful, especially if you need to generate
MS Access files for testing.
A couple of interesting projects I've encountered that may be helpful to
you:
1. #GUFE: Generic but Usable Front End
#by Aaron Weiss ([email protected]) for the Web Developers' Virtual Library
# http://www.wdvl.com
2. # ODBC query - requires PERL 5.003_7 or higher and Win32::ODBC
# Author Alan Johnston - modified by WAS 11May2002
# Downloaded from http://www.roth.net/perl/scripts/scripts.asp?ODBCQry.pl
I'd certainly *like* to be in your situation, as my preference, at times,
would be to know as little as possible about Windows.

I works hard at it :)


Thanks.

I'll check those out.
 
B

Bart Lateur

Stan said:
I find myself with the eed to obtain some data stored in Microsoft Access
format files from a perl script ruinning on a FreebSD box.

What's the best way to do this?

Do it on a Windows box. :)

I'm sorry to say, but the MS Access file format is proprietary, and
undocumented. There's an interface built into all modern Windows
versions, so if you have one of those, you can access it.

Either you do it live (ODBC solution, see some DBI FAQs; or dbi-proxy
running on the Windows box), or you convert the data to a database which
you can use on FreeBSD.

HTH,
Bart.
 
J

James Willmore

I find myself with the eed to obtain some data stored in Microsoft
Access format files from a perl script ruinning on a FreebSD box.

What's the best way to do this?

Use the appropriate DBD (aka Database Driver) that is used by the DBI
module (aka Database Interface Module).
The files are actually on a Windows machien, but I can mount the
partiton using Samba.

There is one issue to be addressed here - Access is NOT an RDBMS -
like MySQL, PostgreSQL, or Oracle. It "lives" on a local machine (aka
no ports/sockets are opened to allow for remote access to the database
file). So, the issue is first - "How do I in FreeBSD 'talk' ODBC?".
You need to use an application that will 'talk' ODBC - such as
UnixODBC. For more information about ODBC datasources and available
ports, check the FreeBSD website or your local FreeBSD newsgroup.

A way to get around the ODBC issue is to (depending on the size,
structure, etc) convert the Access database into a CSV file and use
the DBD::CSV module (or other CSV modules, or just 'roll your own') to
access the file. I suggest this because you mention you can mount the
Windows partition on your FreeBSD box. If the Access database is
small, converting to CSV should not be a problem. Then, using the DBI
module and the CSV driver, you could use the file as a 'real'
database, complete with using SQL to select, update, delete, etc. Not
the most efficient solution, but it is a solution.

Another way to do it is to install Perl on the Windows machine (since
it's a separate box) and then use DBI::proxy. This will allow you to
set up an ODBC DSN on the Windows box and allow the FressBSD box to
'connect' to the DSN. I can't say I've done it, but have read about
this as a solution.

One last solution is to take the database in Access and move it into
an RDBMS (such as MySQL or PostgreSQL - which FreeBSD has ports for
both). Then you could use Perl's DBI module and the appropriate DBD
module to access the database.

HTH

Jim
 
J

James Willmore

I find myself with the eed to obtain some data stored in Microsoft
Access format files from a perl script ruinning on a FreebSD box.

What's the best way to do this?

Use the appropriate DBD (aka Database Driver) that is used by the DBI
module (aka Database Interface Module).
The files are actually on a Windows machien, but I can mount the
partiton using Samba.

There is one issue to be addressed here - Access is NOT an RDBMS -
like MySQL, PostgreSQL, or Oracle. It "lives" on a local machine (aka
no ports/sockets are opened to allow for remote access to the database
file). So, the issue is first - "How do I in FreeBSD 'talk' ODBC?".
You need to use an application that will 'talk' ODBC - such as
UnixODBC. For more information about ODBC datasources and available
ports, check the FreeBSD website or your local FreeBSD newsgroup.

A way to get around the ODBC issue is to (depending on the size,
structure, etc) convert the Access database into a CSV file and use
the DBD::CSV module (or other CSV modules, or just 'roll your own') to
access the file. I suggest this because you mention you can mount the
Windows partition on your FreeBSD box. If the Access database is
small, converting to CSV should not be a problem. Then, using the DBI
module and the CSV driver, you could use the file as a 'real'
database, complete with using SQL to select, update, delete, etc. Not
the most efficient solution, but it is a solution.

Another way to do it is to install Perl on the Windows machine (since
it's a separate box) and then use DBI::proxy. This will allow you to
set up an ODBC DSN on the Windows box and allow the FressBSD box to
'connect' to the DSN. I can't say I've done it, but have read about
this as a solution.

One last solution is to take the database in Access and move it into
an RDBMS (such as MySQL or PostgreSQL - which FreeBSD has ports for
both). Then you could use Perl's DBI module and the appropriate DBD
module to access the database.

HTH

Jim
 
S

Stan Brown

Use the appropriate DBD (aka Database Driver) that is used by the DBI
module (aka Database Interface Module).
There is one issue to be addressed here - Access is NOT an RDBMS -
like MySQL, PostgreSQL, or Oracle. It "lives" on a local machine (aka
no ports/sockets are opened to allow for remote access to the database
file). So, the issue is first - "How do I in FreeBSD 'talk' ODBC?".
You need to use an application that will 'talk' ODBC - such as
UnixODBC. For more information about ODBC datasources and available
ports, check the FreeBSD website or your local FreeBSD newsgroup.
A way to get around the ODBC issue is to (depending on the size,
structure, etc) convert the Access database into a CSV file and use
the DBD::CSV module (or other CSV modules, or just 'roll your own') to
access the file. I suggest this because you mention you can mount the
Windows partition on your FreeBSD box. If the Access database is
small, converting to CSV should not be a problem. Then, using the DBI
module and the CSV driver, you could use the file as a 'real'
database, complete with using SQL to select, update, delete, etc. Not
the most efficient solution, but it is a solution.
Another way to do it is to install Perl on the Windows machine (since
it's a separate box) and then use DBI::proxy. This will allow you to
set up an ODBC DSN on the Windows box and allow the FressBSD box to
'connect' to the DSN. I can't say I've done it, but have read about
this as a solution.
One last solution is to take the database in Access and move it into
an RDBMS (such as MySQL or PostgreSQL - which FreeBSD has ports for
both). Then you could use Perl's DBI module and the appropriate DBD
module to access the database.

I'm reading (perhaps incorectly) in what you are saying that thee is no
Windows way of providing a n ODBC server on the windows side, with the
access database as a data source.

Is this correct?

If not, could I not do this, and use DBI::ODBC?
 
J

James Willmore

I'm reading (perhaps incorectly) in what you are saying that thee is
no Windows way of providing a n ODBC server on the windows side,
with the access database as a data source.

Is this correct?

If not, could I not do this, and use DBI::ODBC?

You can use ODBC easily if you are on a Windows box. ODBC, from my
understanding, is a Microsoft standard that has become mainstream.
So, in Windows, you can define a DSN and, from a Windows machine,
access the DSN. Access was intended to be used on a local machine.
It CAN be access through a variety of methods on a Windows network,
but it was originally desgned as a single user database.

The intent of the orignal post I made was simply this - if you use an
OS OTHER THAN Windows to use an Access database, the method to use the
database changes. You need to use something that talks to an ODBC
datasource. So, just mounting the Windows partition (through, say
Samba) is NOT enough. And to just use an ODBC Perl module is NOT
enough. The module needs some way to interact with the ODBC
datasource - since the datasource is just a file and ONLY a file (ie
it's not being served through a server). That's where the methods I
described in the previous post come into play.

You can use something like UnixODBC (and there's another package
whose name eludes me right now) to connect to the datasource - no
server required. But you CAN'T use something in Perl to just straight
away connect to the datasource - unless it's something like DBI::proxy
running on the Windows machine and using DBD::ODBC on the FreeBSD
machine to connect - which I described in the previous post.

That's my understanding of the whole proccess. If someone sees an
error in the way I'm describing it, please let me know. For now, your
questions may be more properly addressed in a FreeBSD newsgroup first
- since you need to set up a way for FreeBSD to access an ODBC
datasource.

HTH

Jim
 
J

James Willmore

If you have access to a Win32 computer:

1. "Start + Settings + Control Panel" and look for "ODBC Data
Sources(32bit)".
2. Open it and go to the "System DSN" tab.
3. Add a System Data Source with the Microsoft Access Driver
(*.mdb), giving it the name of the Access file you wish to use.

Now you should be able to use DBI::ODBC to make a connection to the
Win32 box that has the Access file on it.

From a FreeBSD box to a Windows box using this method?
Have you tried this?
Does it work?

I'm thinking you may have missread the OP. He wants to connect TO a
WIN32 box FROM FreeBSD. Can not be done as you indicated. If it has,
please let me know.

Jim
 
J

James Willmore

From a FreeBSD box to a Windows box using this method?
No. I don't have a FreeBSD box. I can try it from a Linux box; but i
thought I'd leave a little work for the OP to do. He will learn
something from the exercise.

Linux - FreeBSD - all Unix - NOT Windows. Read the OP.
IIRC, works fine on this Win32 system, as I had set it up several
times. It's working in a CGI environment (IndigoPerl) on this Win32
system; so access from FreeBSD, if I had it, should work. It *does*
work from other Win32, as well as Linux buxes on this network.

A CGI environment. In other words, you used a web server to connect
to the ODBC datasource? So, the drivers needed to access the ODBC
datasource were on the Windows box, right? And the connection to the
Windows box was through a web server, right? So, basiclly, it didn't
work FROM the non-Windows machine without the use of a web server.
Which is why I suggested useing DBI::proxy - same concept, different
approach. Still need a server or drivers to run on one box or the
other to accomplish the task. Simply setting up a DSN on the Windows
box won't work without a server or having drivers on the foriegn
machine. Interesting approach, but not what the OP was after.
No. I understand what the OP is seeking. AFAIK, a System DSN would
work. Have you proved your claim that it "can not be done as ...
indicated"? Why don't you try it with *your* FreeBSD system and I'll
try it with Linux? I'll post the results of my tests.

I have. When I first started using Linux (and later FreeBSD and
SunOS and variants of Windows and MVS), I used the approach the
setting up a DSN on a Windows machine
would fit the bill. Not so. But hey, don't take my work for it.
Here's a blurb from the book "Programming the Perl DBI". In the
section for DBD::ODBC, it states:
"For Win32, the driver manager is included with the operating system.
For Unix and varients, the iODBC driver manager is included in the
iodbc directory. While iODBC acts as the driver manager (in italics),
you still have to find an actual driver for your platform and
database."
It goes on to mention, get this, ODBC drivers for Unix. One mentioned
is ... UnixODBC. I'm sure if you take the time to do some research on
your own, you'll see what I'm talking about.

I placed the OP below your review. The OP mentions mounting the
Windows partition, which tells me the person posting wanted to use the
file WITHOUT using a server.

I'm done with this thread. Have a nice day.

===START ORIGINAL POST====
I find myself with the eed to obtain some data stored in Microsoft
Access
format files from a perl script ruinning on a FreebSD box.

What's the best way to do this?

The files are actually on a Windows machien, but I can mount the
partiton
using Samba.
===END ORIGINAL POST====
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top