Converting single-user MS Access to multi-user

Topic

New Reply

I have a MS-Access 2007 database that’s currently being used by a single user. I’d like this database to be used by multiple users with the database stored in Ms-SQL server.

What are the issues/best practices I should be thinking of before going ahead with it.

1) importing the access mdb into sql-server
2) controlling user access
3) changes to the forms
4) changes to the table/schema (relationships/tables/field properties/columns etc..)
5) coordinating updates to all the ms-access front-ends on user workstations
6) should I continue using ms-access for front-end?

Any good MS articles/white-papers on this process?

Thanks

Reply | Quote

Replies

Before I give any suggestions, perhaps you can give me some more info. Is your current database a single database or is it split between frontend and backend? How big is it? How many users do you anticipate? What do you envision when you say “controlling user access”? Do you have any experience with SQL Server?

Reply | Quote

Before I give any suggestions, perhaps you can give me some more info. Is your current database a single database or is it split between frontend and backend? How big is it? How many users do you anticipate? What do you envision when you say “controlling user access”? Do you have any experience with SQL Server?

Thanks Mark.

Database is split. But both files are on the desktop currently.
Size is less than 100Mb.
Anticipated users is 35.
Controlling user access – means authenticating users to access the backend – I guess I have a choice – either Windows Authentication or SQL Server authentication (preferred). By preferred meaning – if someone gets hold of the front end code – he still will not be able to connect to the backend if we use SQL Server authentication. Is that correct?

I do have some experience with SQL Server but mostly I am from ms-access, oracle and mongodb world (and others). I do have rdb/no-sql db experience.

Reply | Quote

Mark will probably chime in here too, but in his absence, we really only work in the Access front-end, SQL Server back-end environment. Allison Balter has an excellent book on the subject based on Access 2002 that is still quite useful – see http://www.amazon.com/Alison-Balters-Mastering-Enterprise-Development/dp/0672321130/ref=asap_B001IO9NPK_1_6?s=books&ie=UTF8&qid=1415048024&sr=1-6. Another resource you might find helpful is Microsoft Access to SQL Server Upsizing Resource Center. That has a number of links to Microsoft papers, though I’ve not checked to see if they still work. The initial step is to upsize your Access back-end to SQL Server. I would not use the upsizing wizard that comes with Access 2007, but the one with 2010 or 2013 are quite good. Another option is to using the SQL Server upsizing tool, which many people prefer. Before you do that, make sure your database schema is solid and has the indexes you need to get good performance.

Once that is done create ODBC data sources on the workstations that will connect to it, and relink your tables to the SQL Server database. Therein lies an issue which you may find to be a challenge, as each workstation will need to have that DSN configured for the User of the workstation – in Win7/8 you cannot define a System level DSN. One that is done, you can begin to look for performance issues and take the steps necessary to make things work well. In general you want to return a small number of records to Access, and filtered forms with sub-forms are an excellent tool for displaying and editing data. Reports can be a bigger challenge as you may need larger numbers of records, but users are usually willing to be more patient with large printed reports. I should also note that the ADP version of Access database is no longer supported so I would not recommend taking that approach.

Reply | Quote

The articles others have referred you to should help, but a few other suggestions.

Use Windows Authentication. SQL Server authentication is insecure in a networked environment. Windows is more secure than your current system (non-users can’t steal the database), doesn’t require users to have two passwords (one for Windows and another for SQL) and doesn’t risk exposing passwords. Years ago I had to set up an Access front end to an existing SQL database with SQL authentication and someone knew the passwords within days. Once someone knew them, I had to assume EVERYONE knew them.

Access SQL and SQL Server SQL are different languages. Especially where functions are concerned. For a simple migration you should only move the tables, not the queries, so you can leave queries in Access syntax. If your front end relies on manufacturing SQL queries to pass to the server you may have to change them. There are many sources of knowledge for SQL Server but I always use SQL Server Management Studio to manage my SQL databases and its online help (SQL server books online) is very comprehensive. If you resort to tips sites and boards make sure you get tips about T-SQL (Transact-SQL) as other flavours (MySQL etc.) are different.

You’ll find plenty of sites online that highlight the language differences, perhaps the most awkward (for all sorts of reasons) is that the text delimiter is a single quote ‘ not a double quote “. (actually, you can change that behaviour in T-SQL, but in my view you should live with it as while all SQL flavours allow ‘ they don’t all allow “).

Once you’ve done the basic migration consider moving other parts of your application to the SQL database. Moving queries to the database (as T-SQL views) means the bulk processing is done on the server and only the results pass to the client. As SQL Server is a pretty fast system that can speed things up considerably.

As for deploying, others have pointed out that you’ll have to set up an ODBC DSN on each user’s workstation. I suggest you document that procedure thoroughly with screenshots, one day you’ll have to tell someone how to do it for themselves, usually on the phone from your favourite holiday spot. It is a rote procedure and with Windows authentication you don’t need to publish the passwords. As for front-end updates, you can use a scheduled task on each workstation to check for a new version on a server every night – just use xcopy with a date check and no overwrite check. Document that procedure too!

Finally, think about what happens when multiple users are reading and updating the same data at the same time. For example the scenario A reads record, B reads same record, A updates, B updates can lose A’s changes or make B’s changes invalid.

Ian.

Reply | Quote

Since your database is so small download SQL Server Express. It is free and with a few exceptions that you should not be concerned about, works as well as the full function version. As long as your database remains below 10 gig you are good to go.

Reply | Quote

Thanks everyone for the excellent tips.

I am using SQLExpress 2012 in my development environment for the back end and Access 2010 as the front end for now. In production it will be SQL Server 2012 most likely.

It seems there is a performance gain for using “pass through” queries – which execute on the backend. It’s a little different syntax than the Ansi-sql.

So what is the preferred way? Should I get all the tables,indexes, referential integrity setup on Access and then migrate it all to the back-end ?

I noticed when creating pass-through queries I had to store the DSN info in the ODBC connect string. Is there a way to avoid that as it exposes the password in there.

I still need to get my head around the software upgrade part – how to replace/add new forms/queries/reports and distribute it to all workstations.
Would appreciate pointers to docs/reference material or sharing your experiences.

Thanks once again.
Really appreciate it.

Reply | Quote

I noticed when creating pass-through queries I had to store the DSN info in the ODBC connect string. Is there a way to avoid that as it exposes the password in there.

You can use Access MVP Ben Clothier’s method, which I just recently implemented for an Access reporting tool that uses Pass-Through queries to an Oracle database with Integrated Security:

Power Tip: Improve the security of database connections
http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/

Note: Add a declaration for strConnection in the InitConnect function. By private e-mail, Ben mentioned that he omitted this by accident in the posted copy.

I also recommend that you use DSN-Less (or DSN-Free) connection strings:
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Reply | Quote

If you have a choice on SQL Server, I would go with SQL Server 2014 Stamdard Edition (the current version), although for small databases (substantially less than 10 GBytes) the Express version should do most of what you want. It’s biggest limitation, aside from using only a single processor and the 10 GB limit, is the missing SQL Server Agent, which lets you automate maintenance tasks. With 35 users, if they are reasonably active you do want the standard edition.

On pass through queries, we only use them when the performance of Access queries have performance problems, and that typically is only a handful. Those queries have to be written in T-SQL, and you do have to store the connect string in the query definition, but if you are using Windows Security (strongly recommended – it solves many deployment problems as you have to know the UserID and Password of the current user in Access with SQL Server Authentication, and is more secure – see Ian’s comments above), then you don’t store the password in the query. If you are joining 3 or more tables, you are more likely to see performance issues with queries, as the ODBC driver typically struggles with converting that to T-SQL, and you end up pulling up all of the data from all of the tables into the Access front-end in order to execute the query. In those cases, we often create a SQL view, which behaves like a table as far as Access is concerned, and you get a significant performance boost as the bulk of the work is done by SQL Server.

Depending on the complexity of the design, you can approach the actually design either in Access or SQL Server. If there are lots of tables and referential integrity is pretty complex, then I might start with SQL Server, but the biggest factor is likely to be your comfort with the Access tools and the SQL Server tools. If you do it in Access, there are several good tools for moving it to SQL Server. I should emphasize that getting the table structure right is the most important task, and should be done before you even start tinkering with forms and reports. We spent several months on a project getting the schema designed, and it is still happily working 10 years later. There are a few things we did change, and a few more we might change today if we didn’t have it so widely deployed, but that effort saved us many, many hours of work in the long run.

As to getting the deployment process up and running, there are several options. One used fairly often is to have a batch process that simply copies down the Access front-end each time the user starts the database. Another is to do as we did and design our own deployment system, as we have a number of components that need to be updated from time to time with our complex user interface. The third is a commercial product that is available from FMS that does that function.

Feel free to post info about your progress as you work on it.

Reply | Quote

To answer some points in more detail.

Access SQL / ANSI SQL / T-SQL
There are as many flavours of SQL as there are SQL database systems. ANSI is in theory the “standard” and most SQL dialects support most of ANSI. Access is very non-standard. Once you migrate to a server based SQL it is good practice to use ANSI syntax and functions wherever possible. That will assist when getting help from tip sites (like this), if someone else has to look at your code, or if you have to move to a different server (like MySQL). Some things are hard to do with ANSI and in those cases just bite the bullet and use the local dialect. Small things I’ve made myself do include using single not double quotes for string values and putting a space after the ANSI comment marker (–) as MySQL requires one. And I use COALESCE() instead of NZ() or ISNULL() as the latter have wildly different effects in some dialects.

Queries v Views
Access queries tend to pull large amounts of data from the database. If you make regular use of queries on large joined tables create a view in SQL server that handles the join, import that into Access as you would a table, and query the view. In my experience SQL server then optimises the join process and ODBC passes the query criteria down to the server for processing, returning only the results not the entire view or (if you don’t use a view) the entire tables to be joined. The view editor in MS SQL Server Management Studio is very similar to the query editor in Access (just arranged in columns not rows)

Deploying
Google for Task Scheduler. Create a batch file on each user PC with a command like

Code:

xcopy \serveraccessstufffrontend.mdb c:accessstuff /D/C/R/Y

(With appropriate files and folders) Set up task scheduler to run that batch file every night just after the nightly backup, and/or on startup. It will overwrite the user’s current Access front end with the latest version from your file server. Deploying updates only requires you to update the server copy.

Ian.

Reply | Quote