Web Development

General discussion


SQL vs Access on a website

By Tink! ·
Ok, apparently SQL is preferred by knowledgeable application developers.

I am currently revamping the website for my employer. The site was originally designed by a third-party design firm. They then provided all the files on a CD.

The current setup uses ASP pages with the data stored in an Access Database. This is the setup I've been learning to work with.

They did however, provide a folder with a SQL version of the website. There seem to be only minor differences between the two setups.

The biggest one is that the SQL data is stored in an Excel file rather than a database.

As for the ASP pages the coding is very similar. Obviously it has to refer to a different data file and type, but the recordset commands and layout are almost the same as with the Access DB.

There are couple of places where the SQL version seems to have less code, but other than that there is no real apparent advantages (at least to my inexperienced eye)

The future plans for the website are to allow customers to login and access specific pages, some of which may actually be specific only to them.

Am I alright to stay with the Access DB and ASP pages? or should I switch over to the SQL setup?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -


by Sawan Gupta In reply to SQL vs Access on a websit ...

Using a Access or Excel solution, is that really scalable in long term. I don't think so.

All depends on number of visitor/users of the website. Even the existing infrastructure of ASP/Access maybe sufficient for now.

Sawan Gupta

Collapse -

SQL is best

by office In reply to SQL vs Access on a websit ...

Many reasons, including performance, security, .NET support and etc.

Collapse -

Silly question

by Shellbot In reply to SQL vs Access on a websit ...

If the SQL version has its data stored in excel...then..why is SQL being used?

Call me silly, but if your hooking into a database, surely one would keep the data in it, otherwise there is not point?

Collapse -

SQl data stored in Excel file ????

by Tony Hopkinson In reply to SQL vs Access on a websit ...

That's access vs Excel, not SQL vs Access

Can't see any benefit in switching over whatsoever, come to think of it, can't think of a single reason to use Excel.

Collapse -

That was kind of my thought too

by Tink! In reply to SQl data stored in Excel ...

when I first checked out the SQL version. To me Excel did not seem to be a better choice over Access. I'm not sure WHY they chose to do it that way, I wasn't here when they gave the company all the website files.

That means the original setup is about 7 years old. Plus my company was using old computers with WinNT and probably didn't have SQL Server, thereby making the web design firm set things the way they did. That's my guess anyway.

Collapse -

I need some suggestions

by Tink! In reply to SQL vs Access on a websit ...

on what you think the best way to setup the following scenario using ASP and Access:

Keep in mind that this is going to be a small user base, probably never even reaching 200.

Also please remember that this is the first time I'm doing this sort of web design and am learning the ASP code as I go so try not to get too complicated. :)

Each customer is going to have a user id and password.

Upon logging in they will be directed to a menu of files available for their viewing (users will NOT be doing any editing or writing).

The user data and the file data is being stored in an Access table.

- What is the best way to make the menu display files that are specific to the user?

I'm kind of lost with how I should design the Access tables to be able to quickly look up the files specific to the user based on the user id (or vice versa if that's better).

As I said before there will not be a ton of users. As for the files there will be about 250 total but each user may only need about 10-20 of those.

Thanks a bunch everyone for your info!

Collapse -

At the point they do the login

by Tony Hopkinson In reply to I need some suggestions

you have a unique identifier, an SID, a GUID, a number depending on how you are 'registering' users. Use that as an id with the files then it's just select From TheFilesTable where UserID = ...

Collapse -

going along those lines Tony

by Tink! In reply to At the point they do the ...

Yes, they do have an SID, but my question is, for the files do I have just 1 field that contains all the user ids that go with the file? If so, what is the query statement to tell it to look for that SID within the contents of the field?

In case I didn't make it clear before. The 200 some files are general files and although each user is only going to need access to a few of those, several users will have access to the same file.

Collapse -

Ah, got you.

by Tony Hopkinson In reply to going along those lines T ...

Two tables



FileID can be an autonumber (identity) if you want in Files, but some unique identifier for the file anyway, make that the key.

UserFiles is keyed by FileID and UserID
Giveing you a many to many. Each user can have 0 - n files, each file can be applicable to 0-n users.

But you only need one record per file in Files.

So to disply a list of names for a user

It's a join of UserFiles and Files

e.g. Select FileDisplayName From Files,UserFiles
Where UserFiles.FileID = Files.FileID and userFiles.UserID=...


You can do all that in the access gui as well, but being the "God Of SQL", I refuse to do so.

Collapse -

oh ok. but 1 question still.

by Tink! In reply to Ah, got you.

Doh! Should have thought of that tabel setup myself. lol.

One question though when using "WHERE UserFiles.FileID = File.FileID"

If the UserFiles.FileID field is going to have more than 1 fileID in it, can I still use "="?

I was thinking I'd have to use "LIKE".

Related Discussions

Related Forums