backend Microsoft SQL Server and Access

[matrixknow]

Hello Access,

We have an access.mdb which cause more and more performance problems due to many records and heavy queries. I know you can do some performance actions but for the future my boss like to discuss the use of Microsoft SQL Server as backend and MS Access as frontend.
I know you can easy make an SQL Server Project in Access. But first I like to know some experience, information to start.
1. Do I need to know a lot about SQL Server to put my tables there. What do I need to know certainly.
2. Is the SQL Server envirronnemt as easy as MS Access ?
3. Some recommandations when you first start to work with MS SQL Server as backend

many thanks.

 

[Crowley16]

1) technically no, just use the upsize wizard, but it's a REALLY bad idea if you don't know anything about sql server

2) no, IMO, but it's not much more difficult

3) go on a proper course.

--------------------
Procrastinate Now!

 

[lameid]

I aggree with Crowley16 but have some additional thoughts and some information on some differences.

Personally, I like the Access Developer's Handbooks published by Sybex for getting a base feel for Advanced Access topics such as this. If you get the 2 volume set, there are chapters about using SQL as a backend.

Truth is that you may just be as well off using an MDB instead on an ADP for managing the database. ADP allows you to manage the SQL objects within Access, however they look more like their equivalent SQL server tools than Access tools. You can do things in MDB's you can't do in ADP's like use Jet queries (albeit poor performers when SQL Server is the backend). If you use an MDB frontend, you COULD upsize your tables and link them in the frontend (or let the wizard do it if your databases are not already split). Then your MDB should still be functional however things will probably be slower until you write objects in SQL server (typically stored procedures, views and Inline user-defined functions) to run your current SQL statements.

In an Access MDB you can run those SQL Server Objects with things called SQL Pass Through queries (Often abbreviated: SPT queries). In ADP's they are native but either way you will be doing some rewriting. The Upsizing wizard can upsize some queries but ofen bombs and nested queries and Jet functions.

SQL server divides what you know as queries into three types of objects as listed above. They behave in comparatively unintuitive ways but that does not mean it doesn't make sense just that there is not a one to one exchange of an Access query for these objects so you will have to learn a few things to get up to speed. On the flip side of that coin SQL statements are very similar between the two so if you know Jet SQL, SQL server SQL will be very similar. 3 things that you will want to know right away...

1 Wildcards are different. SQL server uses % instead of *
2 Use the Case statement in SQL server instead of the IIF function in Access
3 The NZ function in Access compares to the Isnull function in SQL Server and you do a logic test for null in SQL server as there is no function to do that task.