[PHP] Re: newbie looking to sync access with MySQL
Tom, Every man and his dog seems to have an Access database they want to view on the web. What I usually do for them is : a) Tell them to save the data from Access as a text file, comma separated. (It's a standard Access option). b) Give them a web page where they can upload it, (INPUT TYPE=FILE etc., it's discussed in the php manual). c) Parse the file using parsecsv and store away the bits you want in MySQL. If you get them to leave the field names on the first line of the file (another standard Access option), you can do a quick check that all the fields are there and in the expected place, before you put the data into MySQL. d) Serve the web pages from MySQL. e) You may still need your Web Data Administration. There's often missing data that you need for sensible web pages e.g. categories, more user friendly descriptions of codes etc. From the customer's point of view the uploads are a manual exercise which doesn't sound very sexy, and it would probably take them 3-4 minutes every time. The good thing is that it's all pretty easy, and they should be able to get their most junior member of staff to do it. They also get the comfort of knowing if anything went wrong. If that's not acceptable, it's more tricky. In principle you could use an ODBC interface to their Access database to establish a live connection to it and suck up the data that way, but it's fiddly and much less reliable than a boring old upload. Another option would be to execute the extract and transfer of the data via a batch job running on their platform. But, personally, I would hate to have to support that kind of activity. The bottom line as far as I'm concerned is that if they insist on holding important information on a MS platform, they can expect a) not to have a seamless operation, b) to have to watch out for problems themselves! Once they see how easy an extract/upload is they probably won't mind anyway. Tom Beidler wrote: I have a potential customer that just called and would like to create a web site that would display secure info for their clients. Currently his employees are using a flat Access database to add, edit and delete records and he would like to keep it that way. Normally I would create a web manager for them to add, edit and delete the MySQL database. Is there an easy, reliable way to sync up Access with MySQL. Maybe a nightly script that could upload the contents of Access to MySQL. Would it be easier to start with ASP and SQL Server? Thanks, Tom -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Re: newbie looking to sync access with MySQL
Thanks for that helpful email george, it's something I've been looking at as well. In a situation I'm tackling now I've rewritten the core part of the access such that it is dependant upon data recieved from the server, rather than the server being dependant upon data from access. So far it appears to be working much better, but I would be interested to know your opinions. Tom - Original Message - From: George Whiffen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, September 07, 2001 5:10 PM Subject: [PHP] Re: newbie looking to sync access with MySQL Tom, Every man and his dog seems to have an Access database they want to view on the web. What I usually do for them is : a) Tell them to save the data from Access as a text file, comma separated. (It's a standard Access option). b) Give them a web page where they can upload it, (INPUT TYPE=FILE etc., it's discussed in the php manual). c) Parse the file using parsecsv and store away the bits you want in MySQL. If you get them to leave the field names on the first line of the file (another standard Access option), you can do a quick check that all the fields are there and in the expected place, before you put the data into MySQL. d) Serve the web pages from MySQL. e) You may still need your Web Data Administration. There's often missing data that you need for sensible web pages e.g. categories, more user friendly descriptions of codes etc. From the customer's point of view the uploads are a manual exercise which doesn't sound very sexy, and it would probably take them 3-4 minutes every time. The good thing is that it's all pretty easy, and they should be able to get their most junior member of staff to do it. They also get the comfort of knowing if anything went wrong. If that's not acceptable, it's more tricky. In principle you could use an ODBC interface to their Access database to establish a live connection to it and suck up the data that way, but it's fiddly and much less reliable than a boring old upload. Another option would be to execute the extract and transfer of the data via a batch job running on their platform. But, personally, I would hate to have to support that kind of activity. The bottom line as far as I'm concerned is that if they insist on holding important information on a MS platform, they can expect a) not to have a seamless operation, b) to have to watch out for problems themselves! Once they see how easy an extract/upload is they probably won't mind anyway. Tom Beidler wrote: I have a potential customer that just called and would like to create a web site that would display secure info for their clients. Currently his employees are using a flat Access database to add, edit and delete records and he would like to keep it that way. Normally I would create a web manager for them to add, edit and delete the MySQL database. Is there an easy, reliable way to sync up Access with MySQL. Maybe a nightly script that could upload the contents of Access to MySQL. Would it be easier to start with ASP and SQL Server? Thanks, Tom -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Re: newbie looking to sync access with MySQL
I have a potential customer that just called and would like to create a web site that would display secure info for their clients. Currently his employees are using a flat Access database to add, edit and delete records and he would like to keep it that way. Normally I would create a web manager for them to add, edit and delete the MySQL database. Is there an easy, reliable way to sync up Access with MySQL. Maybe a nightly script that could upload the contents of Access to MySQL. Would it be easier to start with ASP and SQL Server? If only the Access database can change, you could fairly easily schedule an export and then upload that to be sucked into MySQL. There also is at least one tool/product out there specifically to do this, but I dunno where it is... You really don't want Access behind your web-site. Too unstable. -- WARNING [EMAIL PROTECTED] address is an endangered species -- Use [EMAIL PROTECTED] Wanna help me out? Like Music? Buy a CD: http://l-i-e.com/artists.htm Volunteer a little time: http://chatmusic.com/volunteer.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]