s there a module to acess Micorsoft Access datafiles?

Discussion in 'Perl Misc' started by Stan Brown, Jul 18, 2003.

  1. Stan Brown

    Stan Brown Guest

    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.


    --
    "They that would give up essential liberty for temporary safety deserve
    neither liberty nor safety."
    -- Benjamin Franklin
     
    Stan Brown, Jul 18, 2003
    #1
    1. Advertising

  2. "Stan Brown" <> wrote in message
    news:bf9den$bom$...
    > 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
     
    Brian Helterline, Jul 18, 2003
    #2
    1. Advertising

  3. "William Alexander Segraves" <> wrote in message
    news:bf9lhr$mv7$...
    > "Stan Brown" <> wrote in message
    > news:bf9den$bom$...
    > > 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?

    >
    > Win32::ODBC


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

    Bill Segraves
     
    William Alexander Segraves, Jul 18, 2003
    #3
  4. "Stan Brown" <> wrote in message
    news:bf9n42$f1b$...
    > In <bf9lhr$mv7$> "William Alexander Segraves"

    <> writes:
    >
    > >"Stan Brown" <> wrote in message
    > >news:bf9den$bom$...
    > >> 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?

    >
    > >Win32::ODBC

    >


    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 () 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
     
    William Alexander Segraves, Jul 18, 2003
    #4
  5. Stan Brown

    Stan Brown Guest

    In <bfa062$3rb$> "William Alexander Segraves" <> writes:

    >"Stan Brown" <> wrote in message
    >news:bf9n42$f1b$...
    >> In <bf9lhr$mv7$> "William Alexander Segraves"

    ><> writes:
    >>
    >> >"Stan Brown" <> wrote in message
    >> >news:bf9den$bom$...
    >> >> 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?

    >>
    >> >Win32::ODBC

    >>


    >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.


    >>
    >> 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 () 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.





    --
    "They that would give up essential liberty for temporary safety deserve
    neither liberty nor safety."
    -- Benjamin Franklin
     
    Stan Brown, Jul 19, 2003
    #5
  6. Stan Brown

    Bart Lateur Guest

    Stan Brown wrote:

    >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.
     
    Bart Lateur, Jul 19, 2003
    #6
  7. > 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
     
    James Willmore, Jul 19, 2003
    #7
  8. > 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
     
    James Willmore, Jul 19, 2003
    #8
  9. Stan Brown

    Stan Brown Guest

    In <> James Willmore <> writes:

    >> 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.


    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?

    --
    "They that would give up essential liberty for temporary safety deserve
    neither liberty nor safety."
    -- Benjamin Franklin
     
    Stan Brown, Jul 19, 2003
    #9
  10. On Sat, 19 Jul 2003 15:54:55 +0000 (UTC)
    Stan Brown <> wrote:

    >
    > 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
     
    James Willmore, Jul 19, 2003
    #10
  11. >
    > 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
     
    James Willmore, Jul 19, 2003
    #11
  12. > > From a FreeBSD box to a Windows box using this method?
    > > Have you tried this?

    >
    > 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.

    >
    > > Does it work?
    > >

    >
    > 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.

    >
    > > 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.
    > >

    >
    > 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====
     
    James Willmore, Jul 20, 2003
    #12
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Z2F1cmF2?=

    Uploading Data From MS Acess 2000 ti SQL server 2000

    =?Utf-8?B?Z2F1cmF2?=, Jan 9, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    823
    Mary Chipman [MSFT]
    Jan 9, 2006
  2. Rajesh R.
    Replies:
    1
    Views:
    1,643
    Bob Barrows
    Jan 9, 2004
  3. =?Utf-8?B?Sm9yZGk=?=

    ODBC data acess and asp.Net applications.

    =?Utf-8?B?Sm9yZGk=?=, Jun 10, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    2,724
    Kevin Spencer
    Jun 10, 2004
  4. Ian

    Accessing XML DataFiles from a Web Service

    Ian, Oct 26, 2006, in forum: ASP .Net Web Services
    Replies:
    1
    Views:
    111
    Jeff Dillon
    Nov 2, 2006
  5. Roberto S.

    Help with Micorsoft Agent

    Roberto S., Sep 11, 2003, in forum: ASP General
    Replies:
    0
    Views:
    110
    Roberto S.
    Sep 11, 2003
Loading...

Share This Page