471,862 Members | 1,683 Online

Connection to MS access

Hi

I am a newb to perl, n m tryin to get myself familiar with the syntax n stuff. I need to connect to a database in microsoft access. I 've optiperl n activestat perl installed on my machine.. can anyone plz guide me on how to connect to access through a perl program. Also do i need to install win32::odbc or dbi drivers to connect to MS access?

Aug 5 '06 #1
9 24935
HI,

Using DBI you can connect the database.

1. first you create user dsn and connect your access database.
3. Use the below code and connece the access databse

Expand|Select|Wrap|Line Numbers
1. use DBI;
2. use strict;
3.
4.  my $dbh = DBI->connect('DBI:ODBC:test','','') or die "Cannot connect:$DBI::errstr\n";
5.
6.  my $sth1 =$dbh->prepare('Select * from test');
7.
regards
dhascruz
Aug 7 '06 #2
Hi

I am a newb to perl, n m tryin to get myself familiar with the syntax n stuff. I need to connect to a database in microsoft access. I 've optiperl n activestat perl installed on my machine.. can anyone plz guide me on how to connect to access through a perl program. Also do i need to install win32::odbc or dbi drivers to connect to MS access?

Hello,

Here an example to connect to MS Access with Perl:

Expand|Select|Wrap|Line Numbers
1.     use DBI;
2.     use STRICT;
3.
4.         $sqlstatement=$txt1->get('0.0','end');
5.
6.     #open connection to Access database
7.     $dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=C:\dump9\Noordenwind.mdb'); 8. 9. #prepare and execute SQL statement 10. 11.$sth = $dbh->prepare($sqlstatement);
12.     $sth->execute || 13. die "Could not execute SQL statement ... maybe invalid?"; 14. 15. 16. #output database results 17. 18. while (@row=$sth->fetchrow_array()){
19.     print "@row\n";
20.        }
21.
Have fun.

Ocean999
Aug 8 '06 #3
Hi

I am a newb to perl, n m tryin to get myself familiar with the syntax n stuff. I need to connect to a database in microsoft access. I 've optiperl n activestat perl installed on my machine.. can anyone plz guide me on how to connect to access through a perl program. Also do i need to install win32::odbc or dbi drivers to connect to MS access?

Dear,

Have you finished this subject?
I am working on this program now.
I have spent the whole week to do it. But I can not find a good way.
Now I am trying to mount the remote directory.
Or, put the mdb file on the web site, and download it on local direcotry.
Obviously, both of them are not good choices.

Thanks a lot!
I am look forward to hearing you.

Best Regards,
Xu
Sep 23 '07 #4
HI,

Using DBI you can connect the database.

1. first you create user dsn and connect your access database.
3. Use the below code and connece the access databse

use DBI;
use strict;

my $dbh = DBI->connect('DBI:ODBC:test','','') or die "Cannot connect:$DBI::errstr\n";

my $sth1 =$dbh->prepare('Select * from test');

regards
dhascruz
I think this is only for local mdb file.
I have tried to create a data source, but Host, server, serverport doesn't work.
Maybe not, it says: Can't alloc filename
Say: My mdb file is on D:\mdb\B2000.mdb from the remote machine.
And I use: Host = server ip
database = d:\mdb\B2000.mdb
Error:Unable to locate database d:\mdb\B2000.mdb
If I made database = http://serverip/mdb/B200.mdb ( I set the virtual directory named mdb from IIS)
Error:Unable to locate database http://128.128.108.110/mdb/B2000.mdb
Sep 23 '07 #5
Hello,

Here an example to connect to MS Access with Perl:

Expand|Select|Wrap|Line Numbers
1.     use DBI;
2.     use STRICT;
3.
4.         $sqlstatement=$txt1->get('0.0','end');
5.
6.     #open connection to Access database
7.     $dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=C:\dump9\Noordenwind.mdb'); 8. 9. #prepare and execute SQL statement 10. 11.$sth = $dbh->prepare($sqlstatement);
12.     $sth->execute || 13. die "Could not execute SQL statement ... maybe invalid?"; 14. 15. 16. #output database results 17. 18. while (@row=$sth->fetchrow_array()){
19.     print "@row\n";
20.        }
21.
Have fun.

Ocean999
This is a good example. But how to set for a remote access database?
Do you have any suggestion?

Thanks very very much!
Sep 23 '07 #6
numberwhun
3,503 Expert Mod 2GB
Dear,

Have you finished this subject?
I am working on this program now.
I have spent the whole week to do it. But I can not find a good way.
Now I am trying to mount the remote directory.
Or, put the mdb file on the web site, and download it on local direcotry.
Obviously, both of them are not good choices.

Thanks a lot!
I am look forward to hearing you.

Best Regards,
Xu

?
talking
are
you
what
know
anyone
will
how

Regards,

Jeff
Sep 23 '07 #7
4,059 Expert 2GB
This is a good example. But how to set for a remote access database?
Do you have any suggestion?

Thanks very very much!
This is a very old thread, the people that posted before you are long gone. The code posted by ocean999 is not valid perl code and will not run. It may have some insight into your question. Even though you are using perl as the interface to the database, your question about accessing a remote access database might be best asked in the access forum. Personally I don't know the answer to your question so I will be of no further help.
Sep 23 '07 #8
Thanks a lot, everyone!
Thanks again...
Sep 24 '07 #9
Modification to ocean999's posting to get it work for me:
Expand|Select|Wrap|Line Numbers
1. #!/usr/bin/perl
3.
4. use DBI;
5. use STRICT;
6.
7. $sqlstatement= "select * from tblName;"; 8. 9. #open connection to Access database 10.$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);'.
11.       'dbq=C:\path\to\file.mde');
12. # Note: this worked for me on a .mde file, I assume .mdb files work, too.
13.
14. #prepare and execute SQL statement
15.
16. $sth =$dbh->prepare($sqlstatement); 17.$sth->execute ||
18.       die "Could not execute SQL statement ... maybe invalid?";
19.
20.
21. #output database results
22.
23. while (@row=\$sth->fetchrow_array())  {
24.    print "@row\n";
25. }
26.
27.
28.
29.
Hi

I am a newb to perl, n m tryin to get myself familiar with the syntax n stuff. I need to connect to a database in microsoft access. I 've optiperl n activestat perl installed on my machine.. can anyone plz guide me on how to connect to access through a perl program. Also do i need to install win32::odbc or dbi drivers to connect to MS access?