Using Microsoft Tools To Design Web Sites That Work w/ Apache?

Grimster asks: " As a new Web hosting company/ISP we're just now getting to the point of being asked for "E-commerce" and other database requiring Web sites. Currently we're running Solaris 7 on Sparc, Apache + PHP + Frontpage Extensions + SSL, and a MySQL database. The problem is, our Web designers want to use MS Access to create the databases and then deploy those onto our Web sites, both for our customers as well as for our own projects. I've been scouring the Web and all resources trying to find out what (if anything) I can do to support this approach to Web development, and so far I haven't really found anything "good" or even possible to allow this. The question is, how can my Web designers use MS Access and Frontpage to create these Web sites and my Apache server support this, without paying Microsoft Tax by being forced to build an NT + IIS + ASP Web server? I really hate the thought of having anything critical running on NT. Any hints, solutions, antecdotes, and advice on this subject will be utterly appreciated."

Comments

PHP can do ODBC...

[TOCie]

...and I'm pretty sure you can put together some bastardized connection between it and an MS Access database. You don't really need to lose your Apache front-end setup, but you might find yourself reliant on NT for the backend, which might not be acceptable. As for Frontpage... Point them to a nice little box of Dreamweaver and pray they like it. (As it can play nice with PHP. :))

Re:PHP can do ODBC...

[phee]

Point them to a nice little box of Dreamweaver and pray they like it. (As it can play nice with PHP. :))

Every time the web developers here TOUCH one of my PHP scripts with Dreamweaver, it totally destroys it. It can't seem to get it through its little Microsoft-centric mind that not every ">" sign isn't part of an HTML tag... it takes damn near every "?>" and turns it into "<"... but, oh, does it work nicely with ASP.

Use Homesite, BBEdit, or some other plain text editor if you're going to play with PHP.

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Re:PHP can do ODBC...

[pen]

The best way to get Dreamweaver to play nice with PHP is to use <% and %> tags and then enable the ASP tags setting in PHP. (PHP will recognize the <% %> syntax, while Dreamweaver will think it's ASP and ignore it.)

--

Re:PHP can do ODBC...

[phee]

UGH!!!!!! Put ASP tags into a PHP script??? That's worse than Philip Glass selling out to Pepsi ads. There's no excuse for Dreamweaver destroying PHP tags; if it can recognize ASP delimiters, it can damn sure recognize PHP ones. Just because PHP supports using ASP tags instead doesn't mean I'm gonna go rewrite 2,000 scripts, anyway; I'd much rather use a cheaper HTML editor than pay money for something that's purposely designed to make life difficult for non-Microsoft-zombies...

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Re:PHP can do ODBC...

[pen]

One can bitch about Dreamweaver, or one can easily adjust to its shortcomings. Which one do you choose? "<? ?>" or "<% %>"? I don't see a major difference...

--

Re:PHP can do ODBC...

[phee]

The major difference is that I already have 2,000 scripts with "" in them on about five different servers. The developers know to use BBEdit instead of Dreamweaver if the filename ends in .php or .phtml already, so there's no problem there. And besides; I'd rather suck out my own prostate than give in to the whims of programmers who refuse to give PHP as much respect as they do ASP (just search the Dreamweaver help system for "php", then search again for "asp" and compare the results).

Apache runs on over 60% of the world's websites. PHP runs on almost 1.5 million of those sites. The only people who use IIS/ASP/NT servers are big businesses who've been brainwashed into thinking they're more stable, reliable, robust, etc than Unix servers; all the rest of us know better. And some of us refuse to have any trace of any Microsoft product associated with their sites... :')

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Get a big stick

[Matts]

And hit them over the head with it, until all thoughts of MS Access finally leave their bleeding pulverised bodies. ;-)

Seriously - MS Access is not safe enough for live web use, so I assume you're hoping there might be some porting tool to convert MS Access DB's to PHP + MySQL. I think you've reached the conclusion that there isn't, so it's time to reach for that stick.

Use Access as a frontend to MySQL

[flawed]

Perhaps it is acceptable for your WebDesigners to use MS Access as a frontend to the MySQL database through ODBC drivers, so that the actual data is still stored in MySQL.
We use that setup with our Sybase databases and our Web development people are quite happy with it.

Re:Use Access as a frontend to MySQL

[Grimster]

So setup the MyODBC driver on my Solaris box and have my designers point MS Access 2000 to this "external database"? That sounds possible...

This has been plaguing me for 3 weeks now, the powers that be are wondering why I can't get this to work and wonders why we just don't put up an NT server, my reply is I am not deploying any server that I can't administer from the comfort of an SSH connection from home.

I will try this it sounds "doable", now if I can get Chilisoft to work I might be done with this nightmare.(are there any other ASP solutions for Apache? I've yet to find one) Grimster

Re:Use Access as a frontend to MySQL

[DrZaius]

Warning:

ODBC puts a massive strain on a database server!

This has to do with the way it updates its tables. Do not connect access to a live server. I've seen this with MySQL, but I imagine that it is exactly the same with every other dbms.

Consider yourself warned.

Re:Use Access as a frontend to MySQL

[phee]

I have to agree. I've never found it *necessary* to use ODBC for database access except when it has to be used with IIS and ASP (and thus a System DSN is needed). As I mentioned earlier, if a database has been designed and entered in Access or something foreign to all that is holy, export it to flat text and write a small PHP script to parse it and use MySQL calls to do the data entry. People who shop around for months for tools that will do these kinds of things never find what they're looking for; it's always best (and MUCH faster) to just write your own that's customized to your particular task at hand. If you can. If not, I charge $50 an hour... hehehehe...

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Re:Use Access as a frontend to MySQL

[dlc]

• are there any other ASP solutions for Apache? I've yet to find one

Hey, there sure are. Joshua Chamas' Apache::ASP, which runs under mod_perl, is probably the best solution. It runs exactly as IIS' ASP does, using the same constructs. It would probably be worth your while to check out.

The full Apache::ASP home page is at http://www.nodeworks.com/asp/, and there is tons of support for it on the mod_perl mailing list (including the author himself).

darren

Cthulhu for President!

Re:Use Access as a frontend to MySQL

[acarey]

I am not deploying any server that I can't administer from the comfort of an SSH connection from home.

Um, you _can_ administer NT via SSH from home. We've got several NT boxes here which we can administer via SSH (run on a Linux box, of course) using VNC. Works like a dream.

Not suggesting you deploy NT, of course, just giving an alternative point of view ;)

Re:Use Access as a frontend to MySQL

[ths]

You can have then design and populate the database in Access, then export it using MyODBC to a MySQL database on the server.

I have actually done this before, before finding phpMyAdmin, to quickly mock up and modify database prototypes.

Of course this doesn't help with your Access/Frontpage interaction dilemna. I would suggest you get them to read this discussion. The ease of implementation of Microsoft's "easy" solutions is much harder than they think. It would probably take less time for them to learn PHP or perl than to get Microsoft's wizards to do what they want! ;-)

Re:Use Access as a frontend to MySQL

[flawed]

Um, you _can_ administer NT via SSH from home. We've got several NT boxes here which we can administer via SSH (run on a Linux box, of course) using VNC. Works like a dream.

I guess it is a bit sluggish over a modem line, isn't it?

I am one of those Web Designers...

[crowebiashi]

I work with Grimster, and I use perl for most of my db work right now using flat files ( low use, low volume db's ) ..

What we are running into is that the boss and one of the other designer's is wanting to use access because the believe it will automatically produce the html and queries for them.

We've already told them we would rather use PHP / mySQL or even ASP / PERL / PHP mySQL. Anything but a pure M$ Solution.

But, we have to have facts that back it up, I guess our opinion isn't enough to sway our leader, even though it is our job to tell them what the right choice would be.

I've been looking for resources that compare Access to mySQL or any of the *ux sql servers. We need Benchmarks, Usage Stats etc to show the boss.

If anyone knows of a url, we would appreciate it!

----

Crowe

exportsql.txt

[JakeS]

Get exportsql.txt from the MySQL contributed downloads page. Paste it as a module in the Access database and run it. It will make a SQL "script" which you can run in mySQL to recreate the data exactly. I did something similar for a client some time ago and it worked quite well.

As a plug for a favorite product, I'd suggest pointing the designers toward Visio2000 for database design, rather than doing it directly in Access. Visio2000 lets you visually draw out the entities and relationships. Then, with a few clicks, it will actually create the structure you've drawn on any ODBC-compliant database (Access97, SQL Server, MySQL, whatever). It's VERY usefull for complex database design.

Re:exportsql.txt

[phee]

As a plug for a favorite product, I'd suggest pointing the designers toward Visio2000 for database design...

And, lo and behold, coincidence of coincidences, Microsoft assimilated... I mean, "bought"... Visio. How long will it be before it'll only design MS SQL or Access databases? Hmmmmmmm.

Oh, not to mention the fact that you'll spend $900 for it.

You know how I design databases? On paper. I draw out everything the database will need, and then using (gasp!) pico, I proceed to write the structure...

create table assimilations (
id int not null auto_increment primary key,
company_name varchar(255),
assimilation_price float(25,2),
ex_CEO varchar(255),
new_CEO enum('Bill Gates','Steve Ballmer'),
assimilation_date int
);

...and then if need be, use a PHP script to do the data entry legwork for me. See how easy it is?

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Re:exportsql.txt

[phee]

...and since it wasn't a REAL troll, and neither am I, I use pico. vi is not quite as bad as emacs in terms of usability, but it's close. I've yet to find a text editing task that was any easier in vi than in pico. Once you figure out how to keep the damn thing from breaking long lines wherever it feels like, pico rules...

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Re:exportsql.txt

[lomion]

Pico likes to add characters to the end of lines which is bad, and vi won't break long lines if you have it configured and set right. Like anything else it comes down to using it properly.

Re:exportsql.txt

[phee]

I've never once had pico add anything to the end of a line unless it was a DOS-format file and then it just replaces carriage returns with linefeeds. I've also never seen vi break long lines. It's just a matter of ease of use; I find using ^K instead of "dd" easier to use to cut a line, and ^U to paste the lines that were cut somewhere else (I have yet to figure out how to cut & paste something with vi), ^R to read in a file, not having to hit a special key to get the keystroke commands, etc. pico is just plain easier.

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Re:exportsql.txt

[lomion]

yy yanks lines pp pastes lines

A helpful (?) article and an abstraction layer

[arthurs_sidekick]

I know you hate the idea of running anything critical on NT, but maybe you should just hold your nose and set aside one box as the database server. There's an article on phpbuilder.com that should get you started here.

The other suggestion is to point out that, from a programmer's point of view, there's not a hell of a lot of difference between using Access and any other SQL-speaking RDBMS that's got at least as many, if not more, features (such as MySQL, which at least has a security model of sorts). If they're really prissy, you could write a DB abstraction layer (check out phorum's at www.phorum.org for the basic idea) that hides all the db-specific functions from them (e.g. write a DB class and a query() method in PHP that can do Access, Postgres, Oracle (if ever you become well-heeled), or whatever.

Sad but true

[Vryl]

Scary that these days I am being turned into a m$ apologist, but ... Credit where credit is due. "anything critical" ... like a website that gets millions of hits a day? When was the last time you saw microsoft.com go down? Last time I heard, it ran on NT. For what its worth, we run a whole heap of sites from an access backend, from scripts in visual basic (well, sort of) running cgi-bin on IBM HTTP Server, which is their name for APACHE. We use apache because IIS is pretty brain dead, and makes NT unreliable. Without IIS installed, NT is very robust. We use access because it is easily the fastest database on the market, but it will not scale very far, which is fine for our current situation. We are porting our scripting language to a Java Servlet Apache mod to target non m$ sites, most likely Linux, AIX and Solaris. If anyone wants to see it in action, mail me, but I don't think our infrastructure would survive a /. effect (then again, how many ppl read this part of /. ?).

Re:Sad but true

[psychophil.com]

I can't remember when a hit to the main page of www.microsoft.com generated an error, but I run into problems with the back end at least once or twice a week (back end meaning search funtions, knowledge base, etc.). Server errors, parse errors, etc... It may always be up but its far from stable.

Re:Sad but true

[phee]

How does M$.com stay up? It's very simple. They have a bank of servers, some doing work and some just sitting there doing nothing while waiting for the ones doing work to crash; when they do (and they will), the ones that are idle take over... thus, you get the illusion of stability when in fact, their servers are crashing all the time. In other words... IIS is great if you throw enough money at it, don't mind having a 24-hour crew of sysadmins running around hitting the reset buttons on the crashed servers all day every day, and don't mind having to buy twice as many servers as you would need with Apache.

By the way, I recently had need to port an Access database to MySQL, a big list of zip codes which was only distributed as an MDB file. I couldn't find any way to just export a SQL file to import directly into MySQL, so I exported the table as a tab-separated list. The latitudes and longitudes in the database are accurate to 6 decimal places; the ones it exported were only accurate to 3 decimal places, and there's no way to tell it "I want 6 decimal places, damn you." So after wasting hours playing with all the exporting options, I found that if you export it with the "Formatted Report" method, it DOES keep all six decimal places... so I just took that 12-meg file (it puts a line of data, a line of "--------", a line of data, a line of "-------", etc) and wrote a PHP script to parse it and directly insert the values into the database. Worked like a charm.

So you see, there are ways around Microsoft's half-assed programs... I mean, how hard would it be to have "Export as SQL" in the options? Just basic table row data entry is the simplest thing you can do in SQL; it's not like it would have to know whether it's Oracle or MS SQL Server or MySQL or Postgres, for god's sake...

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Re:Sad but true

[Vryl]

Am interested in your story about porting from access to MySql, we have had no probs going from access to mysql or postgres, mysql has legendary reliability problems tho ...

I am interested in replicating the bug you detail, will see what happens, and perhaps report it to the appropriate forum.

as for your comment fre microsoft.com, you are so wrong its not funny, and obviously speaking from ignorance.

apparently, microsoft.com is Four (4) quad-alpha servers (hrrmmm ... what a big bank that is!).

This is quite common, and I am sure one crashes every now and then, but then so do many webservers, and no matter what OS you use, common sense depands that in mission critical situations you implement fail over servers.

Re:Sad but true

[phee]

I've never seen mysql be unreliable. In what way is its unreliability legendary?

The bug, yes. Well, the database in question has two columns, latitude and longitude, in it. The values in these columns are float values accurate to 6 decimal places. The fields themselves are set to "auto-decimal-places" or whatever Access calls it. When exporting as any kind of comma- or tab-separated values flat file, it rounded them down to 3 decimal places. I tried setting the field type to "floating - 6 decimal places." It then chopped the values in the database to ONE decimal place. The only way I could get it to export a flat file and not screw up the data was with the "formatted report" option. (This is Access 97 running on NT server 4, incidentally.) I didn't try any ODBC stuff; it wasn't worth installing myODBC, rebooting the server, etc to get it to work; far easier to make something that could parse whatever it shat out. Besides; I have MySQL's permissions (and inetd.conf for that matter) set to disallow all external server connections for security; I didn't want to change that either.

Now on to crashing web servers. I've been administering one single Linux box running apache, mysql, msql, php, modperl, etc for over two years now. It serves up Twenty (20) different sites in 128 meg of RAM. Several hundred thousand hits per day. That one box has never needed a failover server, because the only time it's been down is from power failures (which would have killed any failover servers too; our colocation site kinda sucks), planned hardware upgrades at 3am, one DOS attack... never because the box just suddenly crashed. I also have an NT box sitting right under it; it's twice as fast, RAID array, 256 meg of RAM, and serves up One (1) site. Just One. In the past two weeks, I have had to drive up to the place and manually reboot the thing THREE TIMES because of either (a) Cold Fusion server sucking up 600 meg of memory and bringing everything to a crashing halt, or (b) inetinfo.exe suddenly deciding it needs to consume 100% of the CPU cycles and refuse to let anything else do anything. So you can see why I think as I do about running a web server on NT; it's as sturdy and trustworthy as a skyscraper made out of toothpicks and Elmer's glue. 4 non-Intel quad-cpu servers (wasn't MS going to drop Alpha support from NT? Odd they're still using it...) running Unix would be easily twice as stable as it would if they were running NT. Hell, 1 dual-P-II Linux box doing 20 times as much as a dual-P-III NT box is more stable. Any non-brainwashed sysadmin knows the truth...

But perhaps I am speaking from ignorance instead of experience. Maybe the version of NT they release in Australia is more reliable (128-bit encryption notwithstanding). Maybe I should trust what people tell me instead of what I personally have to deal with on a daily basis. Maybe... not.

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

Hey!

[randombit]

What are you talking about? Everyone knows that MS is fully compliant with every standard worth using (why do you think people talk about The Microsoft Standard; they like standards that much). No wonder Microsoft recommends using IIS instead of Apache: Apache doesn't even support those great standards Microsoft is such a big fan of! Sheesh.

I was going to suggest this...

[cr0sh]

Ya beat me to it!

Well, here is another link for it, anyhow:

http://www.cynergi.net/exportsql/

What about FrontPage?

[gbr]

Has anybody tried to use the Unix Frontpage systems? I'd rather not throw up an NT box just to do FrontPage stuff for my clients.

The last time I looked, Unix FrontPage was a major security risk.

Re:Dreamweaver can do PHP

[crisco]

Or upgrade do Dreamweaver 3, PHP support (or should I saw PHP ignorance - as in it won't rewrite code) built in.

Or take a look at the documentation on extending Dreamweaver. You might be able to write a translator for the PHP tags as a .dll that plugs into Dreamweaver and tells it to ignore them.

If your web weenies are going to use a WYSIWYG editor, Dreamweaver is the one to go with. As long as they know about the difference between Netscape and IE, they can write cross browser HTML with it. If they can't comprehend even that, a text editor isn't going to be much better for them.

And if they are going to be mucking around in PHP, you might set them up with PHPed (US mirror) as the external editor for Dreamweaver. With the HTML, PHP, and SQL all highlighted they even know what they can mess with and what to leave alone.

MS Access and Frontpage

[phutureboy]

The only *nix web product I know of that includes native drivers for MS Access .MDB files is Allaire Cold Fusion, which runs on NT, Solaris, Linux and HP/UX. I'm just going on my feeble memory so I could even be wrong about that.

I do recall a discussion somewhere (maybe on zend.com) about how you can use MS Access to create a database, and then do an 'Export SQL' to publish the contents to a MySQL server. I forget the details, but you might do a little searching.

There also are a few graphical client programs that are similar to Access (or MS SQL Server Enterprise Manager) that allow you to manage a MySQL server and add/edit data. I use KSQL under KDE, but I know that there are several others for Linux, and at least one for Windows. This might be a satisfactory alternative to Access for your designers.

Now, about FrontPage. I don't know how else to say this except... it's complete and total dog shit. I would elaborate but the product is so bad it doesn't deserve my time.

If the main reason you are stuck with Front Page is for its ability to publish easily to remote servers, consider these alternatives:

WebDAV (Web Distributed Authoring and Versioning) does the same basic thing, only it is standards-based. There's an Apache module for the server side, and a number of options on the client side, including 'Web Folders' (or something like that) under Windows 98. See http://www.webdav.org for lots of info on DAV.

RSync - I edit my sites on my personal workstation, and then publish the changes to my production server using RSync (http://rsync.samba.org/) over SSH. It basically is a quick, seamless way to mirror a site from one machine to another. It only transmits the changes each time you run it, not the whole site.

Anyway, hope that helps a little bit. Good luck.

- pb with no j :(

Linux PHP talking to MS Access

[TeamFXML]

ODBC Socket Server easily allows Linux servers to talk to Microsoft Access. There are clients for PHP, Perl, Python, and C++. Its GPL and can be found at http://odbc.linuxave.net.
Let me know if you have any questions or comments about the software.

Easy solution :

[Betcour]

Install MyODBC on all machine running Access - then you can control the MySQL database thru Access. Plain and simple.

Re:Dreamweaver can do PHP

[phee]

Unfortunately, the web weenies all use Macs to edit things with (no Mac version of PHPed). They've been using DW3 for months now, and it still does the Bad Stuff with PHP. I've looked at the extension template stuff for DW; looked too complicated to be worth it for the few times they'd need it (usually, they make the page first, then give it to me, and I stuff PHP into it with pico :) I also, of course, had to write a little PHP script to convert their files from the Mac-format linefeeds to Unix-format ones because I can't get them to set the "Use Unix linefeeds" option in DW and leave it there...

Mac Weenies. :) (Yes, they crash just as much as Windoze boxes do, and use up twice as much RAM doing it...)

"The best weapon of a dictatorship is secrecy, but the best weapon of a democracy should be the weapon of openness."

ODBC is your (cranky, annoying) friend

[sohp]

Slap an ODBC System DSN for MySQL onto your designer's boxes, let them create linked tables.
It's deep in tedious details to work out and the setup is a bit error-prone, but once done it's trivial to link tables in Access.

BTW, you do know how horribly insecure the FrontPage extensions are, yes?