Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Connection Problem to MS Access Database using DBI

by merrymonk (Hermit)
on May 16, 2019 at 21:30 UTC ( [id://11100129]=perlquestion: print w/replies, xml ) Need Help??

merrymonk has asked for the wisdom of the Perl Monks concerning the following question:

I have a library of subroutines that I successfully used to manipulate some MYSQL databases. I want to use the same subroutines, if possible (I know some changes will be required and I have tried to do this in the connect subroutine), to manipulate a Microsoft Access database. Therefore to start I wanted to prove that I can connect to the Access database that I have created. The Perl below attempts to connect to the database which is in the same folder as the Perl code. When I run the test code I get the following in an MSDOS command screen.

[access_connect] entry dsn <> name <Full Path to Access Database\bacce +ss.accdb> user <> passwd <> [access_connect] after dsn <driver=Microsoft Access Driver(*.mdb);dbq= +$db_name> name <Full Path to Access Database\baccess.accdb> user <> p +asswd <> DBI connect('driver=Microsoft Access Driver(*.mdb);dbq=$db_name','',.. +.) failed: [Microsoft][ODBC Driver Manager] Data source name not foun +d and no default driver specified (SQL-IM002) at Full Path to Access +Database\acess_open_close_a.pz line 65. DBI connect('driver=Microsoft Access Driver(*.mdb);dbq=$db_name','',.. +.) failed: [Microsoft][ODBC Driver Manager] Data source name not foun +d and no default driver specified (SQL-IM002) at Full Path to Access +Database\acess_open_close_a.pz line 65. [access_connect] DBI connection failed - count <2>
After connect return_code <0> err_message <[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002)>

Can someone explain how I can remove both errors?
use strict "vars"; use Win32; use Exporter; use DBI; use File::Basename; our $global_diag; $global_diag = 'no'; #===================================================================== +========= # # sub access_connect # # this connects to a given access database # # arguments # 1 $db_name name of the database # 2 $db_user name fo the user # 3 $db_password password ofr the user # 4 $ref_dbh reference to the handle returned when successful # 5 $ref_return_code reference to the return code # 1 ok # 0 failed # 6 $ref_err referecne to the system error message given when + failure # #===================================================================== +======== sub access_connect ($$$$$$$) { my ($host_name, $db_name, $db_user, $db_password, $ref_dbh, $ref_retur +n_code, $ref_err) = @_; my ($dsn, $dbh_ok, $en); my ($loop_max, $loop_cou, $loop_fnd, $sleep_time); $loop_max = 2; print "[access_connect] entry dsn <$dsn> name <$db_name> user <$db_use +r> passwd <$db_password>\n"; # set the sleep time in secs $sleep_time = 25; $dsn = 'driver=Microsoft Access Driver(*.mdb);dbq=$db_name'; print "[access_connect] after dsn <$dsn> name <$db_name> user <$db_use +r> passwd <$db_password>\n"; $dbh_ok = 'yes'; # have loop to try loop max times $loop_cou = 0; while($loop_cou < $loop_max) { $loop_cou += 1; $$ref_dbh = DBI->connect("dbi:ODBC:$dsn", $db_user, $db_password) +or ($dbh_ok = 'no'); if($dbh_ok eq 'yes') { $loop_fnd = $loop_cou; $loop_cou = $loop_max + 10; } else { sleep($sleep_time); } } if($dbh_ok eq 'no') { print "[access_connect] DBI connection failed - count <$loop_cou>\ +n"; $$ref_return_code = 0; $$ref_err = $DBI::errstr; } else { print "[ access_connect] DBI connection made - handle <$$ref_dbh> +at count <$loop_fnd>\n"; if($loop_fnd > 1) { print "[ access_connect] DBI connection made - handle <$$ref_ +dbh>\n" . "more than one attempt required with count of +<$loop_fnd>\n"; } $$ref_return_code = 1; $$ref_err = ''; } } #===================================================================== +========= # # sub access_disconnect # # this disconnects to a given mysql database # # arguments # 1 $db_handle handle of the already opened database # 2 $ref_return_code reference to the return code # 1 ok # 0 failed # 3 $ref_err referecne to the system error message given when + failure # #===================================================================== +======== sub access_disconnect{ my ($db_handle, $ref_return_code, $ref_err) = @_; print "[access_disconnect] handle <$db_handle>\n"; $$ref_return_code = $db_handle->disconnect(); if($$ref_return_code == 1) { $$ref_err = ''; } else { $$ref_err = $DBI::errstr; } } #============================main############################# my ($mysql_dbh, $return_code, $err_message); my ($db_hostname,$db_name, $db_user, $db_password, $db_table, $pg_dir) +; # get the database paramters $db_hostname = 'localhost'; $db_name = 'baccess.accdb'; $db_user = ''; $db_password = ''; $db_table = 'TableA'; $pg_dir = dirname($0); $db_name = $pg_dir . "\\" . $db_name; if(-f $db_name) { access_connect($db_hostname, $db_name, $db_user, $db_password, \$mysql +_dbh, \$return_code, \$err_message); print "\nAfter connect return_code <$return_code> err_message <$err_me +ssage>\n"; } else { print "\ndatabase <$db_name> NOT found\n"; }

2019-05-20 Athanasius added code tags

Replies are listed 'Best First'.
Re: Connection Problem to MS Access Database using DBI
by soonix (Canon) on May 17, 2019 at 05:39 UTC
    perhaps one of my Connection strings works for you:
    sub conn { my $dbistring; # $dbistring = 'dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Sour +ce='; # 32bit # $dbistring = 'dbi:ADO:Provider=Microsoft.ACE.OLEDB.12.0;Data Sou +rce='; # 64bit $dbistring = 'dbi:ODBC:Driver={Microsoft Access Driver (*.mdb, *.a +ccdb)};DBQ='; my $dbh = DBI->connect($dbistring . shift) or die "C ", $DBI::errs +tr; return $dbh; }
    Update: Your error message says that it can't find the database file. You should give the full path name.

    Windows can in most cases deal with file paths given with '/' instead of '\' - helps much against leaning toothpick syndrome (and "//server/share/folder/file.accdb" looks much nicer than "\\\\server\\share\folder\file.accdb", anyway). In addition, Path::Tiny helps.

    I use it like
    use DBI; use Path::Tiny 'path'; ... my $dbname = path '//server/share/whatever/it is.mdb'; $dbh = conn $dbname->canonpath; ...
      Thank you.
      Having searched my system I believe I did connect to an Access database in 2008 (clearly using an earlier version of Access compared to Access 2010 I now am using)
      The dsn definition was
      $dsn = "DBI:ODBC:PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.m +db)};DBQ=" . $db_name;
      or
      $dsn = "DBI:ADO:PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.md +b)};DBQ=" . $db_name;

      The connect Perl row was
      DBI->connect($dsn, "", "") or ($dbh_ok = 'no');

      I did try one of the dsn defintions suggest (it is below) there are some small differences however I got exaclty the same error messages.
      $dsn = 'dbi:ODBC:Driver={Microsoft Access Driver (*.mdb, )};DBQ=' . $d +b_name;
      I am not sure which worked but I think one of them did since the Perl writes the meta data for the database and there is a file of this as well.
      When I use the code now I get the following error messages for the ODBC version - I could not use the ADO since ADO was no longer there.

      DBI connect('PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=FULL PATH TO Access Databse\StockH.mdb','',...) failed: MicrosoftODBC Driver Manager Data source name not found and no default driver specified (SQL-IM002)

      Therefore it looks like Access 2010 needs something different to what was successful for a previous version of Access.

      Can anyone tell me what I should be using for this?

        This might be a 32 bit / 64 bit problem. I have 64 bit Windows 10 but have 32 bit Office 10. This code works when I tried it on Portable Strawberry 32 bit but not with 64 bit perl.

        #!perl use strict; use DBI; my $dbfile = "c:/temp/Database.accdb"; my $dsn = 'driver=Microsoft Access Driver (*.mdb, *.accdb);dbq='.$dbfi +le; my $dbh = DBI->connect("dbi:ODBC:$dsn", '', '') or die $DBI::errstr;; print join "\n",$dbh->tables();
        c:\temp>perl -v This is perl 5, version 16, subversion 1 (v5.16.1) built for MSWin32-x +64-multi-thread (with 1 registered patch, see perl -V for more detail) c:\temp>perl c:/temp/access.pl DBI connect('driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=c:/te +mp/Database.accdb','',...) failed: [Microsoft][ODBC Driver Mana ger] Data source name not found and no default driver specified (SQL-I +M002) at c:/temp/access.pl line 6. [Microsoft][ODBC Driver Manager] Data source name not found and no def +ault driver specified (SQL-IM002) at c:/temp/access.pl line 6.
        D:\strawberry-perl-no64-5.28.2.1-32bit-portable>perl -v This is perl 5, version 28, subversion 2 (v5.28.2) built for MSWin32-x +86-multi-thread D:\strawberry-perl-no64-5.28.2.1-32bit-portable>perl c:/temp/access.pl `c:/temp/Database.accdb`.`MSysAccessStorage` `c:/temp/Database.accdb`.`MSysACEs` `c:/temp/Database.accdb`.`MSysComplexColumns` `c:/temp/Database.accdb`.`MSysNameMap` `c:/temp/Database.accdb`.`MSysNavPaneGroupCategories` `c:/temp/Database.accdb`.`MSysNavPaneGroups` `c:/temp/Database.accdb`.`MSysNavPaneGroupToObjects` `c:/temp/Database.accdb`.`MSysNavPaneObjectIDs` `c:/temp/Database.accdb`.`MSysObjects` `c:/temp/Database.accdb`.`MSysQueries` `c:/temp/Database.accdb`.`MSysRelationships` `c:/temp/Database.accdb`.`MSysResources` `c:/temp/Database.accdb`.`Table1`
        poj
      Comment on update.
      I did give the full file name and tested this with
      if(-f $file_name)
      to make sure it could be 'found'.
        Hmmm... maybe it's a permission problem. Is the script running under the same user as you "normally" access the database? (In other words: can the user who runs the script, open the database via Access?)
Re: Connection Problem to MS Access Database using DBI
by afoken (Chancellor) on May 17, 2019 at 07:13 UTC

    DBD::ODBC::FAQ (see here while neither [mod://DBD::ODBC::FAQ] nor [doc://DBD::ODBC::FAQ] works) has several troubleshooting tips for accessing MS Access.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
      I tried the link you gave but sadly I got this error message
      The resource you requested could not be found.
Re: Connection Problem to MS Access Database using DBI
by merrymonk (Hermit) on May 16, 2019 at 21:39 UTC
    I should have said I am using Access which is part of MS Office Professional 2010 on a PC using Windows 10

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2024-03-29 11:43 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found