PHP and MS Access?

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

Moderator: General Moderators

Post Reply
firemyst
Forum Newbie
Posts: 6
Joined: Sat Feb 01, 2003 5:33 pm

PHP and MS Access?

Post by firemyst »

Is it possible to use PHP with Microsoft Access? I know PHP has built in APIs for MS SQL server. Do these also work for Access databases as well?

Thanks!
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

i think the odbc library should be able to help you. http://www.php.net/manual/en/ref.odbc.php though i have never used it someone else here might be able to lend a helping hand.
bionicdonkey
Forum Contributor
Posts: 132
Joined: Fri Jan 31, 2003 2:28 am
Location: Sydney, Australia
Contact:

Post by bionicdonkey »

from php manual:
2. Can I access Microsoft Access databases?

Yes. You already have all the tools you need if you are running entirely under Windows 9x/Me, or NT/2000, where you can use ODBC and Microsoft's ODBC drivers for Microsoft Access databases.

If you are running PHP on a Unix box and want to talk to MS Access on a Windows box you will need Unix ODBC drivers. OpenLink Software has Unix-based ODBC drivers that can do this. There is a free pilot program where you can download an evaluation copy that doesn't expire and prices start at $675 for the commercial supported version.

Another alternative is to use an SQL server that has Windows ODBC drivers and use that to store the data, which you can then access from Microsoft Access (using ODBC) and PHP (using the built in drivers), or to use an intermediary file format that Access and PHP both understand, such as flat files or dBase databases. On this point Tim Hayes from OpenLink software writes: Using another database as an intermediary is not a good idea, when you can
use ODBC from PHP straight to your database - i.e. with OpenLink's drivers. If
you do need to use an intermediary file format, OpenLink have now released
Virtuoso (a virtual database engine) for NT, Linux and other unix platforms.
Please visit our website for a free download.



One option that has proven successful is to use MySQL and its MyODBC drivers on Windows and synchronizing the databases. Steve Lawrence writes:



Install MySQL on your platform according to instructions with MySQL. Latest available from http://www.mysql.com (get it from your mirror!). No special configuration required except when you set up a database, and configure the user account, you should put % in the host field, or the host name of the Windows computer you wish to access MySQL with. Make a note of your server name, username, and password.

Download the MyODBC for Windows driver from the MySQL site. Latest release is myodbc-2_50_19-win95.zip (NT available too, as well as source code). Install it on your Windows machine. You can test the operation with the utilities included with this program.

Create a user or system dsn in your ODBC administrator, located in the control panel. Make up a dsn name, enter your hostname, user name, password, port, etc for you MySQL database configured in step 1.

Install Access with a full install, this makes sure you get the proper add-ins.. at the least you will need ODBC support and the linked table manager.

Now the fun part! Create a new access database. In the table window right click and select Link Tables, or under the file menu option, select Get External Data and then Link Tables. When the file browser box comes up, select files of type: ODBC. Select System dsn and the name of your dsn created in step 3. Select the table to link, press OK, and presto! You can now open the table and add/delete/edit data on your MySQL server! You can also build queries, import/export tables to MySQL, build forms and reports, etc.


Tips and Tricks:


You can construct your tables in Access and export them to MySQL, then link them back in. That makes table creation quick.

When creating tables in Access, you must have a primary key defined in order to have write access to the table in access. Make sure you create a primary key in MySQL before linking in access

If you change a table in MySQL, you have to re-link it in Access. Go to tools>add-ins>linked table manager, cruise to your ODBC DSN, and select the table to re-link from there. you can also move your dsn source around there, just hit the always prompt for new location checkbox before pressing OK.

firemyst
Forum Newbie
Posts: 6
Joined: Sat Feb 01, 2003 5:33 pm

PHP and MS Access

Post by firemyst »

Is it possible to use MS Access and PHP with a DNS-less connection? I'd rather not use ODBC drivers so we don't have to rely on sys-admins to set up DSNs.

thanks!
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

One caveat to all this - Access is really meant to be a desktop application.
If you are expecting much traffic, concurrent access to the DB - you are much better off using MySql as a database.

Phil J
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

I have also experienced oddities with the combination of PHP, Linux (Debian), UnixODBC, OpenLink (with requestbroker ODBC-ODBC) to MS-Jet or whatever the ODBC provider goes thru, with an Access-file on a Win-XP machine..

Exactly which link didnt work as supposed I don't know, but I found that using INSERT and UPDATE statements was very unreliable, no error or anything, appeared as if the query went fine but nothing was updated in the db... SELECTS worked most of the time, a few times it did not return all rows found.. This was with a 200MB access-2000 file, i updated mdac,jet and all datarelated stuff on the Bill Gates machine.. I did not try the iODBC manager instead of UnixODBC simply because debian doesnt offer that (would have to compile PHP etc), so I quickly convinced my client that it would most likely be easier to convert his access-front-end to use the data from a postgresql database instead of trying to find out why the php link wouldn't work, and ofcourse I blamed Billl Gates for it.. :)
E_Fox
Forum Newbie
Posts: 9
Joined: Sun Jan 12, 2003 11:33 pm

Post by E_Fox »

what the pros and cons of ms access over mysql?
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

Access
Pros:
Easy to set up, has nice built-in tools, query wizard, can set up stored
queries and something akin to views

Cons:
Really meant to be an application running on a desktop
Won't support many concurrent users
Cannot handle many connections
About $300.00

MySQL
Cons: Does not support some more advanced features such as stored procedures, views, subqueries; although I think the ability to use subqueries will be in the newest release. You have to get some 3rd party tool for a nice management interface to it.

Pros: Very fast, very robust, can handle many connections and large
processing loads. Free.
Post Reply