Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Connect to MS Access w/o ODBC, DSN, from Win32

by chiller (Scribe)
on Dec 12, 2001 at 04:54 UTC ( #131130=perlquestion: print w/ replies, xml ) Need Help??
chiller has asked for the wisdom of the Perl Monks concerning the following question:

I have been reading thru the articles w/r/t ADO and ODBC and getting thoroughly confused.

Is this even possible? I need to connect to a Microsoft Access database on an NT machine without an ODBC Data Source control panel. I cannot set up a DSN. This is not the way I'd like it, but for paranoid security reasons, that is just the way it is.

I have looked at Win32::OLE and DBI::ADO and these all seem to require a DSN. I cannot use any ODBC anything, for obvious reasons.

Short of redesigning the Access DB to export information to CSV, what are possible solutions?

Thanks.

Comment on Connect to MS Access w/o ODBC, DSN, from Win32
Re: Connect to MS Access w/o ODBC, DSN, from Win32
by Kanji (Parson) on Dec 12, 2001 at 05:04 UTC

    DBD::ODBC (alt.) has supported DSN-less connections for awhile.

    From the POD...

    Connect without DSN
    The ability to connect without a full DSN is introduced in version 0.21. Example (using MS Access):
    my $DSN = 'driver=Microsoft Access Driver(*.mdb);dbq=\\\\cheese\\g$\\p +erltest.mdb'; my $dbh = DBI->connect("dbi:ODBC:$DSN", '','') or die "$DBI::errstr\n" +;

        --k.


      Thanks, all. I tried the suggestions, but I get this error:
      DBI->connect(driver=Microsoft Access Driver *.mdb);dbq=\\SERVER\My_Dat +abase.mdb) failed: [Microsoft][ODBC Driver Manager] Invalid string or + buffer length (SQL-S1090)(DBD: db_login/SQLConnect err=-1) at connec +t.pl line 7 [Microsoft][ODBC Driver Manager] Invalid string or buffer + length (SQL-S1090)(DBD: db_login/SQLConnect err=-1)
      (What I am attempting to do is: get table from Access DB on Novell server, determine if Access DB in local machine in the same version, then copy a new version to the local machine if there has been an update... sounds easy enough.)
Re: Connect to MS Access w/o ODBC, DSN, from Win32
by mortis (Pilgrim) on Dec 12, 2001 at 06:08 UTC
    On Win32 you can connect directly to the access mdb file, but it still is through DBD::ODBC. The following code is a summarizaion of code I've used to manipulate pre-existing ms access databases:
    use strict; use warnings; use DBI; my $file = 'c:/path/to/access/database.mdb'; my $dsn = 'dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=' . $fi +le; my $dbh = DBI->connect($dsn,undef,undef);
    I'm not 100% sure this will solve your immediate problems, but it has worked for me when there was no system wide DSN for the access database. I do beleive this solution is documented somewhere in the DBI/DBD pod documentation.

    Also, for the creation of a new MDB file (which you can't otherwise do without resorting to Win32 calls or ADO), I created a simple empty database with Access, and then serialized it using MIME::Base64 and stuffed the serialized version into a module so it was easy to just create a new blank db:

    use strict; use warnings; use BlankMDB; BlankMDB->writeToFile('c:/path/to/file.mdb');
Re: Connect to MS Access w/o ODBC, DSN, from Win32
by strat (Canon) on Dec 12, 2001 at 17:10 UTC

    You could create and delete DSNs on the fly; have a look at DSN's at Re: DSN's. But I prefer the the two other possibilities given here until now...

    Best regards,
    perl -e "print a|r,p|d=>b|p=>chr 3**2 .7=>t and t"

    Edit ar0n -- Changed text link to node link

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://131130]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (7)
As of 2015-07-05 15:16 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (67 votes), past polls