home » What to wear with » PHP and remote Access. Using MS Access in PHP

PHP and remote Access. Using MS Access in PHP

The popularity of the Microsoft Access DBMS (http://www.microsoft.com/office/access) is partly explained by its convenient graphical interface. In addition to using Access as a standalone DBMS, you can use its graphical interface to organize work with other databases - for example, MySQL or Microsoft SQL Server.

To demonstrate ODBC support in PHP, I'll describe the process of connecting to Microsoft Access databases in PHP. It's surprisingly easy to do, but thanks to the popularity of Microsoft Access, it's a useful addition to your creative arsenal. I will describe this process step by step:

Create an Access database. It is assumed that you already know how to do this, and if you do not know how, but still want to follow the implementation of this example, use the services of the wizard program. I created a standard database to store contact information using a wizard. Be sure to create multiple records in the table and remember the table name, we'll need it soon!
Save the database on your computer.
In the next step, we will organize access to the Access database using ODBC. Select Start > Settings > Control Panel. Find the ODBC Data Sources(32 bit) icon on the control panel. This icon launches the ODBC Administrator, which is designed to configure various drivers and data sources on your system. Launch the program by double-clicking on this icon. The Administrator window opens on the User DSN tab by default. This tab lists data sources that are specific to a specific user and can only be used on that computer. In this example, this is the data source that will be used.
Click the Add... button on the right side of the window. A new window opens to select a driver designed to work with the new source. Select the Microsoft Access Driver(*.mdb) line and click Finish.
A new window appears on the screen: Installing the ODBC Driver for Microsoft Access (ODBC Microsoft Access Setup). Find the Data Source Name text box in the form and enter the name of the Access database you created in it. If you wish, you can enter a description in the text box located directly below the Data Source Name field.
Click the Select... button - a window in the style of Windows Explorer appears. It asks you to find a database that will be accessed using ODBC.
Find your database in the directory tree and double-click on it. You are returned to the Install ODBC Driver for Microsoft Access window. The path to the selected database is displayed above the Select... button. Click OK.
Ready! You can now work with an Access database using ODBC.

All you have to do is create a script in which you will work with the database via ODBC. In the below scenario, the common ODBC functions (see above) will be used to output all the information from the contacts table created using the Access wizard. However, before considering the scenario, it is useful to know what the Contacts table looks like in Access (Figure 11.3).

Rice. 11.3. Contacts table in MS Access

Now you know what information will be retrieved from the database, we can move on to the script. If you have forgotten what a function does, refer to the description at the beginning of this chapter. The results of Listing 11.7 are shown in Figure. 11.4.

Listing 11.7. Using ODBC functions to work with MS Access

Isn't it all simple? And the best thing is that this script is fully compatible with any other DBMS that supports ODBC. To practice, try repeating all the described steps for another DBMS, run the script - and you will get the same results shown in Fig. 11.4.

To demonstrate ODBC support in PHP, I will describe the process of connecting to Microsoft Access databases in PHP. It's surprisingly easy to do, but thanks to the popularity of Microsoft Access, it's a useful addition to your creative arsenal. I will describe this process step by step:

Create an Access database. It is assumed that you already know how to do this, and if you do not know how, but still want to follow the implementation of this example, use the services of the wizard program. I created a standard database to store contact information using a wizard. Be sure to create multiple records in the table and remember the table name, we'll need it soon!
Save the database on your computer.
In the next step, we will organize access to the Access database using ODBC. Select Start > Settings > Control Panel (Start > Settings > Control Panel). Locate the ODBC Data Sources (32 bit) icon in Control Panel. This icon launches the ODBC Administrator, which is designed to configure various drivers and data sources on your system. Launch the program by double-clicking on this icon. The Administrator window opens on the User DSN tab by default. This tab lists data sources that are specific to a specific user and can only be used on that computer. In this example, this is the data source that will be used.
Click the Add... button on the right side of the window. A new window opens to select a driver designed to work with the new source. Select the Microsoft Access Driver (*.mdb) line and click Finish.
A new window appears on the screen: Installing the ODBC Driver for Microsoft Access (ODBC Microsoft Access Setup). Find the Data Source Name text box in the form and enter the name of the Access database you created. If you wish, you can enter a description in the text box located directly below the Data Source Name field.
Click the Select... button - a Windows Explorer-style window appears. It asks you to find a database that will be accessed using ODBC.
Find your database in the directory tree and double-click on it. You are returned to the Install ODBC Driver for Microsoft Access window. The path to the selected database is displayed above the Select... button. Click OK.
Ready! You can now work with an Access database using ODBC.

All you have to do is create a script in which you will work with the database via ODBC. In the following scenario, the common ODBC functions (above) will be used to display all the information from a contacts table created using the Access wizard. However, before considering the scenario, it is useful to know what the Contacts table looks like in Access (Figure 11.3).

Rice. 11.3.Contacts table in MS Access

Listing 11.7.Using ODBC functions to work with MS Access

// Connect to the ODBC data source "ContactDB" ;connect = odbc_connect("ContactDB", "","")

or die("Couldn't connect to datasource.");

// Create request text

$query = "SELECT First_Name, Last_Name, Cell_Phone, Email FROM Contacts";

// Prepare the request

$result = odbc_prepare($connect,$query);

// Execute the query and display the results

odbc_execute($result);

odbc_result_all($result, "BGCOLOR="#c0c0c0" border=1");

// Processing of results is complete, free memory odbc_free_result($result);

// Close the connection odbc_close($connect);

Isn't it all simple? The best thing is that this script is fully compatible with any other DBMS that supports ODBC. To practice, try repeating all the described steps for another DBMS, run the script - and you will get the same results shown in Fig. 11.4.

Rice. 11.4.Contents of the Contacts table in a web browser

I also have a problem with a remote Microsoft Access database. Only my database has been removed quite a bit. It is located only on the local network, on another computer.

The odbc_connect PHP function gives me the following error:
Warning: odbc_connect() : SQL error: [ODBC Microsoft Access Driver] Application cannot be started. The system database is missing or is open with exclusive access by another user.

This occurs when in my Windows, on my local computer, in the ODBC driver, in the properties of the “Data Source”, the “System DSN” tab, I indicate that the “System Database” (workgroup file - system.mdw) is located on a network drive. As soon as I transfer the same file to the local disk of my computer (where I sit myself), then everything starts working fine. I can't figure out what's wrong. Tell me, please! For some reason, the ODBC driver stubbornly does not want to see the workgroups file on a shared network drive. And if the file sits locally, then everything works with a bang. What can be wrong? By the way, the same thing happens with the base itself. In general, for odbc_connect to work normally, it turns out that both the “Workgroup File” and the database itself must be located on the local computer. But I can't do that. The base should remain where it is. And I have to reach her remotely via ODBC and post a little information from the database on the Internet on the site page. I can't understand why it doesn't work. In the ODBC settings, you can normally specify all paths to remote database files. But after that it doesn't work. On the local computer everything works fine.

On your local computer:
OS WinXP-SP2
Microsoft Access 97
PHP v5.2.3
ODBC v3.5
Appserver v2.5.5

On a remote computer, there is a “two thousandth” server.
I thought that maybe there was something wrong with the access and the security policy. But I transferred the databases to a regular computer, under the Win 98 operating system and to a hard drive under FAT32. It doesn't plow there either. I’m already exhausted, I don’t understand what’s wrong. One gets the impression that ODBS cannot work with remote databases at all. But this is not so. It should work anyway. Help, please!

-~{}~ 17.01.08 10:30:

That's it, problem solved! Hurray comrades!

Still, I found a text on my question:
http://www.softtime.ru/forum/read.php?id_forum=1&id_theme=251&page=560
There's the bottom post. For those who are too lazy to dig in there, here is the verbatim copyright of that post:
"As I understand it, we are working under Windows. Press Ctrl+Alt+Del, select the task manager and look at the Apache process. And we see that it is running on behalf of SYSTEM, and not as yours. So we can’t access network resources. So We can cure this by launching the process under the required name."

I launched Apache as a user and everything began to work with a bang.

True, there was only one ambush left. The ODBC connection to the database must be written manually in program code in this way:
Odbc_Connect("DRIVER=(Microsoft Access Driver (*.mdb)); DBQ=//server/work/baza/mybaza.mdb; systemdb=//server/work/baza/system.mda", "username", "password") or Die("I can't connect to the database");

If you do the same thing through the Windows “ODBC Administrator”, then it still won’t work. The administrator makes his own paths through the names of the drives. That is, in this way:
"Z:\work\baza"
but I need the path to be a network path, without a drive letter, and with backslashes:
"//server/work/baza/"

Here is another example explaining this new ambush.
We have a network drive “Z:\work\baza” connected to the computer. This is a complete analogue of the network path "//server/work/baza/"
We write the simplest program code in PHP to open a remote directory. Without any ODBC. So that you can simply check whether it works or not:
OpenDir("Z:\work\baza"); //gives an error message
OpenDir("//server/work/baza/"); //no error

Here's my new question. What am I doing wrong? In any case, it won’t be possible to use Windows’ “ODBC Administrator” to connect network resources, or is it still possible somehow? I repeat, the ambush now remains only with the correct spelling of paths to network resources. Maybe some separate setting of the PHP interface itself is responsible for this moment?

If you are planning to create your own dynamic website on the Windows platform, then you will most likely have the task of choosing a DBMS for storing information (apparently, there are no questions with the language in which you will program it). One of the options for solving this problem may be to use MS Access as a SQL server. Next we will describe how to access an MS Access database in a script written in PHP.

Unfortunately, PHP does not have built-in tools for working with this DBMS, which, however, does not exclude them from working together - we will use ODBC to connect to the database.

First of all, we must create the so-called DSN Source. To do this (we consider the option when the user has Windows 2000 Rus installed), in the control panel we must select Administration, and then Data Sources (ODBC). Next, in the window that appears, select the system DSN tab, click the add button, select the MS Access driver and click the done button. In the data source name input field, write the name by which we can later access our database, for example, test, then click the select button and indicate where our database file is located on our disk. Then, if necessary, we can set a username and password to access the database via ODBC by clicking on the additional button. Now that we have an ODBC data source, we can use the odbc_connect function to connect to our database:

$x=odbc_connect("test","test","test");
?>

To pass the request to the DBMS, we can use the odbc_exec function:

$res=odbc_exec($x,"create table test (f1 integer, f2 varchar(10))");
$res=odbc_exec($x,"insert into test (f1,f2) values(1,"qwerty")");
$res=odbc_exec($x,"insert into test (f1,f2) values(2,"asdfgh")");
?>

If, after running this example, we open the test database in MS Access, we will find that a new table test has appeared there with fields f1 and f2 of integer and string types, respectively; the table will have two records with the data that was specified in the request.

Unfortunately, not all functions for working with ODBC work correctly with MS Access, for example, the odbc_num_rows and odbc_fetch_array functions. So, in the version of PHP that is installed on my computer, PHP generally displays a message that the odbc_fetch_array function is not known to it. But these problems can be solved by describing the following two functions and using them instead of the previously mentioned ones:

$res=odbc_exec($x,"select * from test");
$cnt=xodbc_num_rows($res);
?>

for ($i=0;$i<$cnt;$i++) {
$row=xodbc_fetch_array($res,$i+1);
echo " ";
}
?>

f1	f2
".$row["f1"]."	".$row["f2"]."

Views