Access / SQl Server Advantage.

[caljohn527]

Hi, all you out there,
I am building a new database for my business, and I am a little confused, I need some information about access & SQL server. I like using access, but would it benefit me to use SQL Server with access. I do know access can work as a stand alone for single users. And that the SQL Server gives me the capability of multiple users Server. Is there any other advantage to using access / SQL Server?

Please Advice

Thank You For Your Time.

John C.

 

[pbaldy]

Actually Access can work quite well for multiple users too. SQL Server is certainly more powerful as a back end, but unless you already own it I wouldn't bother getting it unless you were going to have a lot of records/users. I'm not sure there are specific guidelines, but Access can easily handle hundreds of thousands of records and 20-50 concurrent users. The number of users is really dependent on the nature of what the users are doing and how the application is written.

 

[GrexP]

We use Access with SQL server quite a bit at work. SQL Server is the back-end, of course, and then we use Access as a front-end so that the mere mortals can run reports on their own. We also have some VB6 & VS2005 .Net front-ends for both Access and SQL Server.

We create zillions of reports and sometimes they are run multiple times a week by multiple users. Often times the only difference is the date range and a few other field values. I supply the user with an Access form with two date boxes and a drop-down list or two of values. They make their selection and click a button to produce the report.

The Access database links to the SQL Server tables (views) and then stores its own queries and tables from make table queries. Some are single user Access dbs, but others are multi-user dbs. We are a relatively small organization with 30 or 40 people. A multi-user Access db would have no more than 10 people connected to it at one time. It works well for us.