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