SQL Server in Place of MS Access-CodePudding

I develop 'small' Microsoft Access applications at my company using a client/database model. Much of the office personnel that use these apps are frustrated with the 'working from home' scenario where the client side grinds slowly as the on-premise data is queried and returned. This is inherent to the nature of MS Access, I get this.

One solution might be to move the backend to SQL Server Express (installed/ready to go on a local machine that I own). I like this solution but it introduces complexity. Of course it is behind the company firewall and security, so my question is this:

Is it possible to configure SQL Server to 'act' like MS Access in that there is no security, no user management, etc - it simply stores data in the database, and can be used as simply as MS Access is used?

CodePudding user response：

For the most part yes. And boatloads of people have migrated the data (tables) to sql server, but continue to use Access as the front end.

As for "additional" management? Well, I can't say running SQL server express is a big deal. And if that instance of SQL server is placed on a server you have? Then setup a nightly back-up script to run to backup the database everyday.

So, other then setup install? I never really found much of any difference required to keep sql server running as opposed to say using Access databases.

in some ways, I have in fact found running the free edition of SQL server express somewhat LESS hassle then using a Access back end.

I assume your Access applcation is now split already?

these apps are frustrated with the 'working from home' scenario where the client side grinds slowly as the on-premise data is queried and returned.

If you using a split setup, and using a VPN? yes, they are not only slow, but NOT recommended when using a back end.

However, such a setup works VERY well when using Access for the front end, and the back end is SQL server.

And yes, in near every case, SQL server express was being run, and run without much consdiering for secuirty.

You can choose two options for security:

Create one logon on SQL server - link your tables using that same logon for everyone. This quite much is standard fair, and a common setup.

Or, if all users - including those that VPN into the work network are part of a company domain and logon? Then use windows authentication with SQL server. Once again, really no additional settings are required from you the access developer.

Without question, using windows authorization is more secure, but you may well not have that choice. But, from a Access developer point of view?

You migrate your back end access database (you are running a split setup right now, right????).

After you migrate the data to SQL server, then you take the Access front end, and link your tables to the ones that now exist on SQL server.

Fully 99% of your Access application should now run and function as before.

However, you NOW start working on reports or say some forms that run "slow". In other words the mere "act" of just a migration does not result in better performance (actually sometimes it does!!).

But, in some cases, you also find the reverse. Some things might run slower. This is where you work on the so called low hanging fruit. In other words, if some report or say form that displays data runs slow? And it used a lot?

Then you can start to use SQL server features to speed things up.

In near ALL cases, the most easy performance trick?

Change that Access query (that the form or report is based on) to a SQL server view. Give the linked view the SAME name as your query was. This trick can result in astounding performance increases!!!

So, Access is quite smart.

Say you bind a form DIRECTLY to the linked table (that now points to sql server).

And say it is some kind of customer invoice table and form - say it has ONE million rows.

And in VBA code, you prompt or whatever for invoice number, and then do this:

DoCmd.OpenForm "frmInvoices",,,"InvoiceID = " & lngInvoiceID

Turns out, that Access will NOT pull the whole table, and ONLY out of the 1 million rows will the ONE record come down the network pipe.

Even more amazing, is the above works EVEN when the form is bound directly to the linked table with 1 million rows in it. Access will pull ONLY one row from SQL server, and the form will open instant if the table has 10 rows, or 2 million rows.

You don't even need to change or have specials code for this to work.

So right now?

Near every access applcation I now work on uses SQL server for the back end database. They work really well together, and with a few tweaks here and there? You find amazing performance increases can be had by adopting SQL server back ends for your Access applications - but they take some efforts on your part.