Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2004
    Posts
    11

    Unanswered: Small business dilemma: Access or SQL Server?

    Hi,

    I work for a small business of about 80 employees which is growing all the time. At present all systems are paper based and we need a database. Which is my job! We want remote users to access the database via VPN connections to the LAN at head office.

    I estimate that we are at this point in time unlikely to have more than 25 simultaneous users working on the database at one time (making updates etc).

    As a growing organisation we hope to put all systems into a database that can be accessed and updated from anywhere. I understand the limitations of access and the possibilities of SQL server. My question is at this point in time, should i be looking to develop the database using acess or should we be seriously looking to invest in SQL server?

    I know SQL server will probably cost a million times more with all the licenses etc. But how much are we likely to spend on licenses? Would each client computer require a license to access SQL server? I am relativley new to making decisions like this and I would appreciate any help you guys could throw my way!

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i would not use JET as the db engine for 25 VPN clients.

    JET is famously fragile with interrupted connections (my VPN drops ten times a week: very bad news if i was talking to JET)

    JET is also a dead-end if you later need more users: 50, 100, 300, 500 - JETs shared-file mechanism will die sooner or later.

    perhaps consider the free options before taking the plunge:
    SQL Server Express which is free, but I confess I have not played with it. it's predecessor MSDE was deliberately performance-limited at above (? memory fails me) 5 concurrent users, but the new Express blurb reads as though it is not limited in the same way.

    mySQL is also a serious contender with the potential to grow huge if you need it later... all at a $0.00 price that just cannot be beaten! i'm still using an old rev, but 5.0 is now supposed to have stored-procedures, triggers, and views - worth a look.

    this is not intended to discourage you from using Access. it is a superb database-aware front-end tool that can be made to sing and dance with more serious backend rdbms.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Posts
    11
    Is it possible to design the rdbms in access but use a different engine? Or use a visual basic front end?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can't help you with the VB vs A debate - my revs of VB are from way back before M$ included JET (and they were truly useless with databases).

    if you want max performance from an SQL-serving machine - design for it from the very start (crucially: not the same as designing for JET).

    put SQL-Express on your own PC as a mock-Server and develop for that. a simple change in the connection string is all you will need for a future upgrade to real SQL-Server.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2004
    Posts
    11
    Quote Originally Posted by izyrider

    if you want max performance from an SQL-serving machine - design for it from the very start (crucially: not the same as designing for JET).
    In what way is designing for jet different from designing for SQL server?

    And what do you use as the front end user interface for your databases?
    Last edited by Miller84; 03-26-06 at 14:02.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in short you can do your developemnt work in Access, its a very capable db front end. yes it has limitations, yes it has problems. depending on your experince and the tools availabel to you its eer the greatest thing since sliced bread, or an abomination. I find it very easy to generate reports (in some cases I have cgone down the VB route for data capture and Access for reporting)

    personally I don't care: if the customer wants Access, they get Access, if they want VB / VC .NET or whatever we do that.

    as regards a db JET is fine for a few (20..50) users using JET on a local network, like Izy I'd get seriously scared using JET for more than that or over a remote network.

    the key things to bear inmind is to shift processing onto the server, reduce the amount of work done on the client workstation and network traffic. its a different mind set, working with disconnected recordsets, better error handling and trapping.

    if you are seriously contemplating devloping in Access then I'd reccommend that you consider gettign a serious developemnt book. I've gone through several iterations of Access Developer - the most recent I have is 2002 by Litwin, Getz & Gunderloy published by SYBEX

    Izy is right to point out that V5 of MySQL supports tirggers, stoired procedures and functions - as far as I can see it removes any final doubts about using it in any production environment. There are always going to be organisations out there who fall for the FUD argument - which is their choice. In my view a good reason to pick SQL Server is the potential plus it adds to your CV.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Posts
    11
    All of my database experience is confined to the world of access, i have a fair knowledge of SQL but wouldn't know an SQL server it jumped on me. I have done a little with mysql but I am far from a database professional. That aside, I think am I right in saying that if the organisation will benefit from SQL server, there is no point in staying with access because its an easy short term solution.

    SQL server cannot be too difficult to pick up can it? I am a confident designer of small to medium RDBMS's regardless of the final system it runs on surely SQL server cant be too much of a jump for me...or could it be? What are your experiences of making that first jump?

    To fill in the picture, I would expect no more than 10 users to remotely access the datbase over a vpn at any one time...within the next year or so. After that...who knows? As for local direct access its also unlikley that there will be more than 5-10 users at any one time within the next year. These estimates are worst case scenarios, but I would expect that to increase with time as well.

    Im drawn towards SQL server as being a solid investment for the future even though it means a change of environment for me. Yet access would allow a more rapid development.

    What I really don't know is whether or not SQL server is needed yet. I'm in the UK...could anyone give me an idea of the total costs involved in using SQL server?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its sematics but you develop your front end in Access, and the back end in a server.

    I have done applications before using JET to store data, but designed for a server back end (on the basis that at some stage it would migrate).
    you can develop using JET and then use the SQL server upsizing wizard - it does an ok job, but it doens't do the real meat of the process.

    in principle the data back end will be SQL - it could be JET, SQL server, MySQL or whatever, but still SQL. SQL server like all SQL implementations has a few wrinkles (it doesn't do things entirely as per the SQL standard no SQL product does). Even now I still do a lot of writing the SQL in Access and then porting it into SQL server or MySQL. if you make use of stored procedures & functionas then you can isolate your applcaiton form the server choiuce (you "just" need to write the same functions in each database).

    the costs: you could use MSDE / SQL server personal edition. Some organisatioins qualify for reduced licensce fee.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Access and SQL Server

    Personally I like the combination of MSAccess and SQL Server. I'm not a fan of MySQL as I don't really like the gui and I like a database server which has had support for relationships in tables in a couple of versions (i.e. version 7.0 and later) as well as writing tran logs backups every hour. SQL Server is fairly easy to learn and I think it has a lot niceties that you might find difficult to work with in MySQL.

    The advantage of MSAccess is that it is easy to learn and will get you up and running fairly quickly. The advantage of MSAccess and SQL Server is that it will work for your company (big or small). If programmed right (I'm a fan of using unbound forms and writing functions to retrieve/write/update data to and from the unbound forms), MSAccess with SQL Server will satisfy your needs.

    We have over 1/2 million records in our SQL Server database and have no issues with time lags in the MSAccess interface (again, it's all in how you write the code). As stated though, you may find a few quirks (but what would be the fun of programming then and what tool doesn't have a few quirks.) For the money though and the time troubleshooting problems, I personally think you'll find the combination of MSAccess and SQL Server is the best bet for your bucks.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm probably the least qualified person in the universe to respond to this question, but somehow i got sucked into/created this mess so i guess i should attempt a comment...

    In what way is designing for jet different from designing for SQL server?

    in every way

    an SQL-serving engine (MySQL, SQL-Server, whatever...) does stuff that is useful - a dumb JET backend does strictly nothing!

    there is a huge difference between talking to a dumb backend (JET) and an SQL-aware-server: the goal in an SQL-aware-server environment is to make the server do ALL the work and return only the data of current interest to the client (saving bandwidth, time, client clock-cycles)

    possible GOLDEN RULE: your success as a db-coder is inversely proportional to the kB pulled from the server.


    what do you use as the front end
    Access.
    Access only.
    Only Access.

    izy
    Last edited by izyrider; 03-27-06 at 13:35.
    currently using SS 2008R2

  11. #11
    Join Date
    Nov 2004
    Posts
    11
    Ok. Im getting the vibe that the JET engine is really a waste of time because i think that sooner or later the organisations data needs will justify SQL server...even if they don't fully just now.

    So let's assume I opt for the SQL server engine which might be as well if the database is to be accessed via a VPN in many cases. Can the user interface really be done justice using the vb tools supplied with access or should I consider another option such as PHP or ASP or anything else??

    Edit: I almost missed your comment about "Access only" izyrider! Can the whole interface really be done in access??

    Is a web-based broswer interface the most suitable given it will be access from many machines locally and over a VPN?

    Thanks to all for your insight so far! I'm starting to get a better idea of where to go with this.
    Last edited by Miller84; 03-27-06 at 15:19.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes thats what we have been trying to say Access is a front end tool - full stop
    the data storage method is up to you, it can be JET the default storage method for Access, or it can be anything else (SQL Server, DB2, Oracle etc...)

    Access as a front end RAD is a good product, some may say great, some may not rate it. In my view it takes a lot to beat Access. yes it has flaws, yes it has weaknessess but if you want a RAD tool and are prepared to accept or workaround its oddities then it is a very good compromise.

    They key thing to bear in mind is that Access is really geared towards the novice user using JET, so you have to do a lot more work if you are using a server product - no biggie, it just needs a little more work. Some of the glossier, glitzy things are not neccesarily appropriate for a server environment. Depending on whatyour application is it could run quite happily using JET the cut off pint is around 15..50 users there is no defintive point where it croaks. You can develop in JET and then port it to SQL server as required, suing the upsizing wizard - however if you want to go down to that route then you ne3ed to design as if you are using a server from day one (ie use discconenected recordsets, unbound forms etc.....)

    As Izy says the real key is making the best use of eacdh element. dump as much of the data processign on the server, pump only what you need to up and down the network, pay carefull attention to query design and tuning

    in the GUI / Front End be porepared to do more error handling, dont' be too tempted by using bound forms.

    Above all get a really good book that concentrates on using Access with a server

    it is entirely possible to develop it using a web front end, however it will probably lack the power and flexibility of an Access App. Unless you need to deploy the app to people outside your organsiation, or you have a licenscing issue on copies of Access then I hesitate to reccomend that you develop a web based app/
    HTH
    Last edited by healdem; 03-27-06 at 17:57.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Access

    I had the urge to throw in this comment about MSAccess. If you decide to use only Access and (Jet) tables, then start you're programming out right. By that I mean write your code like it was taught in school. Use the functions capability in modules to write functions to retrieve/write/update/delete data to and from unbound forms. I hear people say "But Access can only allow XXX many users." But I have to say, it's all in how you write the code. Having an unbound form will allow a user to sit "and they almost definately will" on a form with a record retrieved. It's kind of like using object oriented programming techniques and will save you some hassle in the long run. If you're doing some web development, it might be a different case though. If you decide to go the route of using SQL Server (or MySQL) as a backend for the tables - fantastic! I really, really like the spawning an mde method (see my posts on spawning a database which has the vb script to do this). This works extremely well for us and it allows us: 1. To copy a new mde file without having all the users close out. 2. To see who's in the mde by just looking and seeing if an *ldb file is opened with that user's name. and 3. See when that user last opened the mde file by looking at the creation date. Only problem though is that sometimes users make new shortcuts on their desktop and they sometimes like to select the mde file with their name associated with it when they really should be selecting the vb script which "spawns" the mde file (that's what a Main Menu mde file is for). As a last note, there are a lot of neat MSAccess "goodies" floating around. The Access books by Getz (Sybex) as mentioned previously are a great way to start and usually have an attached disk with a lot of these goodies.
    Last edited by pkstormy; 03-28-06 at 10:39.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •