SQL vs Access for BackEnd

[Nimrod]

Hello Access Experts !

I do a fair amount of coding in excel. However I'm fairly new with Access and even less so with SQL.

I'm interested in doing a project in which I would like to use excel as my front end and either Access or SQL as my Back End . And I'm just wondering how robust Access will be for me. I'm going to want up to 12 users accessing the DB thru excel at any one time. Am I going to have locking issues with Access ?

Thx for your time :D :D

 

[Corticus]

Hi Nimrod,

Not an Access expert but, SQL isn't really a back-end. SQL is standard query language and is an attempt at a universal language to manipulate relational data. Access will evaluate an SQL expression to generate , delete, append, update or join recordsets with criteria like any other database application. MySQL, Oracle, an AS400 are all back-ends, as well as front-ends.
Access deals well with medium-sized data sets, Oracle is better though. MySQL is free, you can download it, but it has some limitations, though I don't know what they are specifically because I don't use it.
Why would you use Excel as a front end? You can embed a spreadsheet in a form, that would keep your all your objects in Access, which eliminates a lot if things that could go wrong. Even that seems uneccesary.
Could you make a form in Access that would accomadate the front-end processes necessary, instead of a spreadsheet? Access will let you view a datasheet in a form, and you could have that datasheet be of an expression query if its necessary to have calculations, and you can put textboxes underneath the datasheet on the form to do aggregate functions like sum on the fields in the datasheet.
I don't think you will have problems in Access with 12 users.

HTH,

 

[Nimrod]

Thanks so much for taking the time to give me such a detailed answer .
.. it was most helpful.

Just for the record I'm aware SQL is the Standard Query Lanquage... when I said SQL what I was referring to was MS's SQL server... sorry for the confusion.

 

[Dugantrain]

I've never used mySQL before, but someone once told me that it doesn't allow for outer joins. How could you possibly run a db without the use of outer joins?

 

[Mark W.]

I've never used mySQL before, but someone once told me that it doesn't allow for outer joins. How could you possibly run a db without the use of outer joins?

Using Union and a subquery.

 

[gator]

mySQL rocks!

Corticus-
Just a clarification, Oracle and mySQL are not front-ends.

-gator

 

[Dugantrain]

Why does it rock? I'm looking to pick up a new technology in my free-time. Access is my bread and butter, but I've been running over some pretty big potholes with it, especially with all the multi-user issues. How does mySQL compare?

 

[Corticus]

gator,

We use Oracle 6i forms here at work as a front end.

 

[gator]

Corticus

I guess it's just a differing of opinions, I think of Oracle forms as being very distinct from Oracle.

gator

 

[Corticus]

gator,

Actually, I was agreeing with you. I didn't realize that the front-end for Oracle was called Oracle Forms, I figured the two were bundled as one entity called Oracle.

I appreciate the clarification,