Web Development

Ask something
Discussion Locked

SQL vs Access on a website

0 helpful
13 replies

by tink!

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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 [i](at least to my inexperienced eye)[/i]

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?

13 replies

by sawan gupta

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.

Regards,
Sawan Gupta

by shellbot

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?

by tony hopkinson

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.

by tink!

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.

by tink!

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.

[i]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.[/i] :)

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!

by tony hopkinson

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 = ...

by tink!

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 [b]within[/b] 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.

by tony hopkinson

Two tables

Files
FileID
FileDisplayName
FileContent
...

UserFiles
FileID
UserID

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=...

HtHs

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

by tink!

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".

by tony hopkinson

Users
UserID UserName
1......Tink
2......Tony

Files
FileID FileName
1......SQL Basics
2......How To Design a Database
3......What you need to know to use Access.

UserFiles
UserID FileID
1......1
1......2
1......3
2......3

The query I gave you would give for UserID 1 Tink

SQL Basics
How to Design a Database
What you need to know to use Access.

Like is for string matches. e.g.

select * From File Where FileName Like '%To%'

would return two rows
2......How to Design a Database
3......What you need to know to use Access.

PS access being totally non-standard accepts * as a while card instead of %

:D

by tink!

Now that I see it I feel totally stupid for not "getting it" in the first place. LOL.

Thanks a bunch for all your help Tony! I'll try it when I get to work on Mon.

Tink :)

This discussion is closed to new replies.