Re: Connection Problem to MS Access Database using DBI

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;
}
[download]

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;
...
[download]

Comment on Re: Connection Problem to MS Access Database using DBI Select or Download Code

Replies are listed 'Best First'.
Re^2: Connection Problem to MS Access Database using DBI by merrymonk (Hermit) on May 17, 2019 at 10:42 UTC
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;` [download] or `$dsn = "DBI:ADO:PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (.md +b)};DBQ=" . $db_name;` [download] The connect Perl row was `DBI->connect($dsn, "", "") or ($dbh_ok = 'no');` [download] 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;` [download] 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: Microsoft ODBC 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?	[reply] [d/l] [select]
Re^3: Connection Problem to MS Access Database using DBI by poj (Abbot) on May 17, 2019 at 13:25 UTC
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();` [download] 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. [download] 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` [download] poj	[reply] [d/l] [select]
Re^2: Connection Problem to MS Access Database using DBI by merrymonk (Hermit) on May 17, 2019 at 10:45 UTC
Comment on update. I did give the full file name and tested this with `if(-f $file_name)` [download] to make sure it could be 'found'.	[reply] [d/l]
Re^3: Connection Problem to MS Access Database using DBI by soonix (Chancellor) on May 18, 2019 at 18:37 UTC
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?)	[reply]