Display and Access Form via Web

Seanh39 · 01-26-2005, 04:17 PM

Hello all! I got my site a little over a year ago and I've never really done much with it, but now I have a small project to tackle. I want to hook a MS Access database to my website that will allow people to do some basic form data entry, run a few canned queries, and view reports, but I don't know where to begin with the web hosting. Will someone please point me in a good starting direction?

Thanks!

Sean

*Randall* · 01-26-2005, 05:11 PM

Well, the first problem you'll run into is that Access databases only run on Windows-based servers, not the Linux machines that FQ uses. So there's no way to use the database directly without moving to a Windows NT host.

FQ does give you MySQL, which is a different kind of database -- instead of a file you can upload to a server, it's a server in its own right. I've only played with it a smidge and don't remember much about my efforts to get data from Access into MySQL. But there are ways to do so -- this site looks like a good introduction to the differences and the challenges involved.

There may be other, simpler "database-like" options as well, but from the sound of it you need a real database to work from.

Others here can tell you a lot more about using MySQL and how to do the things you want. I just wanted to warn you that there's no quick and easy way to get an Access DB hooked up to a site here.

Randall

*ables* · 01-26-2005, 08:56 PM

Many years ago I had to write an Access application for a client. Standalone thing rather than web-based, which isn't very helpful by itself.

What I do remember, though, was that you could take an access table and have it "convert to sql" with a menu command. That could be a good way to start a conversion from Access to MySQL.

However, building a site around MySQL is definitely programming, whereas creating a database in Access can feel more like you're just working with MS Office docs. Just to warn you.

A fabulous resource for how to learn enough PHP and MySQL to create web-based frontends is the O'Reilly book, Web Database Applications with PHP & MySQL.

Plus there's a whole forum here on this very thing, and I'm sure you could find people willing to help.

-david

*Andilinks* · 01-26-2005, 09:18 PM

Quote:

What I do remember, though, was that you could take an access table and have it "convert to sql" with a menu command.

I can't find that command on Access97, but wouldn't a tab delimited file export directly to MySQL, assuming the MySql is properly configured to accept it?

My question is not hypothetical, I plan to do this and thought it possible.

Andi

*ables* · 01-26-2005, 09:26 PM

Quote:

I can't find that command on Access97, but wouldn't a tab delimited file export directly to MySQL, assuming the MySql is properly configured to accept it?

I don't have Access around to check, but I believe it was more of a "view equivalent SQL" for the table setup, now that I think about it more. It wasn't actually taking all of the data and converting it.

A straight txt->MySQL won't actually work, but I'm sure there are scripts available to do what you're looking for.

A quick Google turns up several potential possible solutions that would be a helluva lot easier than what I was thinking.

*Andilinks* · 01-26-2005, 09:41 PM

OK thanks.

It will be a few months before I do this and I will convert small portions (maybe just one page) at a time, but I am definitely moving in that direction.

Andi

*Randall* · 01-26-2005, 09:51 PM

Quote:

A straight txt->MySQL won't actually work, but I'm sure there are scripts available to do what you're looking for.

Right. I dimly remember writing a VBA script that output a file composed of INSERT commands. Or maybe it was a script I found somewhere -- still pretty hazy.

Randall

*YFS200* · 01-26-2005, 11:57 PM

You can use the mysqlimport command to import (insert type here) delimited files directly into mySQL. But you will need to have the db and tables setup first.

For a simple database, I use dbMan.
http://www.gossamer-threads.com/scripts/dbman/

It works quite well after a bit of setup.

For anything that requires more, I just write my own programs in perl. It's not hard to have perl access the mySQL and spit out the web page. PHP may be better for this, but I happen to know perl, so that is what I use.

YFS

Seanh39 · 01-28-2005, 02:37 PM

Thanks everyone! I started digging around and it looks like the only way I could use an Access DB is if I hosted it on my local machine and then had a form linked to my local via the web. I'm not going to bother with all that daisy chaining nonsense, so I guess I"m going to have to fart around with MySQL and PHP. As far as I can tell, both utilities seem to be growing in strength and it can't possibly hurt me any to learn a couple of more skills

. I've always wanted to learn how to create a data gathering webpage anyway!

Take care,

Sean