473,737 Members | 7,808 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 11622
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
5494
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 using Network Client and to register this alias in the usual way using my Enterprise Manager. My...
2
1771
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 "localhost" to the IP/port number of the new server, what else should be done, especially in the...
1
1811
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 the second user has to wait 9 seconds to open the first form. The third user has to wait 18 seconds,...
12
2788
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 servers, for both asp.net and sql server.
5
2381
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 entering my login and password. I get an access denied. Sql Server Error: 17.
5
2130
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
5727
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. Dim sConnString As String = _ "Provider=Microsoft.Jet.OLEDB.3.51;" & _
2
4123
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" providerName="System.Data.SqlClient" /> the strange part is that its trying to connect to a SQL express...
0
1546
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 connecting to MSDE and connecting to sql express 2005 ? here with my connection string.
1
2898
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
8968
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8786
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9470
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9334
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9207
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
4569
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3278
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
3
2192
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.