MS Access connectivity with php!

adnan1983

Hi everyone,

I was wondering if someone can clear some of my concepts. I am trying to connect my first database with php and mysql. I am going to interface it with MS Access. I am going to load all the scripts and the access *.mdb files on my web hosting space. Do I have to use mysql_connect and other mysql commands to connect to the MS Access, or odbc_connect and other odbc commands?? Can someone please let me know about it, and also if there is any good tutorial websites on it. Thanks.

Adnan.

adnan1983

Really need help with this, ANYBODY!!!!!

freebsd_dude

Hello do you still need help with this? I use Ms access 2000 with a dsn connection. Or known as odbc.

freebsd_dude

colbec

You can of course put your .mdb on the server and then access it as a normal back end using the correct path information using access as a front end. You can do the same thing with using access as a front end to link to mysql as a back end using ODBC.

However true server databases such as MySQL are a bit different in that they are designed to run on a server and actually have a daemon running on the server that listens on a port for connections to it. Access mdb does not do that.

So you can use PHP to access MySQL, but you can't use PHP to access an mdb cause it just isn't listening for that kind of access.

If you look through the PHP help file you will find functions to access MS SQL Server, but not the mdb of Access.

dkjohnson

First, using Access for a web database is not generally a great idea, particularly if you will have more than two or three people hitting it, or if the tables are large. However, there may be some reason you want to do it anyway, so here are a few things to think about:

1.) Windows server platform probably best for this attempt: It may be possible to use Access databases in a Linux environment, but I have not seen it done. I suspect it would be more complicated than the effort is worth. If you are working locally on a Windows box, cool. If you are using an ISP, I would check to see that their servers are Windows-based. (This has mostly to do with the way Access works; outside of Windows and the Access DLL's, mdb files are just files; you will have a lot of trouble using them elsewhere, I suspect.)

2.) Support for PHP Windows: Check to be sure that your ISP supports running PHP as an IIS CGI or module. If not, attempting to connect to an Access database will be much more difficult, I believe. I don't know that a lot of ISP's offer PHP on Windows IIS, but maybe yours does. If you're doing this on a local machine only, just be sure you've PHP installed and working correctly on Windows.

3.) Set up a DSN: Unless you have administrative privileges on your ISP you'll have to have them do it. Basically, they have to create a DSN (Data Source Name) which will be an ODBC link to your Access file. Get with the ISP to figure where and how they want your Access file uploaded, and then they can create the DSN for you. If you are working on a local Windows box, you can create the DSN yourself, referencing the local mdb file. The DSN will be an ODBC link to your local Access file.

4.) Use ODBC functions in PHP: You will have to use the generic ODBC functions for working with the Access DSN. PHP has no native support for Access.

Personally, if you're using PHP, I would not use Access. I don't say that because I hate Access; I have used it heavily over the years, and it has its place. However, Access is a Windows application intended primarily for local (not network) use. If I were you, I would download the MySQL Windows binaries (mySQL 4.0.7 rocks!) and install them on my Windows box, and use it instead. You can also download some good GUI tools like mySQL-Front, SQLYog for free. MySQL Studio and Mascon are two GUI products that are not free, but are very nice, and have fully function trial versions. These will give you some the same 'feel' while getting your feet wet. MySQL is used for nearly all PHP examples out there, and it's completely portable from Windows to Linux and back. Plus, it is a very powerful database server that will grow with your skills.

I think you will wind up being very frustrated trying to use Access for learning PHP, and if you are working with an ISP, you will probably go stark raving nuts.

If you have a lot of Access databases to convert, check out Access2MySQL from Intelligent Converters. It does a great job of making mySQL databases from existing Access files.

Hope this helps.

adnan1983

Hi,

I would love it if you could tell me how to do it. Cause I got to work with phpmyadmin, but still would like to know how to do it in MS Access.

my email is adnan1983@hotmail.com, if you could contact me on that and let me know about it. I really appreciate it. Thanks

Adnan.

adnan1983

Hi,

Thanks for your reply. I was really lost when I started off first using mysql. But I did get grasp of it now, it is really amazing. I have learnt to use phpmyadmin, and I think it is amazing. I don't wat else I could use instead of phpmyadmin.

Like I mean how do I build a database on mysql running on my computer, and then wat file do I upload on the remote server. That is one question of mine. Thanks again.

Adnan.

dkjohnson

PHPmyAdmin is a great web-based tool, and it's what most ISPs use for their users to work with mySQL. However, there are some things that are a little more difficult to do with PHPMyAdmin, for security reasons, mostly. One of those things is shipping entire tables and databases back and forth.

Sounds like you're probably using a virtual server on an ISP somewhere to post your web stuff, doing local development on your own (Windows?) box. If so, probably the easiest thing to do is work with PHPmyAdmin to create and manage the databases directly on the server. It is possible to transfer physical files to copy mySQL databases, but as far as I know, you can't do this with PHPmyAdmin, and most ISPs don't give you access to the physical filespace (with good reason).

If you want to develop locally, then move to the server with phpmyAdmin, you'll have to do it using queries in phpmyAdmin. You can write table creation and data insertion statements and run them in phpmyAdmin. If you have a lot of tables and/or data, this can get VERY tedious. Again, you might want to check out some of the GUI tools such as SQLyog (free) at download.com.com/3000-2210-10167632.html?tag=must or mySQL Studio (about $50, but free trial version) at http://www.mysqlstudio.com/detail.php3 . Both will let you design mySQL databases on your local machine using a nice interface similar to Access, and let you export and import data, including generating table definitions and data insert statements that you can cut and paste into phpmyAdmin.

As info, on your local mySQL, on a Windows box, mySQL data files are usually found in C:\mySQL\data. Each database will have a subdirectory with the same name as your database. The subdirectories contain three files for each table in your database. These files will have the same name as the table, with the extensions .frm, .MYI, and .MYD. You can copy these subdirectories and files between mySQL servers to move entire databases. You should stop the mySQL server before copying or writing files.

Hope this helps!

SynJean

If you are using MS Access you'll have to download, install, and configure the ODBC drivers for Windows. This is assuming your platform is Windows-based using IIS.