Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Question Unanswered: Has my application outgrow MS Access?

    Hi everyone.

    I have developed an application used for maintenance of production machinery and transport.
    The application is quite heavy (300 forms, 350 query, 150 linked tables, 100 local tables and a lot of VB code) Application size is approximate 80Mb.
    The size of the database is, after a year, 10Mb to 20Mb. Depending on customer size (no OLE objects, only links)
    The application is developed as a multiuser system that can run as client/server or in a terminal server environment. (Citrix)

    Some new customers put a question mark on the application platform of my application. They got the impression that MS Access is designed for small custom designed applications.
    As I manage to solve all required issues in MS Access Im happy to continue use MS Access. I also cant see any reason to change due to capacity problems on database.

    My question is. Should I consider migrate application development tool and/or database platform? - If so, what do you recommend?

    Sincerely
    Vestigo

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    access is fine as development tool
    as you aopplicatuin grows / expands, number of userts increases then its fine to switch the data store from JET to a server datastire (SQL server wold eb the obvious choice, but any server SQL would do)

    I think that its potentauilly a mammoth task to even consider shifting th ecode to another environemnt

    Im noit even convinced that shiftign to another environment is sensibel.. unless say you users want to shift to a light clioent profile and use web services

    a lot of professional developers sneer at Access.. its actaully a hybrid.. the File server modle of JET is fine for very small apps (10..15 maybe more), but Access as a rad works fine, and is scaleable. Im aware of one busienss ap that uses Access as a friotn end to a serious worldwide data netwrok and has multi thousand users. they are quire happy with Access & VB.

    if you do decide to switch the backend to a serve rproduct then you probably need to recodce the app in any venet to get the best out of the server.

  3. #3
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Im quite happy with MS Access

    Thanks for your input ”healdem”

    As mentioned, I’m quite happy with MS Access.
    OK …. It sometimes get corrupted, it crash for no obvious reason and I loose some hours work. But I have learned to deal with it, I do not even swear anymore when I need to do some work over again.
    After rebuilding my program to run in a terminal server environment, the application can be used from multiple locations. No need for a web solution.
    Another thing is integration to economical system, CRM systems etc. No one say that they can’t interface with MS.
    Maybe what I need is some references to other success stories where MS Access has been used. Can anyone help?


    Opinions and inputs are appreciated.


    Sincerely
    Vestigo
    Last edited by vestigo; 04-04-08 at 12:31.

  4. #4
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Im quite happy with MS Access

    PS!
    If I later decide to migrate to something more “professional” I will use my application as a “clay” model and let others take care of the programming. :-)
    Last edited by vestigo; 04-04-08 at 12:28.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Man I hate the "Access is a poor-man's development tool and not professional" type of quotes. (But you wanted an opinion so here it is...)

    Your current MSAccess application just needs to be upsized to utilize SQL Server tables (and perhaps using some unbound MSAccess forms). I gaurantee your users will be happy with a very FAST, efficient application and you will be happy because you can easily make changes that don't take months to do (as you've probably already discovered with using MSAccess.) If you plan on going to another development tool other than MSAccess, plan on spending a lot of time learning to do in that development tool what you already could easily have done with MSAccess in a few minutes. In your exploration of a different development tool, you may find another tool where you can do "one" or "two" things easier or faster than Access, but you'll also discover that a lot of the simple things you can do with Access are more complicated (or non-existent) with that new tool and overall, you end up spending more and more development time with that new tool trying to do what you could have done within minutes in Access.

    THE MAIN REASON MSACCESS IS NOT CONSIDERED A "PROFESSIONAL" DEVELOPMENT TOOL IS BECAUSE SOME DEVELOPERS SIMPLY DO NOT KNOW HOW TO DEVELOP PROPER CLIENT-SERVER APPLICATIONS WITH MSACCESS AND (I want to say Naive developers) NEED TO LEARN (OR UNDERSTAND) HOW TO DEVELOP IN MSACCESS PROPERLY FOR CLIENT-SERVER APPLICATIONS!!! (I probably pissed off a few developers here - no offense, but I really feel the need to state my point here with using MSAccess properly for client-server programming.)

    The trade-off is simple - use what may be considered a "non-professional" tool like Access and get things done quickly. Or use another tool advertised (and it IS just advertising) as more "professional" and spend a lot more time developing.

    Of course in a Client-Server environment and using MSAccess jet tables and bound forms, you're going to eventually have issues with Access as your application grows in a client-server environment (Maybe Microsoft should have removed the ability for users to create jet tables and made MSAccess like Visual Basic.) It would be exactly the SAME with any other development tool if you created tables (AND a front-end) in that tool!! There is not an all-in-one development tool which can match against MSAccess and SQL Server combined (or other db server similar to SQL Server) for a client-server app. If your current mdb keeps getting corrupted, there's a reason (perhaps table design or the coding or trying to use jet tables in an app that really needs to be upsized to a server db.) As you've discovered with Access, you simply compact/repair the mdb (which usually takes less than a minute). It would be worse with another development tool which is not as forgiving (for example, think about a 4GL application or another application where it might take 6 hours to compact/repair and recompile the code - and your new code fix didn't solve the problem and you must do it all again.)

    MSAccess used as a "Front-end" (and I'm not comparing it to web tools here) in a client-server environment is simply the BEST development tool you can find (period!) With SQL Server as a backend and MSAccess as a front-end (using unbound forms), I developed applications which retrieved/updated millions of records within seconds and worked flawlessly (ie. the entire Midwest Energy Conservation data and the entire US Energy Star Loan application (among many other large dataset databases).) I even had the entire Experian Credit Card dataset running through a front-end Access application.

    Using PROPER coding techniques with Access, (and of course proper backend table development), unbound MSAccess forms, and procedures/functions to Retrieve, Update, Delete data (with a SQL Server backend) - exactly like other functions learned in computer classes in school (for ALL the other cleint-server program development tools), will give you an application which will last years and years.

    Again, IF Access is used correctly, it is BETTER than any other development tool! (I'll stand by that statement.)

    So when I hear other developers tell me "I develop programs with a 'REAL' development tool such as SQL Server or Oracle", I can only smile and ask "So what do you develop your front-end in?" (ummm...I mean I ALSO develop in SQL Server - for the BACKEND!). User's don't see the backend but are mostly concerned with the ease of using the frontend and the safety of their data. (sorry but I tend to get a tad defensive about being classified as a non-professional developer' simply because I use MSAccess as a front-end tool.) As a "non-biased" (ha!) developer who has worked with many, many other development tools, Access has always made my life sooo much easier.

    Among the many other development tools I used, I worked with 4GL once (with a Progress db backend). What I could do within a few minutes in MSAccess literally took days to do in 4GL. As much as I wanted to take a baseball bat to the other 4GL developers head showing them how easy it was in MSAccess, I grit my teeth and subsided to the thoughts of "If only they learned something OTHER than just 4GL but these developers were raised to do things the LONG way (they actually spent weeks making simple changes and thought that was efficient.)

    The best advice I can give you in using MSAccess is that you have to think like Microsoft and not try to over-analyze a task but remember "Oh, Microsoft already thought about how this could be done easily." If you get SQL Server, the MSAccess upsizing wizard will upsize your Access tables in minutes and you'll have a better client-server application (don't get me wrong though, as with any new tool (and I have had my times of frustration with Access/SQL Server), there is a learning curve to using SQL Server with Access. But your time will be well invested and minimal.)
    Last edited by pkstormy; 04-04-08 at 14:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Totally agreed with you PK

    I have developed in multiple environments too and Access is by far the superior product... even Access 2007 rocks by comparison.

    The only thing that Access sucks with is source-code security. MDEs aren't secure enough for my liking. If Access could just generate secure .EXE's it would completely answer all my needs for a development tool. Access 2007 is worse though, needing multiple changes to keep developers happy... and if Microsoft continue to ignore developer concerns regarding it's future development, I don't know how much longer Access will be such a great and dominant development tool.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You're right StarTrekker - I think Microsoft is walking a fine line with Access 2007 from what you've described it as. I always thought Microsoft's goal was to integrate more and more MSACcess with SQL Server. But from the sounds of what you said about 2007, it doesn't look that way.

    I'd hate to see Access "die" similar to the way Visual Basic sort of "died".

    I still remember the day when I was programming in dbase and went to the computer store looking for another development tool and the clerk threw me a free copy of Access 1.0 and said "Here, try this - it might help you." One day with Access and I was hooked.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think Access 2007 is fine with SQL Server... Here's a summary of where I believe the problems to be in Access 2007... compared to its predecessors:

    http://www.dbforums.com/showpost.php...1&postcount=11

    It's still a great product, but I believe it to be a step in the wrong direction for developers.
    Last edited by StarTrekker; 04-06-08 at 10:06.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239

    Thumbs up

    Hi folks

    Thanks a lot for the engagement in my thread.
    I conclude that I should change my Back-End to SQL server rather sooner than later.
    I’m quite happy there is agreement that MS Access is a great Front-End tool.
    This encourages me to continue develop in MS Access.
    (A link to this thread will be send to all of my customers that put a question mark to selection of development tool.) :-)

    Next, are there any good description/threads how to migrate from MS Access db to SQL server?

    PS! I need to do some research on my crashes, (There isn’t enough memory….)
    I will create a new thread if I can’t find a solution to my problem.


    Thanks again.

    Sincerely
    Vestigo

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not sure there is a thread or forum dedicated to changing to a server backend... mind you some of the changes are required in the applciation, rahter than the SQL per se.

    If you don't already: Id stronly reccomend you get a copy of the Gertz et al "Access Developers Handbook" for the version of Access you use, published by Sybex..... it certainly helped me (and still does) through the minefield of developing Access front ends.

    I think that designed well enough, and at 300+ forms etc you should be at that level) you coudl deploy your app as two versions.. one thast talks to JET as is, one that tlaks to a server backend. those who are sneeraingl at JET can pya for the SQL licensce, those that are on a budget can use JET, those that don't care.. depends on utilisation.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The JET users will be envious of the speed and stability of the SQL users

    As for converting to SQL, there are squillions of helps out there, just do a search for it, I'm confident you'll find enough to get started. Take a first look at the upsizing wizard, get yourself access to an SQL server (or download and install the SQL Server Express 2005 thing)... that will get you started.

    Then look at linking to your SQL server from the Access front end. You might want to check out this:

    http://www.dbforums.com/showthread.p...05#post6315605

    I might just create a simple guide on my website for conversion to SQL tbh... it's something I get asked about quite a lot.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Nov 2002
    Location
    Norway
    Posts
    239
    Only having such helpful and engaged members on this forum encouragement me to continue using MS Access as front end. I’m enthusiastic over all help I have got from you all. :-) :-) :-) :-)


    SQL Server Express 2005: http://www.microsoft.com/sql/edition...s/default.mspx
    “Free to download, free to redistribute, free to embed…..”
    What are the limitations?

    Sincerely
    Vestigo

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't know what the limits on the various versions are these days.. it sees to chop and change with each release of competitor versions.

    There used to be a limtied number of users (around 5 I think) on the base version, although this suggests there are no limits.. mebbe Im getting confused with MSDE circa 2000

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding memory crashes...

    Little free RAM will bring down any application. A few things I looked when utilizing MSAccess (and SQL Server).

    SQL Server itself is not a memory hog but does work efficiently with a lot of free RAM. One setting I discovered NOT to use with SQL Server was to let it use ALL the RAM available on the server (which you can configure SQL Server to do). Since this SQL Server was used on a computer with the operating system and other programs, letting SQL Server goble up all the memory eventually led to a crash of the server. Set up SQL Server so it can only use a set amount of free memory (which it defaults to do). SQL Server has this wonderful tool called Trace which let's you monitor the transactions and how many resources transactions eat up as well as a wealth of other information.

    In regards to MSAccess, keep in mind that there is a difference with the mdb size (which usually averages between 2-20 megs with a higher side of 50 megs - I use 50 megs as a cut-off point for my mdb's but have on occasion had a much larger mdb size with jet tables, more data, and more forms/reports) and how much memory is used (i.e. mem usage in Task Manager) when the mdb is running (which averages 15,000-30,0000k with 50,0000k I personally use as a cutoff point).

    I believe it was MSAccess 2000 which changed the way MSAccess saves its forms and having a lot of forms can dramatically increase the mdb size. Not a bad thing but something to be aware of.

    For Access Jet tables, you have a 1 gig data size limit for 95/97 and a 2 gig data size limit for 2000+ although approaching that size is a serious time to consider upsizing the data to SQL Server (especially in a client-server environment and hence - the MSAccess is not "professional" stigma.)

    A trick that I like to do when utilizing an mdb is to open Task Manager and monitor how much mem usage MSAccess uses when the application runs on that machine. What you want to watch for is mem usage which continuously grows and grows and grows. For example, in the mdb, opening a recordset in code and never closing that recordset (and opening more and more recordsets in code), will show you a mem usage that will continuously grow and grow. This is not a good thing and an ever-increasing mem usage should be concerning (as with any application). I also found that defining new recordset names (i.e. instead of always using something like dim rs as adodb.recordset) and new variable names tends to lead to a growing mem usage.
    Last edited by pkstormy; 04-08-08 at 13:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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