Re: Re: Accessing MS Access through the DBI ODBC

> From: Kevin Carothers <kevindotcar@gmail.com>
> Date: 2005/03/30 Wed PM 06:44:48 CST

>  1.  Create an ODBC  connection via %SystemRoot%\system32\odbcad32.exe
>       Call the database connect name "ODBCName"
> 
>  2.   In your Perl code add:
> [---]
>    use DBI;
> [---]
>    $dbh = DBI->connect("dbi:ODBC:ODBCName",'','');
> [---]

Just my opinion, but I don't think this is good advice.  For one, it creates
extra administration on the system, by forcing the user/programmer
/sysadmin/webadmin to create an ODBC DSN setting for every new setup.
For two, DBI and ODBC both support dynamic DSNs, so why not use them?

Here is a connect string that works for Access databases:
my $dbh = DBI->connect( "dbi:ADO::Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=$path_to_mdb_file" ,'admin', {RaiseError => 1, AutoCommit => 1} );

And here's a little extra, showing how to build connections for Access 
(via ADO), MSSQL (via ODBC), Sybase, and Oracle...

my %dbDrivers = ("SYBASE"   => "Sybase",
                 "ORACLE"   => "Oracle",
                 "MSSQL"    => "ODBC",
                 "ACCESS9"  => "ADO");

my $ConnStr = buildConn();
my $dbh;
if ((uc($arg_db_type) eq "ACCESS2K")&&(uc($arg_db_type) eq "ACCESS97")) {
  $dbh = DBI->connect( $ConnStr, 'admin', {RaiseError => 1, AutoCommit => 1} );
} elsif (uc($arg_db_type) eq "SYBASE") {
  $dbh = DBI->connect( $ConnStr.";database=$arg_use_db", $arg_sysuser, $arg_syspwd, {RaiseError => 1, AutoCommit => 1} );
} elsif (uc($arg_db_type) eq "ORACLE") {
      $dbh = DBI->connect( $ConnStr, $arg_sysuser, $arg_syspwd, {RaiseError => 1, AutoCommit => 1} );
}

# Build Connection String
sub buildConn {
  my $retval = "dbi:". $dbDrivers{$arg_db_type};
  if (uc($arg_db_type) eq "ORACLE") {
    $retval .= ":host=". $arg_connection .";sid=". $arg_use_db;
  } elsif (uc($arg_db_type) eq "SYBASE") {
    $retval .= ":server=". $arg_connection;
  }elsif (uc($arg_db_type) eq "MSSQL") {
    $retval .= ':driver={SQL Server};Server='. $arg_connection .';database='. $arg_use_db .';';
  }elsif ((uc($arg_db_type) eq "ACCESS97") or (uc($arg_db_type) eq "ACCESS2K")) {
    $arg_syspwd = ''; # Need to set the password to NULL for Access....
    $retval .= ":Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=". $arg_use_db ."";
  }
  return $retval;
}

> kevindot

HTH,
amonotod


--

    `\|||/         amonotod@    | sun|perl|windows
      (@@)         charter.net  | sysadmin|dba
  ooO_(_)_Ooo____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|

0
amonotod
3/31/2005 3:03:20 PM
perl.dbi.users 11100 articles. 1 followers. Follow

1 Replies
823 Views

Similar Articles

[PageSpeed] 20

> 
> Just my opinion, but I don't think this is good advice.  For 
> one, it creates extra administration on the system, by 
> forcing the user/programmer /sysadmin/webadmin to create an 
> ODBC DSN setting for every new setup. For two, DBI and ODBC 
> both support dynamic DSNs, so why not use them?

If you have separate development, test and production environments, it's sometimes easier
to use DSNs.  That way, when you migrate from dev to test, for example, you automagically
get the test environment connection information.  That's one reason why I like DSNs over
putting them in the code.

Of course, TMTOWTDI...

Jeff

0
jurlwin
4/3/2005 8:11:51 PM
Reply:

Similar Artilces:

Re: Re Re: SQL Anywhere ODBC -> MS-Access
Addition: In both cases MS-Access and ISQL, I was going to the end of the list to compare the speed. I found that each, the DBCLIENT and MS-Access where using the CPU at 25- 45 % (Kernel-Toys) The cpu usage of the NT-Server where Sybase SQL Server is running is about 98% during the execution of the query. It doesn't matter if i use << Pass Through >> or not ! HMB ---------- ...

Accessing MS Access through the DBI ODBC
Hi all, I am trying to access an MS Access database via perl DBI and I am unable = to. I checked the security and there is only one 'Admin' user with no = password and I use that in my connection string. However, I cannot = connect. Please help. Regards, Javier Moreno Hi Javier, > I am trying to access an MS Access database via perl DBI and I am unable to. I checked the security and there is only one 'Admin' user with no password and I use that in my connection string. However, I cannot connect. Please help. > 1. Create an ODBC connection via %Sys...

Re Re: SQL Anywhere ODBC -> MS-Access
Hello ... to continue... Sorry, but im bound to MS-Access, there is already too much developed in MS-Access to switch over to any C++. I found that i, - perhaps -, have an old version of the Sybase ODBC Driver, what is the actual version Nummber ? The decision to buy Sybase SQL depends on, the bought of KHK from the SAGE Company (in Germany). But i have made simple comparisons with MS-SQL (which is a catasthrophe to handle) and found that MS-Access was delivering faster results (minimum factor 10), due to this i think that my Sybase ODBC - driver is too old!! HMB >Bu...

Re: Re: MS Access
I tried the trace. Nothing on SQLNumColumns mentioned in the log, the closest thing in there is SQLColumnsW. I've included a piece of it : pb60 109:77 ENTER SQLColumnsW HSTMT 0x029b2808 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x00000000 SWORD 0 WCHAR * 0x029b2118 [ 8] "i300" SWORD 8 WCHAR * 0x00000000 SWORD 0 pb60 109:77 EXIT SQLColumnsW with return code 0 (SQL...

Antwort: RE: Antwort: Re: Access into MVS DB2 using DBI or ???
Hi everybody , if you don't know the db2-connectivity-details there are 5 sheat-sheets= from IBM who discuss this matter! The first one is to be found under http://www-106.ibm.com/developerworks/db2/library/techarticle/0301chong= /0301chong2.html which should clarify your basic questions. If you need the others as well they are to be found at: http://www-106.ibm.com/developerworks/db2/library/techarticle/0310chong= /0310chong.html http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0312ch= ong/index.html http://www-106.ibm.com/developerworks/db2/lib...

MS SQL through MS Access using DBI:ODBC
Hi When connecting an MS SQL database from MS Access it is necessary to use the option dbSeeChanges with db.openrecordset. This is ok when using Visual Basic but is it possible to access the data using DBI:ODBC. Win2000 MS SQL version 7 MS Access 97 perl 5.005 (build 509) W32ODBC.pm,v 10.1 1998/08/14 Thanks. Ian ...

Dbi objects to access an MS Access Db
I'm having a problem getting Tran-ide to recognize my access 2000 database. I've created a system dsn called Midas and created the attached nnsyreg.dat file and put it into e:\sybase\ImpactClient-5_3\config directory. I've also put this file in my projects directory and still get the following message. Unable to connect to session Midas. Any Advice? Thanks Brent begin 666 nnsyreg.dat M4V5S<VEO;BY-:61A<PT*"4Y.3U1?4TA!4D5$7TQ)0E)!4ED@/2!D8G0R-VUS M;V1B8PT*"4Y.3U1?1D%#5$]265]&54Y#5$E/3B ]($Y.4V5S3T1"0T9A8W1O M<GD-"@E.3E]315-?4T525D52(#T@24U...

Re: (it was) Access/Excell DBI
As a perl-dbi beginner I'm extremely interested to access MS-Access db by means of perl on a linux box. The 'context' is as follows: 1) Windows NT LAN to which my Win NT desktop (the one I have *.mdb files on) is connected; 2) A linux portable PC connected to the same NT LAN (SAMBA) My question is: Is it possible to access an *.mdb file somewhere in the LAN by means of my linux box through perl & DBD-ODBC as briefly explained below? And, if yes, could you please give me the detail of this connection? Ciao Vittorio Richard Chiswell [dbi-users] <17/...

RE: Access/Excell DBI (Again!!!)
First it's already been explained in the thread below. Second if you look through the archive you'll see hundreds of messages of this nature. But a brief overview. You either need to use DBI::ProxyServer and DBD::Proxy, see documentation. Or you can install third party ODBC software on Linux and set up a DSN. Then use DBD::ODBC. Ilya -----Original Message----- From: Vittorio To: dbi-users Sent: 10/18/01 5:39 AM Subject: Re: Access/Excell DBI (Again!!!) As a perl-dbi beginner I'm extremely interested to access MS-Access db by means of perl on a linux box. ...

Fw: RE: MS Access file
-----/r3uzxmbf1udBnbq7NXZXci10YH Content-Type: text/plain; charset=big5 Content-Transfer-Encoding: quoted-printable You=20should=20use=20the=20openlink=20sdk=20connect=20to=20windows.Use=20the= =20Multi-tie=20version=20of=20oprnlink=20software=20and=20SDK. Download=20the=20l3brzzzz.taz=20,l3kozzzz.taz=20,l3kuzzzzz.taz=20,l3oczzzz.t= az=20and=20install.sh=20for=20linux.And=20remeber=20setup=20the=20$ENV=20bef= ore=20you=20make. Download=20the=20ntbrzzzz.zip=20for=20windows.=20On=20windows,you=20should=20= install=20the=20MSDE=20to=20transfer=20the=20access(*.mdb)=20to=20MS-SQL=20...

RE: [dbi] Re: MsSQL DBD::ODBC IsNull and undef
Was this supposed to happen? A large # of tests now fail... t/07bind.........ok 1/11Can't rebind placeholder 3 at t/ODBCTEST.pm line 228. # Looks like you planned 11 tests but only ran 2. # Looks like your test died just after 2. t/07bind.........dubious Test returned status 255 (wstat 65280, 0xff00) DIED. FAILED tests 3-11 Failed 9/11 tests, 18.18% okay t/08bind2........ok 1/5# These are tests which rely upon the driver to tell what the parameter type is for the column. This means you need to ensure you tell your driver the type of the column in bind_col(). ...

RE: DBI FAQ for Driver specific (was RE: DBI FAQ maintainer sough t!)
I agree, definitely. I'm going to look tonight and see how I can organize/rearrange stuff so that everything is grouped. Don't want to have specific FAQs mixed in with general DBI, etc... It will be more heading based. So there will be a general DBI header, then DBDs, then platform specific stuff. Let me know if you have some other ideas/comments. Ilya -----Original Message----- From: Jeff Urlwin To: Sterin, Ilya; 'Tim Bunce ' Cc: ''Chris Pepper ' '; dbi-users@perl.org Sent: 3/12/02 11:28 AM Subject: DBI FAQ for Driver specific (was RE: ...

RE: Re: Re: Oracle DBI commit/rollback question...
--openmail-part-12614949-00000001 Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline ;Creation-Date="Thu, 7 Feb 2002 16:31:07 -0500" Content-Transfer-Encoding: 7bit Mac, Ok, I've got a problem with rollback(), I think. I'm not sure it's a database problem or if it's my code, but here's the output from my DBI script, and the code itself is attached (NOTE - I intentionally used the wrong column in my delete statement to force an error): AUTO_COMMIT is set to: AUTO_COMMIT is set to: create table t_dave_test as selec...

RE: Re: [wxperl-users] Re: Re: EVT_QUERY_END_SESSION
you putted the EVT_CLOSE to the wrong place... or do you have a reason why you need it to be there? $app ist not the window, but you could use EVT_CLOSE on $frame too. i changed your minimal sample that it works. hope it helps greeting Marco ---- use Wx; ########################### package MyApp; use strict; use vars qw(@ISA); @ISA=qw(Wx::App); sub OnInit { my( $this ) = @_; my( $frame ) = MyFrame->new( "Minimal wxPerl app", Wx::Point->new( 50, 50 ), Wx::Size->new( 450, 350 ) ); ...

Web resources about - Re: Re: Accessing MS Access through the DBI ODBC - perl.dbi.users

Highest Percentage Of Opera Mini Users Accessing Facebook? Macau
If you were asked to guess which country had the highest percentage of users of Opera mobile Web browser Opera Mini users accessing Facebook ...

International Users Accessing Facebook Places Through US VPN Accounts
By using a virtual private network (VPN) hosted in the United States, Facebook users from around the world are accessing Facebook Places. The ...

What are some alternatives to Yodlee for accessing bank information?
Clay Loveless , Founder, Jexy. Co-founder, Mashery. Founder, Jexy. Co-founder, Mashery.

Cloud Console - Accessing files in cloud storage for iPad on the iTunes App Store
Get Cloud Console - Accessing files in cloud storage on the App Store. See screenshots and ratings, and read customer reviews.

Meryl Streep On Accessing The Characters Within - YouTube
Meryl Streep talks about the importance of an actors work representing their ability. CONNECT WITH AFI: http://facebook.com/AmericanFilmInstitute ...

Student pleads guilty to accessing records about Frances Abbott design scholarship
The Sydney student who leaked information about a fashion school scholarship controversially awarded to the daughter of the Prime Minister has ...

Accessing a headline opinion
Accessing a headline opinion

Sharp increase in authorities accessing private data
Australian law enforcement and government agencies have sharply increased their access without warrant to vast quantities of private telephone ...

Former librarian charged with accessing student records of Frances Abbott
A former part-time librarian at a Sydney design school has been charged after she allegedly accessed student records of Prime Minister Tony Abbott's ...

Frances Abbott scholarship: Sydney woman pleads guilty to accessing Whitehouse Institute records on PM's ...
A Sydney woman who leaked the student records of Tony Abbott's daughter pleads guilty. A Sydney woman who leaked the student records of Prime ...

Resources last updated: 11/26/2015 8:33:25 AM