Using Access DB for e-commerce site

will allowing picture uploads cause problems?

[Drum]

I am trying to decide if an Access database will be ok. Here are my estimates for my e-commerce site:

300-500 Visitors a day,
50 records tops, will never grow more than that,
5-10 concurrent users,
90% of concurrent users will read from the DB,
10% of concurrent users will write to the DB,
What i am worried about are the concurrent users that are writing to the DB will be uploading about 5 images at a time, and that might take sometime, will that lock up the DB?

Anyone have experience with this?

[duckhunter]

You have pretty much described the limits of Access. More than 5 concurrent users will start showing problems. More than 2 or 3 writing will bring it to it's knees. Check out MySQL, a cheap alternative that will scale up a little more.

[Drum]

I am using super cheap hosting at $5 per month and they only offer Access - you get what you pay for i guess.

[txbakers]

Yep you're right. $5/month for an ecommerce site is asking for trouble.

1) Access won't cut it.

2) Find a host with a SQL database such as mySQL or MSSQL.

3) Pay more.

You expect to make money, right? Be prepared to spend some.

[Drum]

My biggest concern is allowing customers to upload their own pictures (5 max) to the access DB. Even though i will have a cap on file size, just the act of writing to the access db for such a long period of time for each photo from the sounds of it will bring access to its knees.

[txbakers]

Yes you're right again. Access alone will bring your website to it's knees. Trying to store BLOBs in there as well will finish it off.

IMO, you shouldn't store the images themselves in the database, only the path to them.

[wackal]

txbakers is right that you shouldn't upload images directly to the db. you should upload them to a folder and insert the path into the db. also, use 2 db's, one for reading and one for writing. that will keep the site from slowing down due to db limitations.

[Drum]

Thanks for the advice.

You guys have convinced me to go to MySql and not use Access due to the amount of writing to the DB.

As mentioned above, should i use a separate db for writing when using MySql?

[digitalv]

Just to add to what a couple of others here said, you don't want to store images as blobs even in SQL ... there just isn't any advantage to it. The best thing to do would be to get a component like ASPUpload (I'm sure there are components out there that do the same thing for free if you look around) and upload the files to a directory on your server and just write the LOCATION of the file to the database as text. Much easier on the DB.

If protection of the files is your concern (ie; you don't want people to be able to link to them) then this can still be accomplished. Store the files in a subdirectory ABOVE your webspace, and display the images through a script. In ASP your file would look like this:
=======

<!--METADATA TYPE="typelib"
UUID="00000205-0000-0010-8000-00AA006D2EA4"
NAME="ADODB Type Library"
-->
<%
' Your ODBC code to get the image name '
' for now we will pretend the image is stored as '
' a string called theImageFileName '

ShowImage(theImageFileName)

Function ShowImage(xImage)
'Create a stream object
Dim objStream
Set objStream = Server.CreateObject("ADODB.Stream")

objStream.Type = adTypeBinary
objStream.Open
objStream.LoadFromFile "C:\PATHTOIMAGE\" & xImage

' Uncomment the line below that applies to the file type '
' Response.ContentType = "image/gif"
' Response.ContentType = "image/jpeg"
Response.BinaryWrite objStream.Read

objStream.Close
Set objStream = Nothing

End Function
%>

========
Then you would just call the image using thisfilename.asp?whatever and let the script do the rest. Using a session variable to lock it up, no one will ever be able to link directly to your images because they're not even in the same directory as your web files.

[Drum]

Thanks, I will definately only store the file names in the database.

[wackal]

if you're going to use mySQL, then you probably wouldn't need to use separate db's for reading and writing. That was just a workaround that I use to get around the Access limitations.

[Drum]

In MySQL, does having two separate DB's run slower on a Join command (joining two DB tables from the two different DB's) then doing a Join command on just one DB?

[GeorgeS]

Access will work just fine as it works with many of our websites. We have 1.2 thousands clients (real estate brokers) loggin in every day, downloading & uploading info & photos. We also have 25-40 new sign-ups a day and we have Admin & tracking system (data comes from the same Access DB) that our 15 employees are using constantly.
Everything works fast and I don't see any need yet for upsizing to MSSQl server as we have planned before.

The thing is that new JET engine can handle a lot more than 5 concurrent users as many have been mentioning here. It was somewhat true with Access 97 version but not with 2002 & especially 2003 versions.

We looked into using MySql and have rejected this plan because with Access/MSSQL we can very easily create/edit user Win front ends (integrated with MS Office through VBA) for our intranet and use the same DB for our website.

If you don't believe me read this topic from support forum of YesSoftware's - the run the whole support system on Access DB.
[forums.codecharge.com...]

Regarding cheap hosting.
We pay in average from $5 to $12 per website and pretty happy with web hosting (includes support ASP, ASP.NET, PHP, CF, MySql, ACCESS, MSSQL & more).
I went through many providers - higher price doesn't guarantee you better service