in reply to Re: Connection Problem to MS Access Database using DBI
in thread Connection Problem to MS Access Database using DBI

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: 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?

Replies are listed 'Best First'.
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]

In Section Seekers of Perl Wisdom