Help me Do it Wrong

February 21, 2011 4:37 PM

Ok, here's a stupid-sounding idea: can someone point me to a guide to how to manipulate an Access database client-side on a web page?

I've got an application that clearly needs a database and not a ginormous multi-user shared excel spreadsheet (which, in typical office-worker fashion, it is.) I've managed to mock this up over the weekend on my mac at home using MySQL, Apache, and php. Works beautifully. Really, I did it as a self-learning project, since that's the kind of guy I am, not because I have to, but now I can't help thinking: this would be handy in real life, too.

Here's the problem.
Work is strictly a locked-down MS Office house. No web server I have access to. I can make and use an Access database. I can write a .html page and look at it in IE. But php, asp, any server-side whatever is a no-go.

I could do the whole thing in crappy MS Access forms, I guess. Yuck.

The question is, is there a way to write my html page, using JavaScript, that could manipulate the database to generate dynamic content, when all I get to do is use IE in file-open mode, without a http server?

I'd say my technical competence is 'marginally familiar with all the technologies, quick learner, but not much hands-on experience." I could probably teach myself all the JavaScript I need to know, but I'll need more hand-holding than a pointer to an esoteric developer news-group.

posted by ctmf to computers & internet (14 answers total)

You work with crazy people who should all be institutionalized.

That said, if I understand your problem you're basically hosting an Access database somewhere, and want to offer a html file/package that lets people use the Access database without requiring a web server, since you can't host one (even on your desktop). This isn't a technological limit, it's a policy limit, yes? After all, just hosting your WAMP mock-up would be doable on your own machine, but it sounds like that's restricted. Where does the Access database live?


So to answer your question, yes this is totally doable, even though the idea makes me a bit nauseous; the server-side code in classic asp is just vbscript or javascript that has some server-side objects exposed to it, but you can use the ADODB object to connect directly to a database and do... whatever. You can also look into script encoding as part of the Windows scripting host, which offers the most minimal of protections for the casual user to see your script contents and any passwords you embed. It is NOT secure, but isn't horrible for basic end users. You'd create your script encoding, maybe even wrap the whole thing up in an mhtml, and hand that out to users.


Also, this fellow posted on kuro5hin.org in 2005 with pretty much your exact problem:

A friend asked me to do a fairly simple database app for them. The tools at my disposal are pretty much limited to MS Access and whatever else comes with Windows XP and MS Office. This should be pretty easy to do with an Access front-end, but I don't know VBA and I'm not eager to learn. I do, however, have a bit of experience in developing web-based apps using Active Server Pages and ActiveX Data Objects. I have my hammer, and by god I'm going to use it.

The customer does not have a webserver to serve as a transaction engine, so the ASP route is a no-go. However, it occurred to me that I can just connect straight from the browser to the DB. And since I can use Javascript to write other Javascript (thank you, innerHTML), there's not much of a paradigm shift.

He then goes on to describe in great detail how he did it, and has code snippets as well.
posted by hincandenza at 5:04 PM on February 21, 2011 [1 favorite]

is there a way to write my html page, using JavaScript, that could manipulate the database to generate dynamic content

No.

But php, asp, any server-side whatever is a no-go.

Connecting to Access is easy if you add it as a DSN-less connection to a local datasource. The thing is you need to be able to use a server-side scripting language. Client-side scripting isn't going to help you here, so JavaScript is out.

Finally, I would like to strongly advise you to find a more robust solution than an Access database. If you're sharing the database amongst several users, you're going to quickly find this out. Sorry to say, but a static Access database file isn't much better than an Excel spreadsheet.
posted by Civil_Disobedient at 5:07 PM on February 21, 2011

Argh, sorry about the BOLD.
posted by Civil_Disobedient at 5:09 PM on February 21, 2011

C_D: I know, hence the "doing it wrong"

The thing I think it will help with has to do with normalizing the tables and enforcing referential integrity, preventing crucial misspellings from making records evade reports, and not missing the one spot where a change didnt get applied.

No, I'm not an access fan either, even when used as intended.
posted by ctmf at 5:28 PM on February 21, 2011

Not the answer to your question but:

Can you install anything on your work machine? You should be able to connect to access using ODBC. Therefore you can use access from PHP. If you have rights, should be simple to install apache and php on your machine.
posted by Ad hominem at 6:41 PM on February 21, 2011

My god, hincandenza, the comments on K5. Makes me appreciate AskMe a lot more.

Still, one reminded me of Access' ability to generate crappy web pages from its forms. Maybe I'll play around with that a bit and see what happens. Plus, the guy's javascript strategy seems doable if that doesn't work.

I'm not worried about security at all, just careless data entry screw-ups and ease of use. It's on a private lan, and there are no secrets in there or passwords or anything. Everyone who can get at it in the first place is more than welcome to edit at will and has no reason to wreck it.

Ad hominem: no. I can't install so much as a browser extension without filling out damn-good-reason justification forms in triplicate and waiting ten years for corporate IT approval, which in turn requires national-level IT general blessing of the thing I want installed. A local machine web server would be right out, I can guarantee that without even asking.

I could put in a request for a SharePoint site (which I have no idea what that even is), but it seems overkill to go through IT for some little thing a half-dozen people in my shop will use for our own convenience. Really, this is just a spare-time kind of project. The status quo "works", it just drives me nuts.
posted by ctmf at 6:59 PM on February 21, 2011

this also looks interesting, unless I have to install something.
posted by ctmf at 7:04 PM on February 21, 2011

I'll play the devil's advocate. For 5 or 6 people Access forms/VBA is just fine. In fact, small workgroups is what Access is designed for! You'll need to split up the database into its front and rear end components and obviously keep everything on a network drive that is backed up frequently. Access works for small groups and problems tend to creep in past 10-12 or so users, but for your usage it sounds like you'll be fine.

Instead of trying to circumvent your IT policy and dreaming of a LAMP stack on your desktop for use in production (shudder) you should really exploit Access and see what it can do for you. Don't be a technology fanboy. You have certain tools that are approved and you have a obligation to use them.

An issue that you may need to consider before bringing this to the boss is what happens to this critical database the day you get a new job? Are you writing any documentation? Does your department have a backup for you to continue maintaining it? As an IT person I've inherited huge messes from projects departments have done on their own and would have appreciated a heads up or some basic documentation from the person who pieced it together. I'd really hate to iherit some fucked up JS hack job that may or may not be connected to some middleware running on someone's desktop or breaks entirely when we try to migrate to IE9 (see how organizations get stuck on IE6?). Be considerate to the guy who will come after you. Comment your VBA code, write documentation, and for the love of Darwin remember the concept of KISS.
posted by damn dirty ape at 8:08 PM on February 21, 2011

instead of trying to circumvent your IT policy

I'm not. I'm trying to follow it, which is the whole difficulty.

I'd really hate to iherit some fucked up JS hack job...

That's a great point, damn dirty ape. A mistake I've made before was, coincidentally, using Access for it's designed purpose for my single-user needs, and then being promoted out of that job. The guy who took over for me had no idea how the thing worked, even though it was pretty straightforward data entry/retrieval, so when he wanted to change a little thing in how the report looked and I couldn't play developer for him, he had to recreate the whole process from scratch in something else. Which, if I remember correctly, was a manually cut/paste word table, FFS. Few people understand how normalized databases work, and even fewer how the MS Access form/report designer controls work, it seems like.

So maybe crazy javascript and css-generated html reports is out for that reason. (Although, I'd still like to try it just for my own curiosity, even if I delete it right after.) Still, I think using access as the data storage side is worth at least building and seeing if anyone likes it.

Ideally, I could interface with the thing using my *AMP stack application clone, and everyone else would be unaffected, but I think that's impossible. (without a REALLY stupid mess involving CSV files, Excel export/import, Access, a Daily WTF article... way more trouble than just being irritated by the current method)
posted by ctmf at 8:50 PM on February 21, 2011

Even more not an answer to the original question:

This is pretty much what Sharepoint is designed to do. Check out this video of exporting from access to Sharepoint. You can also link Sharepoint lists to Access as well as customize the display of Sharepoint lists.
posted by Ad hominem at 9:17 PM on February 21, 2011

When you work in an environment controlled by Microsoft fanbois - especially enterprise Microsoft fanbois - you will do yourself no favours by choosing tools creatively. Your minimum-energy saddle point is almost certainly going to be found by translating your home mockup to a straightforward Access app.

The Access form and report generators are actually pretty good. The main reason people who know stuff shy away from Access is that multiple concurrent database users without a proper database service is indeed the Wrong Thing, and that front ends which are not web browsers are so 20th century. But if it's only a handful of users, and you back it up, it should work OK.

If you do split it into a replicated app MDB and a network-drive-shared data MDB, it will even be pretty easy for Those Who Come After to migrate the back end to SQL Server (perhaps one of the fee-free Express editions) once a compelling business case exists for that.
posted by flabdablet at 10:40 PM on February 21, 2011

Hey, thanks, Ad hominem. I'll have to look into SharePoint some more. The MS site for it gives me buzzword overload before I can even figure out wtf it DOES.
posted by ctmf at 11:05 PM on February 21, 2011

Congratulations! You've figured out what Sharepoint does!

Before they came up with the Sharepoint name, it used to be called Microsoft Buzzword (Enterprise Edition)^*.

^*Opinions expressed here, while guaranteed to reflect reality, may not be factual.
posted by flabdablet at 11:13 PM on February 21, 2011 [1 favorite]

The question is, is there a way to write my html page, using JavaScript, that could manipulate the database to generate dynamic content, when all I get to do is use IE in file-open mode, without a http server?

Couldnt you do this with an HTA app & ADO? (example)
posted by canoehead at 7:48 AM on February 22, 2011