Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Microsoft Access - Use it!

    I know this message is going to probably piss a few people off but I really don't care. As I've been on this site for a while now, I've read a lot of crap about how MSAccess isn't an enterprise solution or that it has limitations of only 3-5 users and has this problem or that problem, or the database keeps getting corrupted, etc..

    This is pure bull! If your company is looking for a good programming "TOOL" (as I've been corrected on before that Access is just a tool and VBA is the language - true), but MSAccess is an EXTREMELY good solution (and yes that includes an ENTERPRISE solution!) I've programmed in a lot of languages using a lot of "TOOLS" (i.e Visual Basic, Foxpro, Delphi, Dbase, Powerbuilder, etc.) and have yet to find a solution in which I can get an application running in 1/4 of the time it takes to program using any other tool. I stated this several times and I'll state it again: "It's all in how you set up the tables and how you write the code!!!"

    In school we were taught the basics of how to design functions which write, retrieve, and update records. If you stick with this philosophy in MSAccess, you won't have to worry about database corruption, etc. I would like someone to explain to me the problem of using UNBOUND fields in an Access form and utilizing functions to write, read, and update to these forms (if there's anyone out there who does this.) I would even go as far as to challenge anyone in getting an application up and running in their "tool" verses me using Access. Yes I would agree that using BOUND forms will limit you to 3-5 users in an Access application and cause problems and that a great solution would be to use Access as a frontend and have SQL Server or MySQL as a backend. But this DOESN'T mean you can't use just Access tables and write the code correctly. If you did the same in another programming "tool" and wrote poor code, you'd have similar limitations or other problems!

    I've also read a lot in these forums about how programmers will split the database and use these type of techniques. I repeat, what is wrong with writing UNBOUND fields in a form and programming events around these fields?

    This forum is used to help programmers in all languages but I really get sick of hearing how Access is not a good enterprise solution or not good at this, or that or that, etc. Pure bull! You have problems in every tool used and after using several of them, I chose Access because I can do things faster and better in Access than any other tool on the market. Plus the fact that someone with very little programming skills can get an application running in no time at all. The combination of queries and code is awesome if you know how to utilize them correctly!

    If you're anti-microsoft or have only been programming in one language all your life, or have learned a little about this language and a little about that language, you're going to probably disagree with all of this. But support for Access has grown (like other tools) and there are so many good things about Access (not that I'm just biased towards Access, I just know how to use it to it's full potential), that you can't discount it. It sickens me that taking an Access class as I've had my subordinates take, only teaches them the basics and not how to use the true power of Access (most won't even teach you how to write functions in modules or API calls.) It also sickens me when I see and have to convert other Access programs written so poorly that I can't help but wonder what kind of drugs they were on when they designed the tables and wrote the program.

    I'm sure I'll get some feedback on this but after developing hundreds of applications, I've chosen Access as the tool to use and I would encourage anyone else to use it as well!!
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Here Paul, have a drink!

    I agree with you. We've put our little old Production Planning and Forecasting Access system up against many of the top off the shelf software packages available. The planning engines are no match in speed or capability. I perform tasks in Access that our IT people can't duplicate in our legacy Informix system.

    Edit: I will admit that having SQL Server as a back end and Access as a front end did make my life a whole lot easier.
    Last edited by RedNeckGeek; 02-16-05 at 17:07.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Dec 2004
    Posts
    277
    I have 10 users running access within the office, plus maybe another 10 from outside the office

    I haven't had too many problems, except for the slowness(which I've been told is just the access overhead).

    The slowness is really the only thing I can complain about otherwise it's doin the trick for me

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I have written in over 25 computer languages and have used 10 different types of databases from Oracle to Dbase to my-sql, access and the list goes on. I have been programming for 25 years and Microsoft access is a lightweight database solution thats very good at what it does, but I would NEVER build an enterprise solution on it. I would use one of the real database engines for any major database. Access (with lots of backup) is fine for small departmental databases, but I wouldn't bet the company on it. And as for its query capability being awesome, it's fairly obvious that you havent used something like oracle. It puts access to shame. As for building applications, I can build a solid application using Oracle Developer just as fast as an access application. While I admit that Oracle or sql-server is a lot more expensive then access, they are real database engines and access is NOT. As a test, have 2000 people use an access database at the same time and then have 2000 people use an Oracle database and see the difference.

    P.s. I am not trying to start a flame war, but I have enough experence to make a judgment for myself on your statement.
    Last edited by beilstwh; 02-16-05 at 18:03.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    I agree 100%!!!!

    Yes Access is an EXCELENT progamming tool, even as an enterprise solution i'm sure. For several reasons:

    1- Anyone (with some knowledge of course), can modify the structure of the application made, so that the needs of the company can be satisfied. Today, companies are flexible not rigid, and the software must acomodate to the company not the reverse.

    2- We can get the same or better results (Information needs) quicker and cheaper, as if implementing an ERP solution like the ones there are in the market. (SAP, BAN, NAVISION, etc...)

    3- Even with poor code and poor design structure of the tables, if we care of everyday backing-up th DB and for instance at the end of the year we change the mdb file for a new one, will work just fine. (I have that experience - the first DB i made, still runs, but every 2 years i have to collect the sums of the data, for example: stock entries and stock outs, the query "Stock" exports the data to a new database. The containing data in the "Stock" query is much less that the data containig in the "stock entries" and "stock outs"). Of course even at the end of the year it is annoyng do this.

    4- If it is more or less well coded, we can get good results.
    Two years ago i made an 1/2 ERP solution (Comercial/Producion - Manufacturing/Maintenance) to my company, there are 5~6 users introducing and querying data at the same time. I didn't use unbounded forms (I didn't know at the time, now it's too late, maybe the next), but i presenced the server wenting down for let's say 10 times in this 2 years, and the DB still runs with no problems. I also know that the way it is coded and structured it may not handle a server crash when the mdb file have 300 Mb. But i have time to think about that.

    5- The easy it is to axport to other Office applications.

    etc,etc,etc...

  6. #6
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    OK guys, have read a lot here and do feel, on the whole (with my limited expereince) that Access and it's VBA functions are very handly tools.

    I would agree with pkstormy though and that there's very little out there to really unlock the power of Access. As an example, the place I work for is a little tight on the training, so I'm nearly totally self taught in Access, with a moderate VB background and strong maths. To be honest, I don't know the difference between an Unbound and a Bound form. My SQL is no where near where it should be after the time I've been doing this job and do feel a bit out of my depth in a forum with you guys!

    So, for a bit of an inexperienced guy, where do I get that knowledge that pushes me up to where you are? MS Press etc is difficult to get anything function based done and the higher funstions I haven't really ever found.

    If i have a complaint about MS Access (Other than trying to use ODBC to a Sybase {black box environement} and our SQL server which is at best slow), it's documentation. If you;d like to point me in the direction of some good books, or learning resources please fell free as I want to learn.

  7. #7
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by daveo61
    ...If i have a complaint about MS Access (Other than trying to use ODBC to a Sybase {black box environement} and our SQL server which is at best slow), it's documentation. If you;d like to point me in the direction of some good books, or learning resources please fell free as I want to learn.
    Agreed. I'm just starting Access too (and I think I know what bound and unbound is ) but most tutorials are somewhat one-sided.

    Dave061, a few tutorials I HAVE found that (together) taught me a lot are these:

    http://www.functionx.com/access/index.htm (Very nice, graphical tutorials, explaining the basics and more of Access. However what I missed in this one is the connection to SQL/VBA and how to optimally use them with Access. The site has a VBA-tutorial too btw, also very nice, but also stand-alone)
    http://www.fontstuff.com/access/ (especially usefull for connecting Access/VBA and SQL, very hard to find elsewhere)
    http://cma.zdnet.com/book/masteringaccess/index.htm (One of the last I found, VERY extensive, well-written, but somewhat old and somewhat none-graphical/unattractive...)
    http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html (Very well written, nice graphics, and actually has chapters about VBA/SQL! However, I found it somewhat difficult to grasp every step they do, it goes pretty quick sometimes)
    http://www.mvps.org/access/tencommandments.htm(speaks for itself )

    I've come across these tutorials time and time again, I think these are the best available on the web atm.

  8. #8
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Thanks for the tip Avalan, I've just quickly trawled through the pages and have now found what Bound and Uubound are in under 5 mins, so all good.

    Was what I thought it was to be honest, thought there must have been more to it though. Bound = Has a datasource other than from the form. Unbound = No data source. Simple.

    Thanks again.

  9. #9
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by daveo61
    Thanks for the tip Avalan, I've just quickly trawled through the pages and have now found what Bound and Uubound are in under 5 mins, so all good.

    Was what I thought it was to be honest, thought there must have been more to it though. Bound = Has a datasource other than from the form. Unbound = No data source. Simple.

    Thanks again.
    As I understood it, bound means a combobox of some sort that (through a SQL-ish statement) gets data from a table with a relationship to the main table, and the big problem with this is that if you leave your combobox unlocked and active, and the user changes the value, it is directly entered in your database, messing up the data. Am I correct, guru's? I'm pretty sure, since I've already experienced this

    An UNBOUND box is a simple textfield, which displays information due to a function that retrieves info from the databases. This is read-only; meaning you could change it (if the box is unlocked and active) but it can never be stored into a table, since its just a txtfield displaying something (unbound to the table).

    Correct?

    This means I have to write some code to display the right info in the right txtfields shame, would have been nice

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pkstormy
    I know this message is going to probably piss a few people off but I really don't care. As I've been on this site for a while now, I've read a lot of crap about how MSAccess isn't an enterprise solution or that it has limitations of only 3-5 users and has this problem or that problem, or the database keeps getting corrupted, etc..

    This is pure bull! If your company is looking for a good programming "TOOL" (as I've been corrected on before that Access is just a tool and VBA is the language - true), but MSAccess is an EXTREMELY good solution (and yes that includes an ENTERPRISE solution!) I've programmed in a lot of languages using a lot of "TOOLS" (i.e Visual Basic, Foxpro, Delphi, Dbase, Powerbuilder, etc.) and have yet to find a solution in which I can get an application running in 1/4 of the time it takes to program using any other tool. I stated this several times and I'll state it again: "It's all in how you set up the tables and how you write the code!!!"

    In school we were taught the basics of how to design functions which write, retrieve, and update records. If you stick with this philosophy in MSAccess, you won't have to worry about database corruption, etc. I would like someone to explain to me the problem of using UNBOUND fields in an Access form and utilizing functions to write, read, and update to these forms (if there's anyone out there who does this.) I would even go as far as to challenge anyone in getting an application up and running in their "tool" verses me using Access. Yes I would agree that using BOUND forms will limit you to 3-5 users in an Access application and cause problems and that a great solution would be to use Access as a frontend and have SQL Server or MySQL as a backend. But this DOESN'T mean you can't use just Access tables and write the code correctly. If you did the same in another programming "tool" and wrote poor code, you'd have similar limitations or other problems!

    I've also read a lot in these forums about how programmers will split the database and use these type of techniques. I repeat, what is wrong with writing UNBOUND fields in a form and programming events around these fields?

    This forum is used to help programmers in all languages but I really get sick of hearing how Access is not a good enterprise solution or not good at this, or that or that, etc. Pure bull! You have problems in every tool used and after using several of them, I chose Access because I can do things faster and better in Access than any other tool on the market. Plus the fact that someone with very little programming skills can get an application running in no time at all. The combination of queries and code is awesome if you know how to utilize them correctly!

    If you're anti-microsoft or have only been programming in one language all your life, or have learned a little about this language and a little about that language, you're going to probably disagree with all of this. But support for Access has grown (like other tools) and there are so many good things about Access (not that I'm just biased towards Access, I just know how to use it to it's full potential), that you can't discount it. It sickens me that taking an Access class as I've had my subordinates take, only teaches them the basics and not how to use the true power of Access (most won't even teach you how to write functions in modules or API calls.) It also sickens me when I see and have to convert other Access programs written so poorly that I can't help but wonder what kind of drugs they were on when they designed the tables and wrote the program.

    I'm sure I'll get some feedback on this but after developing hundreds of applications, I've chosen Access as the tool to use and I would encourage anyone else to use it as well!!
    Well well well ... Enterprise eh? Since when can a database with a MAX of 255 concurrent connections be even considered to be enterprise level??? It's not even departmental level ... This is on a pure connection basis - say you need a 1000 or even 10000 concurrent user access what are you gonna do? I know: not use Access. It's flatout not designed for that level of usage ...

    I agree with most of your other commentary on the state of design and programming practice (or lack thereof) ...
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    It is true that a bound form can have unbound controls, but what is really meant by an unbound form is that the recordsource property of the form is empty. The form is populated via code, using a recordset to get data.
    The beauty of that is:
    1) Any data entered by the user can be validated before it is saved to the DB.
    2) If the system crashes, the user may lose what he has entered in the form, but half filled records are not put in the database - causing possible corruption of the DB.

    A combobox can be unbound (control source is empty), and still query data for it's values (by putting a SQL statement in it's Row Source property). That just supplies the values you want the user to choose from.
    Inspiration Through Fermentation

  12. #12
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by RedNeckGeek
    It is true that a bound form can have unbound controls, but what is really meant by an unbound form is that the recordsource property of the form is empty. The form is populated via code, using a recordset to get data.
    The beauty of that is:
    1) Any data entered by the user can be validated before it is saved to the DB.
    2) If the system crashes, the user may lose what he has entered in the form, but half filled records are not put in the database - causing possible corruption of the DB.

    A combobox can be unbound (control source is empty), and still query data for it's values (by putting a SQL statement in it's Row Source property). That just supplies the values you want the user to choose from.
    Hmke... understood.
    However, if I want a combobox to show data from a related table (not the form's source that is) then I'll HAVE to fill in the control source... or?

    I've just checked it, I dont have to! omg! Ok... but then the thing I have to do is to code in a way all the other fields change accordingly when one of the comboboxes is changed.. Hmmmm one step closer yet again

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Well well well ... Enterprise eh? Since when can a database with a MAX of 255 concurrent connections be even considered to be enterprise level???...
    I am aware of an application developed in MS Access (97 I think) which is used by multi thousand users concurently deployed over many remote offices connecting to a back end SQL_Server. Its a fairly well known multinational. Givent their size & turnover I think they'd be quite offended if they were regarded as 'not even department level'. My understanding of the 255 connections is if you are using the workgroup securtiy services. As there security relies on other technologies they are not limited by the 255 connection limit, as each Access front end connects driectly to the Server backend itself. It is my understanding that they are mid way through splitting the application into 2 and deploying a screen query / customer services section into a web based interface but the intensive data capture function is and will remain a MS Access front end.

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by healdem
    I am aware of an application developed in MS Access (97 I think) which is used by multi thousand users concurently deployed over many remote offices connecting to a back end SQL_Server. Its a fairly well known multinational. Givent their size & turnover I think they'd be quite offended if they were regarded as 'not even department level'. My understanding of the 255 connections is if you are using the workgroup securtiy services. As there security relies on other technologies they are not limited by the 255 connection limit, as each Access front end connects driectly to the Server backend itself. It is my understanding that they are mid way through splitting the application into 2 and deploying a screen query / customer services section into a web based interface but the intensive data capture function is and will remain a MS Access front end.
    AHA!!!! And this backend server is??? SQL Server ... NOT ACCESS. Your argument is toast ... If the back end where Access I'd be wanting to know more ... Like how they were able to bypass the .ldb file (which as far as I know you can't) or what mdw file they're using (probably system) ...

    Also, your keyword here in FRONT END ... most likely they have the front end deployed on each machine thereby each frontend db has at most 1 user connection ... And as we all know SQL Server has a license level that allows for effectively unlimited users ...
    Back to Access ... ADO is not the way to go for speed ...

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I agree with what you are saying if you qualify it as "Access, using JET cannot scale". I think there should be little argument with that statement, there is argument at what level of users JET starts to hit the wall, I have seen people quote 3..5 users, some go as far as 40 to 50 users. There is no doubt that at some time JET will crap out. But that doesn't make Access the limiting factor or inappropriate for organisations of any size or scale. So I would still contend Access is a enterprise capable application.

    It may be sematics, but people read that and immediately jump to the false assumption that the limitation is Access not JET. The reality is that when JET runs out of steam if the appplication is designed fairly carefully then you can redesign it to talk to a server with the minimum of effort. Now that is a huge bonus to a small project - the re-assurance that when you need it more power is available. (yes all the queries need redesigning and tuning but the real effort in most applications is the interface and the hidden cost of training people how to use it.)

    Flatly stating Access can't hack it is at best untrue and at worst I would contend disingenuous - I don't mean that to start a flame war, if it does I'll live with it.

    Access has some great RAD tools, like most development tools it has plusses and minuses. In my view it has way many more plus points than minuses. If anythin its biggest plus is also it biggest minus - that its realtively easy to prototype and get something going. It encourages the hard of thinking to evolve something which sort of works but becoems a nightmare to maintain. Those self same tools encourage competant developers to develop worthwhile & professional applications in a fraction of the time required for other tools. Like earleir respondants I too remember the pain of developing multi user applications with products such as dBase, Paradox, CICS-Cobol & APS-Cobol, VB, C++ and Delphi and so on....

    To date, my vote remains with Access & VBA over so called 'proper' languages such as C, C++ and others.
    Last edited by healdem; 02-17-05 at 12:02.

Posting Permissions

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