473,545 Members | 721 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Connecting to an MS Access database.

7 New Member
I am working on a class assignment where I have to connect to an MS Access database. The perl script is contained in a separate file *.pl

I went into Control panel/ODBC/ and selected Microsoft Access driver and identified the source name.

I was looking at a previous post here and many things appear to be similar to my requirements.

1. Add dbi inclusion. (pretty straight forward, like an “include” in C or “import” in Java
Expand|Select|Wrap|Line Numbers
  1. use DBI;
  2. $::db_host = 'localhost';
  3. $::db_name = 'KTC.mdb';
  4. $::db_user = 'root';
  5.  
My database is titled KTC.mdb and is located in the apache2\databas e directory
I’m not sure what the db_user=’root’ statement does?

2.Add database connection hardcoding the user name and password. (use the DBI connect call and store the handle)
This is somewhat confusing because there are no passwords on our database. I am also using access not mysql. DBI:mysql:host= ??
Expand|Select|Wrap|Line Numbers
  1. # $::db = DBI->connect(
  2. # "DBI:mysql:host=$::db_host;database=$::KTC.mdb",
  3. # $::db_user) || die $DBI::errstr;
  4.  
3.Add a query to select the product name, description, units in stock, and price. (use products table and only select one product, where “ProductID = $productID”)
Expand|Select|Wrap|Line Numbers
  1. my $stm= $::db->prepare("SELECT ProductName, ProductDescription, UnitsInStock, UnitPrice FROM Products WHERE ProductID = $productID ORDER BY ProductID");
  2. $stm->execute || die $::db->errstr;
  3. my $LST = $stm->fetchall_hashref('ProductID');
  4.  
4. Add statement handle definition and execution.
5. Code the loop by fetching an array from the statement handle. (use “fetchrow_array ()”, will return records from database into an array “@product”, there are also other ways to do this, could return a hash)
6. Display the records. (students often get confused with this. You need to access the indices of the array to display in the table. Each index corresponds to the column name in the select statement of your query. The syntax to grab an index of an array for this project in perl is (“$product[x]” where x is a number pertaining to the column name, will map to product information)

I haven’t even looked at these requirements yet.

7. Disconnect database.
Expand|Select|Wrap|Line Numbers
  1. $rc = $dbh->disconnect();
  2.  
Do I need more than this?
Oct 27 '06 #1
2 11595
vssp
268 Contributor
=************** *************** *********
= Name: Create Table/MS Access/ADO
= Description:Thi s is a simple script th
= at will create a
new table in a Access Database. This is to show
how to connect to an Access Database thru Perl
by using DBI and the ADO driver. You can also
connect by using ODBC, by replacing where you
ADO. dbi:ADO:$DSN
= By: Randy McCleary
=
=This code is copyrighted and has = limited warranties.Plea se see http://w
= ww.Planet-Source-Code.com/vb/scripts/Sho
= wCode.asp?txtCo deId=457&lngWId =6 =for details. =************** *************** *********

#!/usr/bin/perl -w
############### ############### ############### ######
## This is a simple script that will create a
## new table in a Access Database. This is to show
## how to connect to an Access Database thru Perl
## by using DBI and the ADO driver. You can also
## connect by using ODBC, by replacing where you
## ADO. dbi:ADO:$DSN
############### ############### ############### ######
use POSIX;
use strict;
use CGI qw(:standard);
use CGI::Carp qw(fatalsToBrow ser);
use DBI;
use Time::Local
print header;

############### ############### ########
## Set DSN-Less Connection
############### ############### ########
my $DSN = 'driver=Microso ft Access Driver (*.mdb);dbq=C:\ WINDOWS\perl.md b';
my $dbh = DBI->connect("dbi:A DO:$DSN", '','')
or die "$DBI::errstr\n ";

############### ############### ########
## Generate the SQL Statement
############### ############### ########
my $sql = <<"EndOfSQL";
CREATE TABLE tblContacts2 (
ID COUNTER,
LastName CHAR(40),
FirstName CHAR(40),
MiddleName CHAR(20);
HomePhone CHAR(40),
WorkPhone CHAR(40),
CellPhone CHAR(40);
BirthDay CHAR(20);
Fax CHAR(40),
Email CHAR(40),
Address1 CHAR(40),
Address2 CHAR(40),
City CHAR(30),
State CHAR(10),
ZipCode CHAR(20),
CONSTRAINT ID_PK PRIMARY KEY(ID)
)
EndOfSQL


############### ############### ##
## Execute the SQL Statement
############### ############### ##
$dbh->do($sql)
or die "Execution problem: $DBI::errstr";
print "Table was Created";

############### ############### ########
## Close the connection when finished:
############### ############### ########
$dbh->disconnect;



I hpe this helpfull for u

vssp
Oct 30 '06 #2
gmccammon
7 New Member
Thanks, I finally got it to connect, I just have a data fiormatting problem now.
Oct 31 '06 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
5483
by: John Morgan | last post by:
I have Enterprise Manager on my local machine. For the last twelve months it has been connecting without problem to my online SQL Server database provided by my ISP. Three weeks ago the ISP applied some sort of extra security arrangements to their SQL Server to allow access only through port 1433. they have told me to configure an alias...
2
1762
by: news | last post by:
We currently have our mySQL server on the same box as the Apache server. For security and load balancing, we're going to be moving the mySQL server to another box. We're already using a single included connection file in all of our PHP pages that has the server, username, password line that connects to the database. Aside from changing...
1
1800
by: Tim Dol | last post by:
I have inherited a VB6-SP5 program connecting to an Access 2000 database. I'm relatively new to VB/Access programming, so I may overlook something obvious. I'll try to explain the problem I can't solve. My VB executable and Access database are both stored in a shared folder on a Novell volume. The first user has a good performance, but...
12
2757
by: Ann Marinas | last post by:
Hi all, I would like to ask for some help regarding separating the asp.net webserver and the sql server. I have created an asp.net application for a certain company. Initially, we installed both the iis and sql server in a single machine. Not too long ago, the machine had some hardware problems, and management has decided to purchase new...
5
2372
by: bill | last post by:
I am having difficulties connecting to a remote Sql server database in VB.net. Lets say my remote server is found at 255.255.255.255 and its name is MyServer. The database is called MyDatabase. I drap my SQLDataAdapter on to the form and set up a new connection at the wizard. I am putting the IP address of my server under server name and...
5
2117
by: Sebastian | last post by:
I'm using the following code to connect to an access 2000 database. (with reference to adodb) Public DBvar As New ADODB.Connection() DBvar.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Data.mdb", "userID", "Pass") Previously I was connecting with just DBvar.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data
5
5721
by: Al | last post by:
Hi, I am looking for sample VB.net code for working with Access 97 database. I know how to work with SQL but I dont know how to read and writ into access 97. Any help is very much appreciated. There was suggestion that connecting to an MS Access database through VB.NET is exactly same as SQL and I only need to change the provider name. ...
2
4109
by: Patrick F | last post by:
Hi, i have SQL Server 2005 and a database set that is called, myCompany the problem is that i cant connect from my page to it, here is from the web.config: ( i have got this connectionstring from my sqldataobject on my page) <add name="ConStr" connectionString="Data Source=localhost;Initial Catalog=myCompany;Integrated Security=True"...
0
1530
by: =?Utf-8?B?R2lkaQ==?= | last post by:
Hi, I've my win32 application which connecting to my sql database. till now i used MSDE as my database and now i want to upgrade to sql express 2005. after the upgrade i tried to connect to the database and couldn't. i keep getting an error which saying that database doesn't exists or access is denied. is there something diffrent between...
1
2884
by: Yew12 | last post by:
Hi, I'm having problems connecting to a Microsoft Access database. Im using PHP and ODBC but im not using a DNS. The scripts below give no errors but I they only give me a blank white screen. Any would be greatly appresiated. Please help. Script 1 <HTML> <HEAD>
0
7465
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7416
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7752
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5969
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5325
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3449
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3441
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1878
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.