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

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";

}
[download]

2019-05-20 Athanasius added code tags

---

In reply to Connection Problem to MS Access Database using DBI by merrymonk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.