[SOLVED] Help with moving data from MS Access to another DB

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

TopCat
Forum Newbie
Posts: 7
Joined: Thu Oct 14, 2004 10:17 am

Help with moving data from MS Access to another DB

Post by TopCat »

Hi guys I’m in a bit of a sticky situation. I have to access information from an access database and insert fields into a specific places in a template in a website the database and the website are on the same machine. I have been advised to use PHP is there anyone who can help me

Thanks
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

err that requires some knowledge
-- Connecting to a database
-- retreiving information

do you have some code already made, if so post it here and describe a probleme you have encountered(using

Code: Select all

tags to make code readable)

Mhh if you didn't do anything yet there's couple of places to start
--[url=http://www.google.com]google[/url]
--[url=http://forums.devnetwork.net/search.php]Here[/url]

look in my signature the second one (I think) explain how to make a connection to database.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

HELP as a subject isn't very informative as far a question goes.

none the less a quickly google search provided.
http://aspn.activestate.com/ASPN/Cookbo ... ipe/123709
which starts to explain how to use php and access on windows.
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

Would also help if you told us which database you're meant to insert data into from MS Access.
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post by Draco_03 »

Mhh i didn't got the acces database part.
here some help

Code: Select all

//connecting database
$connect = odbc_connect("name", "user", "password") or die("Connection failed");

//getting info from table
$query = "SELECT * FROM table";
$queryexe = odbc_do($connect, $query);

while(odbc_fetch_row($queryexe)) 
    { 
    //do stuff
	}
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

I think you will be required to work with ActiveX Data Objects for this one.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

I had a little look at this problem throughout the net and came up with:

Code: Select all

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("database.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT * FROM table"
rs.Open sql, conn
%>

<%
for each i in rs.Fields
  response.write("<th>" & i.username & "</th>")
next
%>
That opens a db connection, performs a query and shows the username records. This uses ASP (recommended for access databases).
TopCat
Forum Newbie
Posts: 7
Joined: Thu Oct 14, 2004 10:17 am

Sorry

Post by TopCat »

Sorry for the extremely vague question and thank you all for putting up with me. I dont want the information to be inserted into another database, I would like certain fields (e.g. Report Number, Event, Date) to be inserted into there designated parts of a website template. I would like the user to be able to type in the report number and using PHP retrieve the information resulting in it being displayed in its place. I have a little knowledge of SQL and think that I am capable of querying the database, I would like to use an ADODB recordset.
How will the PHP code run? Will it be written into a button which when executed will retrieve the desired information?

Thank you for your help :D
TopCat
Forum Newbie
Posts: 7
Joined: Thu Oct 14, 2004 10:17 am

Where is the information being displayed?

Post by TopCat »

twigletmac | Help us, help you. Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

I got his code from http://aspn.activestate.com/ASPN/Cookbo ... ipe/123709. What I dont understand is commented as a question. If any of the other comments are wrong would you please let me know thanks.

Code: Select all

<?
// sets the connection type, this time its an ADODB object
$conn = new COM("ADODB.Connection") or die("Cannot start ADO"); 

// Microsoft Access connection string.
$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; 
// Whats DBQ?
DBQ=C:\\inetpub\\wwwroot\\php\\mydb.mdb");

// SQL statement to build recordset.
$rs = $conn->Execute("SELECT myfield FROM mytable");
// this is an echo, like printf? Where is it writting it to a msgbox a label i have no idea.
echo "<p>Below is a list of values in the MYDB.MDB database, MYABLE table, MYFIELD field.</p>";

// Display all the values in the records set
// Whats $ in the !rs, is this just PHP? 
while (!$rs->EOF) { 
// Whats $fv? and again where is this information being displayed????
    $fv = $rs->Fields("myfield");
    echo "Value: ".$fv->value."<br>\n";
    $rs->MoveNext();
} 
$rs->Close(); 
?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

The place to start with the code is here: http://www.php.net/tut.php

In answer to some of your questions:
  • [php_man]echo[/php_man] prints output to the browser.
  • The dollar sign ($) denotes a variable in PHP.
  • $fv is just the variable name chosen in this instance.
Mac
TopCat
Forum Newbie
Posts: 7
Joined: Thu Oct 14, 2004 10:17 am

Run before walk

Post by TopCat »

I understand that i have tried to do to much to soon, so i have taken your advice and am trying to work through the tutorials at

http://uk.php.net/manual/en/tutorial.firstpage.php

i have copied:

<html>
<head>
<title>PHP Test</title>
</head>
<body>
<?php echo '<p>Hello World</p>'; ?>
</body>
</html>

put it into notepad and saved it as hello.php
I then run it, the window opens and nothing happens.
Has anyone got any ideas????
TopCat
Forum Newbie
Posts: 7
Joined: Thu Oct 14, 2004 10:17 am

Post by TopCat »

I run this and it outputs fine

<html>
<head>
<title>PHP Test</title>
</head>
<body>
<?php echo '<p>Hello World</p>'; ?>
<h1>Hello World</h1>
</body>
</html>

in the tutorial it says:

When developing locally this URL will be something like http://localhost/hello.php or http://127.0.0.1/hello.php but this depends on the web server's configuration. If everything is configured correctly, this file will be parsed by PHP and the following output will be sent to your browser

could my computer not be configured? How do I check?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

If it is displaying the php code (everything including the <?php ?> tags) then the webserver isn't setup to parse PHP.

Mac
TopCat
Forum Newbie
Posts: 7
Joined: Thu Oct 14, 2004 10:17 am

misunderstood

Post by TopCat »

I have from the beginning intended to never have this PHP script on a server. I understand that thats stupid but I have to have the information formatted into a website page for ease of viewing can I have an interpreter/compiler which is on the standalone machine which will interpret the PHP? If so could you please point me in its direction?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

The script needs a webserver to run - you can install one like Apache or IIS and then install PHP to run through this. This can all be on a standalone machine.

Mac
Post Reply