Fear of NOT upsizing to SQL

[advancesystems]

Hi all, I develop reasonably complicated network solutions with a split database scenario, Typically My clients have 4-5 users. This works fine and performs well, however I am constantly being reminded to upsize to sql server everyere, on websites etc. I have a fear that my databases may not be good enough or that MS jet will disappear of the market soon, I like the way I do it and I dont want to upsize to sql server. I am also afraid that my users may reach the limits of access and I will be forced into sql. I havent had the chance to really get a realistic answer to the questions.
1 - Just how many users can u expect to run over a split access 2000 database on a LAN before performance is bad and corruptions occur. is it 5?, would 25 or more users even be possible?
2 - Would I be better to upsize and develop all my solutions that way from now on??.
The reason I ask is that I am starting a new project and I dont want to start with ms jet and end up redoing it in SQL. The only reason I am thinking like this is because microsoft seem to be pushing this like crazy on everyone.

 

[mission2java_78]

From a bit of experience of access you'll learn to like the foundation it was built on. Meaning access was written by MS to be suited as a database environment. So its very very easy to write code, create forms, create reports, queries, and easy access to tables. So yes it is easy, but NO it is not a good transactional database app that can server more than say 5-10 concurrent users. i have experienced the pains of access through various multi user client server apps. This is what I did...I got a solid understanding of databases from Access ... so in no way would I say I didnt gain a lick out of access...cause I'd be lying. I learned a LOT of vb which was basically vba because of access. If you know vb or at least enough vba...Id drop access for your newest app, write your front end completly in vb and connect to a SQL Server db backend. You'll thank me later... of course learning anything new is difficult..making the adjustment is very time consuming..but once you have you'll realize why you have. Plus you have .EXEs

Jon

 

[Pat Hartman]

I disagree totally Java. You really need to distinguish between Access the development environment and Jet the database engine. I wholeheartedly agree that Jet is inappropriate for large amounts of data or large numbers of concurrent users. But that doesn't mean that advancesystems should abandon Access as a development environment. What it does mean is that EVERY app should be developed with the possibility of switching from Jet to something else at a later date.

Almost all the apps that I develop use something other than Jet as a back end. I actually prefer Oracle with DB2 as a close second choice. SQL server is a distant third and Sybase is out of the park. That covers the major players.

So, the way to prepare for upsizing is to start by using proper naming standards for your tables and columns. Do not on pain of death use embedded spaces or any special characters in your names. Be consious of names that are reserved words. Some databases refuse to accept Description as a column name. Some refuse to accept Desc. Lean toward multi-part names to avoid potential conflicts. So use OrderDesc instead of just Desc. Become aware of the various restrictions of the potential back end. If it is SQL server, read its naming standards. Read all the knowledge base articles on obtimizing client/server applications. If the suggestion is good for client/server it will be good for you too. NEVER base forms or reports directly on tables. ALWAYS base them on queries that include where clauses to restrict the number of rows returned.

Then create a relinking module that will let you swap out the Access back end and replace it with links to some other database. This has worked fine for me (I always promise myself that I will write this routine but I haven't yet so I manually relink the tables). I can swap a set of Access tables for a set of DB2 tables in under a minute and not have to change a single line of code.

Microsoft is pushing SQL server but they will loose the desktop market if they continue on this path. Small companies are simply not going to buy into SQL server and individuals are not either. What are your Church and your daycare center going to do, buy SQL server. I don't think so. They'll switch to something else. Believe it or not, Access does have competors in the desktop market.

 

[mission2java_78]

I don't think that access is totally out of the question. However I do feel that just from seeing everyone's problems, errors, corruption that access is too buggy. Whether users use access or sql server its still money going to MS. So I dont think MS is competing with itself. However saying that upsizing is really simple or using that stupid upsizing wizard is not that simple. I've written several apps where I have followed the correct naming conventions and so on only to have my code break in a vba debug window on opening a recordset. Tables and the way records are stored is not the same in SQL Server as it is in Access. The datatypes themselves dont match up. Does one really need to use bigints for number datatypes? You have to be careful with SQL Server since access by default saves a record and assigns a Record ID right when a user has entered text into a field. However sql server does not assign ID numbers unless a record has been saved. There are many more pitfalls with upsizing an access db..its not cake.

Jon

 

[advancesystems]

Thanks for all the insights, I suppose I was tryng to determine the point where access becomes useless over a LAN. For example just how many users will access (split) really work comfortably with (10,20 or even more). Reading all the comments above I am also beginnning to see that access may cause other problems with corruptions and bugs etc.
I am coming from a background where I use FileMaker Pro. I have never had any problems with it but access is more powerful and I began to consider moving to access. Maybe I would better to stay where I am .