mySQL + MS Access question

[CrazyFunkyTown]

Hello

I have started looking into PHP and mySQL. My company has a bunch of Access databases that it uses for all sorts of stuff. I would like to create a website based on these databases. mySQL and PHP seem like good choices. I am just having one difficulty. I would like to link the tables that exist in Access into my mySQL database. Is this possible? I know that I can link mySQL tables into Access and export/import both ways but is there a way for me to keep my present tables and serve them with mySQL? Thanks for any help or advice you can offer.

 

[AFB]

http://www.convert-in.com/acc2sql.htm

I think the only way is to export them. I may be wrong.

 

[drag]

I don't realy know about Databases much, but Access is accessable thru a thing called the ODBC plugin, which I am geussing is a standardized way of moving data from one database to another.

Probably the only decent advice I can give is to make good backups and do some extensive testing to make sure that you know what your going into and what to expect and so on and so forth. Like the boyscouts say "Always be prepared".

Do a search on google for migrate from access to mysql.

I came up with this seemingly fine http://www.kitebird.com/articles/access-migrate.html

I am also pretty sure that there are quite a few applications and whatnot that you can pay for to make the trasisition as painless as possible.

And don't forget about postgresql, which is another free SQL server. Mysql was originally intended to be as a easy to use SQL database as possible, which means that it's popularity is high because of all the websites and stuff that uses it (which also means that you can get help easily.), but postgresql is a more "correct" sql database with more standardised features.

But that's about the limit of what I know, so take it with a grain of salt.

 

[notfred]

Can you use ODBC from PHP? I've used it from perl to connect to an Access DB.

 

[AFB]

Originally posted by: notfred
Can you use ODBC from PHP? I've used it from perl to connect to an Access DB.

Yes
http://www.php.net/manual/en/ref.uodbc.php

 

[CrazyFunkyTown]

Thanks for the replies. I do not want to migrate from Access, that would be near impossible. There are thousands of lines of code behind our Access databases, they are full fledged applications. What I am looking for is a way to connect to these databases from a PHP script, ideally in a way that allows them to be viewed over the internet.

 

[drag]

That sucks. Sounds like you realy painted yourself into a corner with this MS-Access stuff.

But if you just want to display stuff it shouldn't be to difficult since the information is going to be read only. It's going to have to go thru that ODBC stuff.

PHP has bindings so that it can communicate directly with the ODBC interface that Access has. here is a example in action

Depending on how often you need to have the webpage display the changes to your Access database, you could simply use one of those conversion utilities to dump the parts from your access database to a Mysql one on a the server and build the webpage from that.

Or you could possibly stick with a pure MS solution and pull information from your databases using ASP. like is described in this article Even then if you want to still use a Apache web server or whatnot you can do weird stuff like Apache::APS mod, although that seems to get pretty convoluted...

here is another article about using the ODBC support in PHP to pull information from any database that supports it (which Access does)


You would do the "Access Database --> Mysql (on the server or whatnot) ---> scripts and stuff ---> webserver" thing with the Connector/ODBC with Microsoft Access So you could setup Access to interact with a Mysql database and then it would be (relatively) easy to make that interact with a web server based application. I suppose you wouldn't have to do a entire port of the entire database and all their apps to work with MySQL, just the stuff you want to be displayed/interacted with online. Probably perform better that way, too.


(keep in mind that I don't know a whole lot about databases, just little bits here and there. I am just brainstorming.)

 

[CrazyFunkyTown]

Thanks all for your posts. The thing that I most want to do is have an internet site that can access certain databases residing on a local intranet server PC. The access that I have to these databases is on a shared network drive. For my situation I need a way to "serve" this database to the internet. Simply accessing the Access database using ODBC would be insufficent. MySQL does not seem to be willing to simply serve existing Access tables.

My solution, which I am mostly happy with, is the use a utility I found called ODBC Socket Server. This serves an ODBC connection over TCP/IP. I can then connect with this from PHP. I guess the main problem with this is the means of getting to the data. The server will accept SQL strings only. This means that all data accessing, modifying, deleting etc. must be done using SQL. This might be tricky so I will write a "wrapper" class that acts more like a VB recordset. The other problem is that the records returned from the SQL queries are formatted in XML. I need to parse this XML to get any use out of the data. These are certainly challenges, and I am getting a little closer to the data than I would have preferred.

Looks like I will be able to accomplish what it is that I want though. Thanks again, let me know if anyone has any other alternatives.

 

[Alternex]

What was your reason for using PHP vs ASP/ASP.NET? Displaying access data from ASP is pretty easy

 

[everman]

You can use access as a front end to a SQL database. But you have used access to create entire webapps? Probably a nightmare. That just makes me cringe

 

[kamper]

Originally posted by: CrazyFunkyTown
Thanks all for your posts. The thing that I most want to do is have an internet site that can access certain databases residing on a local intranet server PC. The access that I have to these databases is on a shared network drive. For my situation I need a way to "serve" this database to the internet. Simply accessing the Access database using ODBC would be insufficent. MySQL does not seem to be willing to simply serve existing Access tables.

My solution, which I am mostly happy with, is the use a utility I found called ODBC Socket Server. This serves an ODBC connection over TCP/IP. I can then connect with this from PHP. I guess the main problem with this is the means of getting to the data. The server will accept SQL strings only. This means that all data accessing, modifying, deleting etc. must be done using SQL. This might be tricky so I will write a "wrapper" class that acts more like a VB recordset. The other problem is that the records returned from the SQL queries are formatted in XML. I need to parse this XML to get any use out of the data. These are certainly challenges, and I am getting a little closer to the data than I would have preferred.

Looks like I will be able to accomplish what it is that I want though. Thanks again, let me know if anyone has any other alternatives.

Why is it exactly that you can't use the odbc library in php? It seems to me that if you must use two different database servers and that they won't talk to each other that the easiest thing to do would just be to query the two seperately. Maybe build a data access layer in php that concentrates the knowledge of which data exists in which database. The only thing you'd be losing is referential integrity for references crossing the line between the two databases (if there are any).

How much traffic are you expecting on this site? I don't know much about Access but I wouldn't think it would really stand up to multiple users that well.

drag: nice research. I think the majority of your posts turn out to be very helpful for more than just the op

 

[drag]

Originally posted by: everman
You can use access as a front end to a SQL database. But you have used access to create entire webapps? Probably a nightmare. That just makes me cringe

I would think that you would use PHP or Java or Perl CGI pages to build dynamic webpages on apache to interact with the Mysql database.

With Access you would build queries and export them along with tables to the mysql database on the webserver to be manipulated and then the next time you need to look at the data you import them back into Access. All thru the ODBC database stuff.

The reason I thought that this may be a good solution is because Access realy sucks at this sort of thing, it's design is more for small business record keeping and the scope of it is rather limited. MySQL would be much easier for the average webmaster to interact with, then they can use whatever language they are most comfortable with.

But if it's just a simple intranet web page I think the easiest course of action would be to use simple IIS server along with ASP to build the pages, because security and design is much less of a concern then something that the public or remote users would have to interact with over the internet.

I don't know tremendous much about this sort of thing, but I like what my company does were they have a Apache server with CGI dynamic webpages that interact with the MSSQL server in the back room. That SQL database is used to form queries with the real database which is kept inside a IBM mainframe setup. The the results are FTP'd or mailed by CDROM to the customers that use the information from the queries they setup on the webpage... along with the bill of course.

It all depends on how scalable you want it and any future plans you may use your databases for as your company grows. That sort of thing.