Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

MS Access through ADO and ODBC DSN

by zer (Deacon)
on Aug 31, 2009 at 05:04 UTC ( #792284=perlquestion: print w/replies, xml ) Need Help??

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

Good evening

With my DataSource: dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\somedir\Desktop\sqr\srv\TngDB.mdb. I have been getting the following error:

unknown database Ms_Jet - using GenericSubQ dialect at C:/Perl/site/li +b/SQL/Abstract/ line 406, <$F> line 21029. DBIx::Class::ResultSet::find_or_create(): DBI Exception: DBD::ADO::st +execute failed: Can't Execute Command 'SELECT, me.Course FROM C +ourse me WHERE ( me.Course = ? )' Package : DBD::ADO::st Filename : C:/Perl/site/lib/DBD/ Line : 1234 Last error : -2147217904 OLE exception from "Microsoft JET Database Engine": Parameter ?_1 has no default value. Win32::OLE(0.1709) error 0x80040e10 in METHOD/PROPERTYGET "Execute" Description : Parameter ?_1 has no default value. HelpContext : 5003747 HelpFile : NativeError : -542379020 Number : -2147217904 Source : Microsoft JET Database Engine SQLState : 3748 [for Statement "SELECT, me.Course FROM Course me WHER +E ( me.Course = ? )" with ParamValues: 1='P RES BMQ'] at lin +e 38

If i change to DataSource: dbi:ODBC:driver=Microsoft Access Driver(*.mdb);dbq=C:\Users\somedir\Desktop\sqr\srv\TngDB.mdb I get:

DBIx::Class::ResultSet::find_or_create(): DBI Connection failed: DBI c +onnect('dr iver=Microsoft Access Driver(*.mdb);dbq=C:\Users\somedir\Desktop\sqr\s +rv\TngDB.mdb' ,'',...) failed: [Microsoft][ODBC Driver Manager] Data source name not + found and no default driver specified (SQL-IM002) at C:/Perl/site/lib/DBIx/Clas +s/Storage/ line 950

This is code which works with my SQLite3 db's however I can't seem to do anything to make it work with MS Access.

#!/usr/bin/perl use strict; use warnings; use feature ':5.10'; use lib 'lib'; use SPFHandler; use DBFacade; use YAML; use Data::Dumper; my $config = YAML::LoadFile('config.yml')->{Database}->{$^O}; my $db = DBFacade->connect($config->{DataSource}, $config->{Username}, $config->{Password}, $config->{DBIAttributes}, $config->{ExtraAttributes}, ) or die 'Could not connect'; my $rspc =$db->resultset('PersCourse'); my $rsp =$db->resultset('Pers'); my $rsc =$db->resultset('Course'); open (my $F, $ARGV[0])or die 'No file specified'; my @FILE = <$F>; my $SPF = SPFHandler->new(@FILE); $SPF->Parse(); open my $f, '>tmp/out.txt'; print $f Dumper $SPF; my $c = 0; my $i=0; for my $file(@{$SPF->Files()}){ my $pers = $rsp->find_or_create({SN=>$file->SN}); if ($file->Courses){ for my $course(keys %{$file->Courses}){ my $crse = $rsc->find_or_create({Course=>$course}); $rspc->find_or_create ( { CourseCode=>$file->Courses->{$course}->{id}, DateField=>$file->Courses->{$course}->{date}, CourseId=>$crse->get_column('id'), PersId=>$pers->get_column('id') } ); } } $i++; }

I am running vista, and the msjet40.dll exists. I would appreciate any help i can get with this

Thank You

Replies are listed 'Best First'.
Re: MS Access through ADO and ODBC DSN
by Jenda (Abbot) on Aug 31, 2009 at 17:56 UTC

    The driver should be "Microsoft Access Driver (*.mdb)" not "Microsoft Access Driver(*.mdb)". Whether that will be enough for SQL::Abstract::Limit to select the right syntax is a question.

    Look into HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI for the list of installed drivers.

    Enoch was right!
    Enjoy the last years of Rome.

      Thank you, this worked like a charm!

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2022-10-05 22:23 GMT
Find Nodes?
    Voting Booth?
    My preferred way to holiday/vacation is:

    Results (25 votes). Check out past polls.