Feasibility of using Access Database behind Frontpage Website

[kjv1611]

I have had a desire to have a database behind a website I originally designed in Frontpage. From what I have seen so far, it is apparently possible to use Microsoft Access databases in connection with Frontpage websites.

I would imagine that using a SQL Server (either MS SQL or MySQL or some other variant) would be the best approach. But it seems I just do not have a whole lot of time for getting into enough of that to get anything going with that project.

However, since I am fairly adept at using Access now, and have learned MUCH over the past couple of years in that area, that I could easily set up the website to use an Access Database.

What I want to know is this:

If the website normally does not have more than say 10 to 20 users accessing it at the same time (just a guess - don't remember any specific site statistics at the moment, but I would think this a fairly safe estimate, at least at this time), then shouldn't an Access database be powerful "enough" to make that available?

Also, I may get with someone else if we both can find the time, who does a lot with Oracle databases. He suggested that using that would be very efficient for the website I'm talking about.

The underlying tables wouldn't be terribly large. At the most, we'd be talking thousands of query-able records for the foreseeable future.

Any thoughts/opinions on such an idea?

--

"If to err is human, then I must be some kind of human!" -Me

 

[xwb]

1) Are they single table queries or multi-table queries or a mix of the two?
2) Are they just queries or will you be updating the database as well?
3) Will you be using stored procedures? They are a bit of a pain in Access: you have to write VBA to insert them. They cannot be just typed in in SQL.
4) How complex are the queries? Do you have a lot of joins?

Access is OK for just queries. It isn't very efficient on updates: it doesn't delete anything. You'll just find your database getting bigger and bigger even though you've deleted half the records. Eventually, you will have to go in and compress it.

Easiest way is to try it out. If it is not good enough for the job, you can always export all the tables in csv and import them into another database. With things like these, it is hard to tell because it depends entirely on your loading, server, network etc. You may get the case where it is fast upstairs but slow downstairs because it has to go through a 10Mb hub between upstairs and downstairs. Of course, that is nothing to do with Access but you won't know until you try it.

 

[kjv1611]

Thanks for your thoughts on this matter, xwb...

I'll try to sort of answer your questions first, ahead of all the other that I typed about this particular situation:
[ol][li]Most likely multiple-table queries, if I stick with the current structure[/li]
[li]For the web portion, just queries (via a search form); but I will be updating the database locally - no web updates)[/li]
[li]Not sure. I may have to, as the queries would be predefined based on form input, so I may.[/li]
[li]The complexity will depend upon how many items the user uses to search through the sermons - so it could be very complex[/li]
[/ol]

Apparently, back when I tried to post back a short reply, it didn't take. And then I've just not gotten back to this one. But I've had the thought in the back of my mind all along...

Before jumping to try and do this, I decided to make sure about my current hosting package. I setup hosting on a Linux server, and not MS. So, I think I'm going to try and force myself to do the MySQL stuff after all. It was something I've wanted to try for a long time, anyhow, but I've just not taken the time and effort to fully learn it well enough to do this.

What I'll need to do is setup an export process to export the data from Access into a text file, I suppose, and then import it into the MySQL database locally, then "publish" it to the web server. I have to do this, b/c where the Access database is used is on a computer that has no internet connection, and is not local to my internet-connected PC. Of course, I can also just copy the database, take it to the internet-connected PC via thumb drive, and export/import there if it makes it any easier.

Basically, this is my final goal for this database in the website: I want the user to be able to search for audio (and text if available) copies of various sermons. Then, I would like them to be able to sort the results as well.

So, the search would be something like:
Search By:
[UL][LI]Preacher[/LI]
[LI]Sermon Title[/LI]
[LI]Date[/LI]
[LI]Subject[/LI]
[LI]Bible Passage[/LI]
[LI]Or any combination thereof[/LI]
[/UL]

So, I guess I could have Frontpage create the SQL that would be used - maybe - in a String variable or something that gets sent to the MySQL database... Or either I'd have to have the SQL preset in the database, and have the Frontpage form basically trigger that form.

Here is why I was thinking Access might work better:
1. I've used Access quite a bit in my line of work.
2. Being they are both Microsoft products, they should work together fairly well.

If anyone sees any "holes" in this logic of my current thinking, let me know. I can switch to a Microsoft server, but that will end up costing a little bit more. The VERY small price difference probably wouldn't matter to the person who supplies the funding for the site, but it IS a difference that is not necessary if I use MySQL.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

A further update/question of opinion on this topic (if it would be best suited to another forum, please specify which):

I am still not 100% sure exactly how/what I'm going to do with the database portion. I have another individual in my church who does pretty high-end level db design/support for a large company. If he and I get our schedules worked out to where we both have free time at the same time one day, he may help me get a little more comfortable with Linux-based database solutions (such as MySQL, PHP, Oracle), as our website is on a Linux server).

However, since I am already much more adapt to MS tools and such, I may just go a different route altogether. I am considering looking into using MS Web Developer 2005 (Express Edition) to convert the website to a more up to date platform, since Frontpage is sort of being kicked out the back door.. .. and because it looks very promising.

What I am thinking along these lines is that I can use MS SQL Server 2005 Express along with the Web Design platform to create a better website, including the search portion. Then, I can just work on this whole new "revamp" of the web site while not really touching anything on the current site until I have it ready. Then, when I have the other ready, I can change the servers plan at our current web host, and go with the MS solution.

Can anyone offer me any suggestions/experience/advice along these lines? I hope this isn't getting too overly wide-opened a thread. If so, let me know, and I'll try to narrow it down. I've not had much time to deal with this over the past couple weeks to a month, at least. But, I think I'll have more time available to me in the near future...

--

"If to err is human, then I must be some kind of human!" -Me

 

[xwb]

You don't really use Access/MySQL/Oracle as such - you use ODBC or JDBC (Java) so the query looks the same from the web point of view.

If you write it as stored procedures then the front end stays the same. The back end, which is all the SQL stuff can keep on changing. You can rearrange your tables to your heart's content as long as the procedural interface remains the same.

SQL Server can be very fast - it was based on Sybase but it depends on what MS has done to it. Sometimes things improve, sometimes they don't. eg write a document in Word 6.0, import it into Word 2003, save and you'll find it as trebled in size. Fortunately most people nowadays have big disks so they don't even notice the difference until they try to mail it or write it back to floppy.

 

[kjv1611]

Well, I know that MS SQL Server 2005 is what we use where I work, and it's extremely fast. It's even much faster than MS SQL Server 2000.

I'm still sort of in a thinking/planning phase with all of this at the moment. Before I up and make any changes, I want to tinker some to make sure.

What I'm initially planning on doing now (I think ) is trying the new MS Visual Studio under the Web Development portion (Express Edition) for creating a new site, and create a SQL Server Database from which to run my queries on the website. Of course, I'm going to test around with it first before making the switch.

I've heard some people brag about how much better the Linux options are (web server stuff), but from what I've seen in numbers, it appears that the MS stuff is still at least as good if not better than most of the Linux offerings. That, plus I'm more used/accustomed to Windows than Linux, as I've done tons more in MS applications in every area of life, so I think I'll be more comfortable there.

Now that MS offers the Express editions, totally free of charge, I guess it's really a no-brainer for me. But I'll try all of this out to be sure, before I make any "real" changes.

Anyway, I'll try to remember to post back here with anything I find/try/experience regarding all this.

--

"If to err is human, then I must be some kind of human!" -Me