Moving from Access to SQL Server (1 Viewer)

Libre

been around a little
Local time
Today, 15:28
Joined
May 3, 2007
Messages
660
I know there must be other threads about this - please allow me to start this discussion anew, so that I may restrict it to my concerns without poring over other possibly similar threads.
I have a fully developed Access db with 2-3 users. This db will be moved to another environment where there may be 20+ users. I've heard many times that Access is not "robust" enough for a "real" production environment, and that SQL Server is for the BIG BOYS, while just Access is for kids. More or less.
Is this true, in your opinion?
Does SQL Server offer more in terms of security, multiuser capability, stability...or what?
I would still want MS Access for the front end, user interface, stored procedures, functions, etc - um, right?
If not an Access GUI, what then?
Can anybody give me the lowdown, without getting into too many technicalities - sort of the 40,000 foot view? Am I going in the right direction?
I have zero experience with sql server, how to code instructions for it (I do know basic SQL and VBA) - where do I start?
Or even, SHOULD I start?
Thanks to all
 

Rx_

Nothing In Moderation
Local time
Today, 16:28
Joined
Oct 22, 2009
Messages
2,803
I had an Access 97 application (split) front-end to Access Back End that had 200 concurrent users. Access 97 had a now dropped feature called Replication.
This means that one back-end could be a publisher/subscriber to another Access backend DB. The group of databases each had 200 users. That ran a telecommunication center of thousand of callers asking you to switch from AT&T to XXXX long distance.

20 users are nothing.
That said, it is important to evaluate your front-end, back-end, network latency, and code. Why is code at the end when it is the most important? answer: segway

If your code opens a table recordset, holds it open while the user enters a record... opps they took a coffee break in the middle of editing... then releases the recordset on the enter: you will have problems with multiple users.
That alone will be a topic to discuss.

Distribution to desktop (new release, desktop consistent configuration, Microsoft Patches, ...) this is your second topic.
My shop did run the front-end on users workstations. They also wanted the Apple Tablets to work. Oh, they also wanted the Apple tablets to work in remote rural areas over wireless (low bandwidth). One of the remote offices only had a DSL (low bandwidth) line for 10 users.
That is why I recommended Citrix. Citrix runs on any platform. The Citrix ICA client uses about 1/10 the the bandwidth. The main advantage is that you release one singe Front-End. When a Citrix user gets Network Permission to the server, it dynamically makes a copy of your CurrentRelease into a UserName folder - the Access runs on the Citrix Application server. So, a desktop with XP, Windows xxx, Mac doesn't even need MS Office installed to run your application. For a version release, the Access DBA just replaces the CurrrentRelease with the new updated release. Presto - the next time a user logs in, they have the latest version.
Citrix just came out with a new Xgen for small business. You should be able to get a free one day training seminar.
The Citrix will provide a ROI (return on investment) in about 3 months from the cost of having a workstation tech constantly go desktop to desktop to insure every desktop has the same exact configuration for Access including patches.
Speed wise, the Access Application is running very near (big bandwidth) to the DB backend. Processing threads are on a Server. Extremely small data (screen changes, keyboard clicks, mouse movements) goes across the network.

For 20 users would the SQL Server be better? Heck yes.
SQL Server has the ability to make backup copies through the day. If your back-end database is just on a network folder, there will be labor involved to insure there is a proper backup.
On SQL Server - create views (queries) linked to your front end. This means only the result of the data goes across the network (desktop or Citirx). Time savings depend on each database type. Simple transactions not so much. Big regulatory rule calculations, 20 to 1 time savings.
SQL Server also provides a pathway to publishing selected data to webpages. It also allows new levels of security for your network administrators. What is the value of the data if it was all lost tonight? A company should have an exact value for that.

Congratulations on your upgrade! Will look forward to assisting you as will probably several others on this site.
 

Ranman256

Well-known member
Local time
Today, 18:28
Joined
Apr 9, 2015
Messages
4,339
I had an Access db. Then simply replaced the attached tables with those in SQL.
By simply naming the outside linked SQL tables to My Internal table names, it worked fine.
I had no major problems.

The only problem was SQL server HATED having table modified to include a BIT field (yes/no)
It would choke when trying to set that field for some reason. But changing it from bit to integer and saving 0 or -1 ,worked fine.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:28
Joined
Aug 30, 2003
Messages
36,118
The answer to "Does SQL Server offer more in terms of security, multiuser capability, stability...or what?" is yes, it does. That said, Access is a very capable back end and is used in any number of "real" production environments. I would start off by leaving the back end in Access, but be prepared to switch if circumstances warrant. You might start playing with SQL Server to start learning it, but never need it. ;)
 

Users who are viewing this thread

Top Bottom