Software

Ask something
Discussion Locked

Access to SQL? Pro/Cons

0 helpful
7 replies

by redolph

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In our group of 40 users, we have about 300 databases. Many users are "a database per task" and I want to work with them to consolidate, but we will still have quite a few different databases. Also, I am trying to recruit users to a couple of "common databases" to link to, to prevent duplicate data maintenance.

My question:

One of my less-Access-savvy brethren has the idea that if we simply transform our tables to SQL Server or Oracle, that our performance will dramatically increase. He seems to think that we can use EVERYTHING in Access the same way we have and everything will be more powerful and faster. I am more skeptical. I am concerned that that change would come at a price. I am concerned that we would be far less nimbleabout making changes to table structures. (We are far from perfecting our process.) I also am concerned that to truly reap the benefits we would have to write queries in SQL. I have heard that there are some Access queries that cannot be reproduced in SQL? I am also concerned that our 40 users would have to learn SQL Server to function or really benefit. That would be out of the question, as many are doing all they can do to use Access.

So, is there such a thing as "tables-only" SQL server? Does it alone improve performance? Does it make you less adaptable without SQL expertise? What are the pros/cons? I am trying to be cautious, while I am afraid my buddy is spreading visions of "pixi dust" that will magically transform everything if only we would convert our tables to SQL or Oracle.

Am I wrong, or can anyone tell me the true ramifications, or how full-blown we would have to be to reap any benefits?

I would greatly appreciate any advice!

P.S. Our biggest tables are ~100,000 records and while we have a handful of databases around 300-400MB, most are 100-200MB, if that is helpful. We are compacting semi-regularly, too.

Thanks,

Redolph

7 replies

by roho

Eventhough it sounds like "pixi dust" maybe it is not.

You can use the Upsizing Wizard to upsize your database to SQL Server. That copies the tables and structures from your Access database to SQL Server and relinks the tables in Access. Leaving all the code, forms, queries running as before only a bit better.
On the other hand you are not using the real benefits of SQL server like stored procedures etc.

Mind you, you can use MS Databse Engine (MSDE) as well, MSDE is a "stripped down" version of SQL server that comes with Office Professional or Visual Studio.

This however does not make one or a few central databases. Before you do that you should redesign the databases. Then you can easily jumpstart SQL Server as database backend, but to truly reap the benefits get some knowledge of the powers of SQL Server!

by redolph

Roho,

Thanks for your thoughts!

Perhaps to shed some light on our situation:

We have about 40 Access users, none of which have any designs on learning SQL. My concern is, will we have any real performance improvements without going deep enough into SQL than changes must be made using SQL (table structure changes, query changes, stored procedures, etc.)?

Maybe the best question is: will we notice any signifcant performance improvements in running ACCESS queries, by having only the tables themselves in SQL? We would do everything as "normal," except that the tables would be SQL. Our biggest tables are about 150,000 records, in case that is helpful.

Would this make any real difference without creating SQL queries/stored procedures?

Thanks for any advice!

Redolph

by mg

I converted my company from Access only to an Access front-end and SQL Server holding all the data.

I couldn't use the Access upsizing wizard because of poor initial design of our tables. However, SQL Server's Data Transformation Services are excellent tools and became my best friend...

The hardest part came in re-working the queries, forms and reports in Access. Linking to the SQL Server tables via ODBC created tables with a 'dbo_' prefix to their names. In hindsight, I suppose I could have renamed the tables in Access to remove that prefix... However, I chose to update the names wherever they were used in Access.

FMS' Total Access Analyzer was a lifesaver for this. I have also seen a renaming tool called Speed Ferret but I have never used it.

As for performance, SQL Server definitely blows the doors off of Access. Proper table design is a must and I assume you have a good schema already. SOME things that are legal in an Access query (VBA functions mainly) cannot run on SQL Server; though Access will analyze a query and run as much of it on the server as possible it is often best to re-design the query as a SQL Server view.

One of our (poorly written) VBA functions took nearly 10 minutes to execute. After re-working the code as a SQL stored procedure and calling it from Access via ADO; execution time dropped to about one second.

-Mike Garstin

by alex.jones

Redolph, Your users will not have to learn SQL by displaying the table and then setting the query. You have valid questions about sql, May I suggest a good third party book such as Mastering Access Premium Edition this should answer alot of your beginning questions. The whole answer would be SQL will allow you to Combine the Mdb's and Tables into a single Sql Database. The users can still create their own tables and such if need be.The performance will be Better .

by sirshannon

if you can afford the $, that is.

The process really isn't that bad. You convert the tables from Access to SQL (going over them and correcting by hand if/when needed), then move your queries to Stored Procedures and Views.

Learning stored procedures will take little time at all if you guys already know Access pretty well.

As you continue to learn about SQL Server, you should continue reworking the code as needed, but even after your initial version using SQL Server, you'll more than likely be amazed at the performance boost.

I remember the first time I had a project that was moved from Access to SQL Server, I couldn't believe the difference, even before the queries were moved to stored procedures. 15 seconds to 3 seconds, easily.

by alex.jones

Redolph, You can use Access as the front end to a Sql database. The are called *.adp - access database project The forms can be used just point to the Sql Database and and Table structure for the fields or use vartibales. You will be able to group Tables together and get rid of duplacated fields containg the same Information. The Security of Data would be system level instead of application level. Go ahead and take an existing Access application and run a test on your test server. The benifitswill greatly be noticed and the application will be Enterprise wide instead of single user. Access projects can be stored on a server with Icon links and users will also be able to work while the data structure is being updated. If in the future yiudecide to start using Access pages you will be 1 step ahead of the game. I believe your coworker has the right idea, To be on the safe side also check into some good SQL programming classes. I am currently working on a project using access and sql and the power is unbelivable.
ps. Visual Basic can also compliment your application with more power by calling sql commands. I hope this may help to at least look at the possiblities. You can down load a 90 day eval copy of SQL to test with.

This discussion is closed to new replies.