Welcome to WebmasterWorld Guest from 54.205.170.21

Forum Moderators: open

Message Too Old, No Replies

Access Database driven Website

Sketchy limitations

   
7:51 am on Apr 11, 2008 (gmt 0)

5+ Year Member



After a only one db connection error (fixed by a reboot) my hosting company said my database is really too big to be stable. I am aware of there are limitations of an Access db but everything is a bit sketchy and as a designer with basic programming I donít think I have the skills to convert to better mySQL.

My question is when should I be looking to upgrade?

Traffic: Almost 8000 page views a day (6 views a minute)

Access db size:
12.5 MB (expected to grow to 15MB in 2 years
About 1500 records
4 tables
DSN-LESS connection
Programmed in very efficient none inline simple Classic ASP

[edited by: Johan007 at 8:26 am (utc) on April 11, 2008]

1:04 pm on Apr 24, 2008 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



The problem is Access is not reliable for serving database-driven pages. The way Access fails (by locking up the web server or generating ASP errors) is unpredictable. An access-based site can run fine for months, then lock up 3 times in a day.

I'd recommend switching to SQL Server asap if possible. There is a relatively simple upgrade path from Access. The other alternative is some form of caching eg generating static pages from your database.

In the meantime 12.5MB seems big for a db with only 1500 records - is this correct? Are you storing binary data? Are you doing lots of updates or just pulling info from the db?

1:27 pm on Apr 24, 2008 (gmt 0)

5+ Year Member



Most users are reading from the database. I have 2 editors who log in and edit the db often just adding pages. We never delete records.

I feel the db works well but I guess I need to bite the bullet lean mySQL some time.

[edited by: Johan007 at 1:48 pm (utc) on April 24, 2008]

3:30 pm on Apr 24, 2008 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



I agree that 12.5MB seems big for just 1500 records. Have you tried compacting the DB?

Biting the bullet and learning mySQL would definitely be a good idea. It will not only improve this site, but will also increase your marketability with an additional (and very important) skill.

3:57 pm on Apr 24, 2008 (gmt 0)

5+ Year Member



Yeah done the compacting thing every month.

I not really clever enough to be a programmer... I am more creative designer but I guess it will look on my portfolio.

[edited by: Johan007 at 3:58 pm (utc) on April 24, 2008]

4:58 pm on Apr 26, 2008 (gmt 0)

10+ Year Member



Converting from Access to MySQL isn't that difficult.

I did it last year. There are some free conversion tools (on the official MySQL site if my memory serves me right) which work well.

If you want a graphical front end for MySQL there are several available, I use NaviCat which is pretty good.

If you are on Windows hosting, does your host offer MySQL? Part of my reason for swapping was moving to Linux/Apache hosting.

8:00 am on Apr 27, 2008 (gmt 0)

5+ Year Member



Oh can you get MySQL on Windows hosting? I need it windows because I am using Classic ASP.
8:32 am on Apr 28, 2008 (gmt 0)

10+ Year Member



can you get MySQL on Windows hosting?

You can, but I'm not sure how common it is.

Windows hosts tend to use MS SQL and ASP (more likely .NET these days), while Linux hosts use Apache/PHP/MySQL.

I swapped ASP for PHP because I didn't like .NET. Buts that's a very personal decision, PHP suits my coding style - quick and dirty!

Actually PHP is very easy to learn, it took me about a week to learn enough to move a simple site. There are plenty of free scripts for forums, shopping carts, blogging , etc. So perhaps you don't need ASP as much as you think!

8:51 am on Apr 28, 2008 (gmt 0)

10+ Year Member



As already mentioned it would be best to upsize from Access. I would recommend going the MS SQL route though more expensive than MySQL I find it more compatible with your existing classic ASP (imo). There is quite an efficent 'upsizing' tool within Access that can make the process quite painless. That is, you might only have to change the db connection string whilst the rest of the code remains unchanged. I usually only have to tweak asp code where dates are used.

With your existing Access database it might be worth either writing an application to compact the database (go google 'asp compact access database') or download it and compact it offline if feasible. You can get quite dramtic size reductions doing this.

1:25 pm on Apr 29, 2008 (gmt 0)

5+ Year Member



Thanks all.

Its only a movie review website so I like to keep it cheap as possible however will budget to MsSQL.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month