MS SQL Server vs MS Access--which one

[Syscwl]

[/b] We have a 50 user environment and we need to make a critical decision re
which DB to use...we've tested both and are most pleased that at least in
our scenario, they're fairly comparable in speed..development time is a
whole different issue...SQL Server is clearly more labor intensive......what
we don't know is how they'll perform in real production...has anyone
migrated from one to the other or made a similar decision. Can someone point
me to an articel Access vs SQL Server...thanks in advance.[/b]

 

[WildHare]

I don't know if you'll find any USEFUL article on Access vs SQL Server - that's like a road test comparing a Chevy Suburban to a 45' Freightliner..they can both do some heavy hauling, it's just a matter of scale.

Access obviously has lower ceilings in terms of total number of users, average throughput speed and other things, but has the edge in ease of development and support cost.

Have you considered Access as the Front end and SQL server as the back end (data repository) - in some ways that gives you the best of both worlds.

Hope this helps a bit.

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[demoman]

SQL server will smoke Access in all performance areas, especially in a WAN environment. However, many applications do not require the increased performance. I am comfortably running a couple Access 40-50 megggers, with 300-400k rows each. The applications are not East Cupcake either!

I have the best of all worlds. I develop in VB, or Access, depending on application scope and complexity. I may use either SQL Server, or Access, or a combination, for the data. There is a lot of flexibility if you have the whole toolbox available to you.

Jim (above) made excellent points. The bottom line is, you can start in Access. If you later need more 'pop', migrate up without too much difficulity.

 

[Fflyer]

I was originally in this situation, but now find myself needing to migrate the main table to SQL and I am going to front end with ASP code. I do want the MSAccess database front-end to stick around though for just a few individuals that will update the SQL table. All the other users will just use a filtering utility as read-only. What settings do I need to make so that someone opening the MSAccess database can edit the SQL table?

I started another thread today with this question, but it makes a lot of sense to tag it onto this one.

Thank you,
Chad

 

[grnzbra]

I worked in an office which used an Access97 system. It was driven by 5 tables which ranged in size from 300 or so Meg to one that was approaching 1 Gig by the end of each year (each table had its own database and the main database linked to these tables). Every month end activity by the accountants entailed running a process, look at the results, make changes, run the process, etc, etc, etc. Running the process on the accountants computers took several hours (which didn't fit well with the process, adjust,process, adjust nature of what they were doing). When I ran the process directly on the server, it took 10 to 15 minutes. So, if your high priced accountants are being slowed down by the "inexpensive" Access system, vs the faster SQL system with 1 higher priced techie, you might want to consider the SQL Server systerm. Also, if there is any possibility of having to go to SQL in future, START OUT WITH SQL SERVER; converting our system turned out to be such a bear that we had to call back the firm that built the Access system to do the conversion and it was never completed.

 

[petermeachem]

It's not critical at all. It is extremely easy to change from Access/Access to Access/SQL Server and then slightly harder to change to VB/SQL Server.

The reason why your process was quicker on the server than over the network was that Access is slow on a network. This is because it shunts a huge amount of data to the client. If you have a grid with recordsource SELECT * FROM table1, then ALL of table1 will be copied to the client.

Who told you that sql server is more labour intensive? What were the reasons? Can't think of a single one.

The deciding point would include the size of the tables, and hence the network traffic as well as the number of users and what those users are doing. With 50, I'd tend towards SQL.

You may as well see how Access/Access gets on first. Upgrade to sql server takes no time at all really. Just use the upgrade wizard and that's it.

VB/SQL gives you more control, so you can reduce the traffic. The fastest is VB/SQL/ run procedures on the server from vb. That is a bit more work, and you really need to find the bottlenecks before attacking them like this.

Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[grnzbra]

The experience we had with Access 97 upgraded to Access 2000 and they using the 2000 wizard to convert to SQL Server 7.0 was that almost all of the queries wouldn't convert. Since there were hundreds of these things, there was a lot of work involved. Having seen what was wrong, I believe the queries could have been written originally so that they would convert, but they weren't. All I'm saying is that if there's any possibility of needing to go to SQL server, either start there or keep in mind the things that don't translate and don't use them in Access.

 

[petermeachem]

That's strange. I didn't have to change a single thing going from access 97 to sql. From access to vb I did as I had to use sql date formats and a few other little things like that. We are probably going to do a vb/Access (how many combinations!) to vb/sql and don't imagine it would take more than an hour. I have had to change a few things in the past as ADO seems to have the majority of the English language as keywords. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[grnzbra]

Perhaps it's because I wasn't that familiar with SQL at the time that I didn't see any simple quick tweek to things. Or perhaps it was because the developers who built the Access system made it sound like there would be instant SQL server; kinda like using a report wizard in Access - fill in the blanks and poof, there it is.