Discussion on:
View:
Show:
Have you worked with both these products? Which one comes out on top in terms of meeting your needs?
What is a multiple user? I suspect you meant "several users" or "multiple users" or perhaps "several simultaneous users".
A veritable plethora of several multiple users! Thanks for the catch; fixed now.
Jody
Jody
Sorry, I wasn't able to find where you were referencing "multiple user", but I would have meant "multiple users" in any case. Thank you. Tim
In the past, I have found that Access works rather well for small business that are capturing a small amount of user data. It also works well in terms of reporting for the data...the user interface for designing queries abstracts you from having to know SQL. However, in terms of features, scalability, and stability...SQL Server is the winner hands down. It has so many more features to scale your business.
It is also worth considering Mysql if you do choose to use a SQL-server solution within a small business. It is free and the amount of support and pre-built app's available makes it a very sensible choice.
If you're talking about a project/business that has access to a person who knows how to "database analyst" (to mis-verbify a noun), then I'd definitely have to go with using SQL (or whatever other free database solution the DA is familiar with).
Here's how I got into databases and how it worked for me:
I was in sales and marketing for a small business. I was one of two salespeople in the whole company (which had, if I remember correctly, about 15 employees, tops). There was no means of keeping track of customers and orders, except when an order was placed, it would be put in a manila envelope and kept on someone's desk. So I started an Excel spreadsheet to keep track of my own customers, their contact data, their order history, etc.
The spreadsheet rapidly became too large and complex for easy use, so about a month after starting that, I decided to check out this "Access" product. I'd used flat-file databases before, at prior employers, but no relational databases, and wasn't even sure what "relational" meant.
Access was very easy for me to learn and get some decent use out of. Took me a couple of weeks of playing around with it between sales calls and such, and I had something that would work for my needs.
By this time, we were up to six salespeople, and the other five all wanted to use my Access app too, as they saw me using it. So I struggled through the help files and, again in very part-time work, got it set up so the database was on the server and the front end could work from any workstation in the company.
Functionality expanded. Features were added. Management wanted access to the data in it. The production and delivery departments wanted access to the upcoming jobs stored in it (what we sold, they had to produce and ship). Payroll wanted access to it for commissions data. And so on.
Rapidly, it became clear that too many users made it unstable and we were running into concurrency issues routinely.
So we upgraded to SQL Server. By that time, I had a good idea on how a relational database should work (as opposed to how I started out) and the migration to SQL took only a couple of days.
It's been five years since I started out trying to build something to keep track of my own customers, and it's been 3 1/2 years since I actually had any customers of my own to keep track of, since I was moved to full-time system development.
The application still has an Access front-end, the back-end is SQL Server 2000 Enterprise, and we have some very feature-rich .NET Web pages on the same database (though I don't do the pages).
I'm in the process of working out how to move to SQL 2005 and a .NET front-end (probably C++).
That kind of path, starting with easy-to-learn Access, moving to SQL when/if the application grows in use and functionality and importance, is very good when you don't have someone available/affordable to build a "real database" right off the bat. Also, if the database never ends up being mission critical, and stays small and easy to maintain in Access, you've invested less time and effort.
After all, experienced database analysts are much more expensive than having someone in Sales spend half an hour building something in Access.
Here's how I got into databases and how it worked for me:
I was in sales and marketing for a small business. I was one of two salespeople in the whole company (which had, if I remember correctly, about 15 employees, tops). There was no means of keeping track of customers and orders, except when an order was placed, it would be put in a manila envelope and kept on someone's desk. So I started an Excel spreadsheet to keep track of my own customers, their contact data, their order history, etc.
The spreadsheet rapidly became too large and complex for easy use, so about a month after starting that, I decided to check out this "Access" product. I'd used flat-file databases before, at prior employers, but no relational databases, and wasn't even sure what "relational" meant.
Access was very easy for me to learn and get some decent use out of. Took me a couple of weeks of playing around with it between sales calls and such, and I had something that would work for my needs.
By this time, we were up to six salespeople, and the other five all wanted to use my Access app too, as they saw me using it. So I struggled through the help files and, again in very part-time work, got it set up so the database was on the server and the front end could work from any workstation in the company.
Functionality expanded. Features were added. Management wanted access to the data in it. The production and delivery departments wanted access to the upcoming jobs stored in it (what we sold, they had to produce and ship). Payroll wanted access to it for commissions data. And so on.
Rapidly, it became clear that too many users made it unstable and we were running into concurrency issues routinely.
So we upgraded to SQL Server. By that time, I had a good idea on how a relational database should work (as opposed to how I started out) and the migration to SQL took only a couple of days.
It's been five years since I started out trying to build something to keep track of my own customers, and it's been 3 1/2 years since I actually had any customers of my own to keep track of, since I was moved to full-time system development.
The application still has an Access front-end, the back-end is SQL Server 2000 Enterprise, and we have some very feature-rich .NET Web pages on the same database (though I don't do the pages).
I'm in the process of working out how to move to SQL 2005 and a .NET front-end (probably C++).
That kind of path, starting with easy-to-learn Access, moving to SQL when/if the application grows in use and functionality and importance, is very good when you don't have someone available/affordable to build a "real database" right off the bat. Also, if the database never ends up being mission critical, and stays small and easy to maintain in Access, you've invested less time and effort.
After all, experienced database analysts are much more expensive than having someone in Sales spend half an hour building something in Access.
Access is limited to desktop use, MS SQL Server Express can be used over the N/W usimg TCP/IP. So you can use your database for networked applications also. Hence migrating to SQL Express is recommended.
Access as a front-end and SQL Server as the back-end is just another option in the mix. This allows us to utilise the ease of developing forms and reports in Access and keep the data in SQL Server.
Not only "just another option" but a good option at that. It has worked quite well for me.
Same here. I really like the "Documenter" that comes with Access. I use that to generate those nice little table reports describing SQL Server tables. Odd that SQL never came with something like that. Nevertheless, it works great.
-CB
-CB
This solution .mdb or .mde will expose the UserID/Password(s) of your SQL Server to binary editors. Not usually diserable.
You can use Windows security on the Access-SQL connection. Then there's no userID+password stored in the binary. Of course, that requires that you have a domain or be running on the local machine.
Yes, I did omit that fact. I just wanted those that use SQL security to know that Access front-ends are not necessarily a good idea.
Using Access for the front end provides the tools for rapid and flexible reporting. ODBC provides the connection to the back end database. SQL Server provides integrity and multiuser access, among other features. The worst limitations of Access are avoided.
With this modular approach, either piece can be changed to meet future needs. The front end can be replaced without affecting the database. The database can be replaced with little impact to the front end.
We use Access with an enterprise Oracle database. The database is part of an application that provides all of the data entry, analysis, and management. Access provides report flexibility that is lacking in the application.
With this modular approach, either piece can be changed to meet future needs. The front end can be replaced without affecting the database. The database can be replaced with little impact to the front end.
We use Access with an enterprise Oracle database. The database is part of an application that provides all of the data entry, analysis, and management. Access provides report flexibility that is lacking in the application.
If you are going to use access as the development tool and it's always going to be a small number of users and a small amount of data and it's not that critical, then OK access is a choice. A bad one, but possibly a practical one.
If you are using access to be mobile, ie carrying the mdb about locally and then resynching with a main database, or passing it between users without a constant connection, then mobile is now a much better choice.
If you are using it because it's called a database and you got it free with office, then you are doing yourself no favours at all.
As an investigation tool for power users, preferably off a copy of the live data, it still has some attractions, to write professional systems with, total non starter.
The real nightmare is when your requirements outgrow a desktop database, porting it over to a more full featured one, you either end up with a really crappy solution, or you have to rewrite from the ground up.
There are other free DBMS's that don't come from MS of course.
So it's not one or the other.
If you are using access to be mobile, ie carrying the mdb about locally and then resynching with a main database, or passing it between users without a constant connection, then mobile is now a much better choice.
If you are using it because it's called a database and you got it free with office, then you are doing yourself no favours at all.
As an investigation tool for power users, preferably off a copy of the live data, it still has some attractions, to write professional systems with, total non starter.
The real nightmare is when your requirements outgrow a desktop database, porting it over to a more full featured one, you either end up with a really crappy solution, or you have to rewrite from the ground up.
There are other free DBMS's that don't come from MS of course.
So it's not one or the other.
You say it can be bad or "doing yourself no favours at all". Can you please elaborate?
I have worked on different applications: VB6 connecting to Access, Access app connecting to Access database, Access app/mdb connection to SQL Server, Vb6 connection to SQL Server plus applications not germane to this discussion. Each case is different, and had pros and cons.
I have seen ETL logic written in Access 97, and then subsequently migrated to DTS in SQL Server. It is certainly feasible and cheap to design and port the database to bigger databases. It requires good understanding of the system and simple software development principles. Rewriting per se is not a bad sign of the state of software. And crappy solution can be implemented even in SQL Server.
Once I had to work (actually clean up) on an Access database that originally was started by a person who had very little experience in designing and creating database/applications. The person had picked a book from a store and started creating what was needed (because the IT department thought the application was too small to create and support; and hence had assigned a low priority). Well, sadly the tables were not created properly and I had tough time getting it into shape. I could do only so much. So yes, it is so easy that people can get started on it and create maintenance issues. But the database had served well for about 15 people until they needed a professional to help them out.
I have worked on different applications: VB6 connecting to Access, Access app connecting to Access database, Access app/mdb connection to SQL Server, Vb6 connection to SQL Server plus applications not germane to this discussion. Each case is different, and had pros and cons.
I have seen ETL logic written in Access 97, and then subsequently migrated to DTS in SQL Server. It is certainly feasible and cheap to design and port the database to bigger databases. It requires good understanding of the system and simple software development principles. Rewriting per se is not a bad sign of the state of software. And crappy solution can be implemented even in SQL Server.
Once I had to work (actually clean up) on an Access database that originally was started by a person who had very little experience in designing and creating database/applications. The person had picked a book from a store and started creating what was needed (because the IT department thought the application was too small to create and support; and hence had assigned a low priority). Well, sadly the tables were not created properly and I had tough time getting it into shape. I could do only so much. So yes, it is so easy that people can get started on it and create maintenance issues. But the database had served well for about 15 people until they needed a professional to help them out.
and you didn't need the portability aspect, why are you using it.
It's probably me but I've seen a lot of access based systems, that someone tried make into a complex business application and failed extremely badly.
If you have plans for the data volume, the number of users or the number of uses to grow, then starting with access is a mistake.
Like all design errors, you need to discover them early.
I've seen people try to get concurrent access
for fifty users with gigabytes of data and entire swathes of business and storage logic wodged into the presentation layer.
I freely admit it put me off.
It's probably me but I've seen a lot of access based systems, that someone tried make into a complex business application and failed extremely badly.
If you have plans for the data volume, the number of users or the number of uses to grow, then starting with access is a mistake.
Like all design errors, you need to discover them early.
I've seen people try to get concurrent access
for fifty users with gigabytes of data and entire swathes of business and storage logic wodged into the presentation layer. I freely admit it put me off.
As a developer predisposed to using Access only as a database I would not use it as a front-end. Since, I can code and create database, I would naturally go for C# and SQL Server solution. But sometimes Access is useful for a quick solution.
I am talking about users who do not have enough experience in creating applications; but who just need something to get their job done.
I am sure you will agree that all IT does not run on the BIG systems, eventually the small Perl utility or the Excel sheet or the Access database is used by the end-users to accomplish tasks.
I have seen 20 users trying to use Access concurrently, and run into problems. I have read at Experts-Exchange.com people claiming they have had no problems with 50+ concurrent users. It is about how they split the app and mdb part, I guess.
I am talking about users who do not have enough experience in creating applications; but who just need something to get their job done.
I am sure you will agree that all IT does not run on the BIG systems, eventually the small Perl utility or the Excel sheet or the Access database is used by the end-users to accomplish tasks.
I have seen 20 users trying to use Access concurrently, and run into problems. I have read at Experts-Exchange.com people claiming they have had no problems with 50+ concurrent users. It is about how they split the app and mdb part, I guess.
Your CD collection, asset log for a one man shop. Some weekly performance graphs, it's a good tool.
I've seen it used well in professional systems as well.
Had a 'debate' with one guy who uses it as back end for web server systems. He called that 100s of users, in actual fact it was one, but the guys on the other end don't know that.
He also called it big (100M) , I guess I'm a bit of a size queen, I've worked on tables bigger than that.
Scalability is an overarching requirement in the environments which I work, Access just doesn't cut the mustard on that front, and it never will and still be Access.
I've seen it used well in professional systems as well.
Had a 'debate'
with one guy who uses it as back end for web server systems. He called that 100s of users, in actual fact it was one, but the guys on the other end don't know that.He also called it big (100M) , I guess I'm a bit of a size queen, I've worked on tables bigger than that.
Scalability is an overarching requirement in the environments which I work, Access just doesn't cut the mustard on that front, and it never will and still be Access.
"Had a 'debate' with one guy who uses it as back end for web server systems."
That I wouldn't do, unless as you say if it is going to be used by only one person Even if it was just a handful of users, I would have gone with MSDE (now SQL Server Express edition).
That I wouldn't do, unless as you say if it is going to be used by only one person
Even if it was just a handful of users, I would have gone with MSDE (now SQL Server Express edition). 
You're absolutely right that it works well for small groups of people (5 concurrent, 15 or 20 or so total), and for small to medium amounts of data.
The biggest bit of advice I can offer to non-DB professionals building a db in Access is the same one that many professionals forget about: DOCUMENT EVERYTHING.
If you've documented everything correctly, you know what the relationships are, what all the properties are, what exceptions are allowed, and as importantly, WHY they were built that way. Not only does it make maintaining and modifying the database easier (and being able to write reports out of it); but is also makes a good base for those situations where you have to upgrade to a bigger database engine - you still may have to design from scratch, but you've got almost a complete analysis to work from then.
The biggest bit of advice I can offer to non-DB professionals building a db in Access is the same one that many professionals forget about: DOCUMENT EVERYTHING.
If you've documented everything correctly, you know what the relationships are, what all the properties are, what exceptions are allowed, and as importantly, WHY they were built that way. Not only does it make maintaining and modifying the database easier (and being able to write reports out of it); but is also makes a good base for those situations where you have to upgrade to a bigger database engine - you still may have to design from scratch, but you've got almost a complete analysis to work from then.
Upgrading a small Access database to SQL server is as simple as firing up a wizard and running it, in many cases.
The DB engine used by Access since Office 2000 has been T-SQL "compliant" to a very large extent. That means upgrading tables, views and stored procedures from the Access database to MS SQL is very, very simple in most cases.
Of course, the front end has to be modified a bit to connect to SQL instead of an Access database (either in the same file or separate), but the wizard in Access does that part for you too, if I remember correctly.
(My big issue with converting from an Access database to an Access front-end on an SQL server database was that the original database was badly designed. Too many wide tables with multiple columns for things where I should have had subtables. I know better now.)
The DB engine used by Access since Office 2000 has been T-SQL "compliant" to a very large extent. That means upgrading tables, views and stored procedures from the Access database to MS SQL is very, very simple in most cases.
Of course, the front end has to be modified a bit to connect to SQL instead of an Access database (either in the same file or separate), but the wizard in Access does that part for you too, if I remember correctly.
(My big issue with converting from an Access database to an Access front-end on an SQL server database was that the original database was badly designed. Too many wide tables with multiple columns for things where I should have had subtables. I know better now.)
Jody,
I don't know if you've discovered this or not - but Microsoft now offers (for free) the front-end tools for SQL 2005 Express. I just downloaded and installed them. You get SQL Enterprise Manager and the online books too ... all for free. I presume the only limitation is the number of concurrent users on the development machine - like MSDE has always limited you to - but the limitation of no management tools for it no longer exists.
Regards,
-CB
I don't know if you've discovered this or not - but Microsoft now offers (for free) the front-end tools for SQL 2005 Express. I just downloaded and installed them. You get SQL Enterprise Manager and the online books too ... all for free. I presume the only limitation is the number of concurrent users on the development machine - like MSDE has always limited you to - but the limitation of no management tools for it no longer exists.
Regards,
-CB
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
