Access and SQL Server

Posted on 2013-12-30
Last Modified: 2014-03-05
I wonder if someone could address a couple of Access/SQL Server questions. First of all I wonder how main stream it is these days to use SQL Server as the back-end to an Access Front-end. I suppose a lot of Access developers are doing it. When you have big data it seems logical. But, as you know, SQL Server can get very complex.

I have used it in the past and what I needed to know in order to make it work well with Access, as I remember it, was relatively small. I would like to use SQL Server when the data needs are great but I would also like to stay away from any more complexity than is absolutely necessary. Stored procs, for example, speed things up and are the standard in the SQL community but as I remember it, they're not really necessary for most if not all of what I do.

If for large data needs I could use SQL Server as the back-end and continue using Access  in the traditional way (for the most part) as the front-end, this would be optimal.  Is it realistic to think I can do so?
Question by:WoodrowA

Assisted Solution

russell12 earned 167 total points
ID: 39747318
I use SQL server for all my applications.  I develop the tables in MS ACCESS when I develop the programs then I do an upsize to the sql server.  It isn't that much more complicated.  MS ACCESS does most of the work for you.  I only go into the sql server to set up the primary key.  Now if the program is going to be used in a single user environment and not have many records then I would say you could get away from the sql server side.  But I would just put all the tables on sql as it cant hurt anything.  This is my opinion only.  

Another thing is when I started developing I was using access tables and I was running into a lot of issues doing it that way when more than 1 user was using the program at the same time.
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 39747355
There are some "gotchas" that can crop up when moving to SQL Server:

1. Not all Access datatypes are supported on SQL Server - for example, the new Multivalued fields and the Attachment datatypes

2. If you've used Access-specific functions in your queries you'll have to change that, or leave the query in Access. For example, if you refer to a Form in a query criteria, you'd have to change that.

3. There is no Boolean datatype for SQL Server. The upsizers will change it to a Bit datatype, and you should always set a Default for that (0 = False, which is the normal default).

4. Primary Keys are important with SQL Server, and sometimes you'll find that you need a TimeStamp or RowVersion field as well in order for updates to happen.

5. Consider moving some of  your processing to the server in the form of Stored Procedures. SPs can manipulate data "off machine",  so your FE can get back to work more quickly.

There's much more that can be written about the conversion. See these articles: - the Best of Both  Worlds
LVL 36

Accepted Solution

PatHartman earned 167 total points
ID: 39747652
I use SQL Server (and other RDBMS') for most BE's.  I start with local Access tables and define all RI in Access.  Once the BE is stable, I upsize for final testing.  There are a couple of gotchas when using DAO but they're minor.  You need to include dbSeeChanges if your table uses an autonumber and you have to remember that you can't actually "see" the new autonumber (identity) value until AFTER the record is saved which can sometimes complicate processing if you are working with multiple related recordsets.

Access makes every effort to "pass-through" queries so they are processed on the server.  You can prevent this if you are not careful when using VBA and UDF functions.  There is no problem in the Select clause.  Access simply sends the query without the function to the server and when the recordset is returned, applies the function.  Other places in the query will usually cause Access to request entire tables to be brought down from the server.  You would need to solve this problem with a stored procedure.  

You may find that certain action queries will run more efficiently as true pass-through queries.  The reason for this is that Access wraps its action queries in a transaction so it can allow you to cancel the changes after they have been made.  This can take huge amounts of workspace and time.  Running these action queries as pass-through queries, access simply ships them off to the server and never gives you the option of accepting/rejecting the update.

Most slowness in queries can be resolved with proper indexes and occasionally views.

Views are updateable provided the underlying tables are updateable as long as the view returns ALL PKs from the related tables so you can create a local pseudo index.

The biggest difference when using ODBC databases is how your forms work.  Binding a form directly to a table or even to a query without selection criteria completely defeats the advantage of using a real RDBMS since you force Access to request entire tables be returned from the server and this puts a huge burden on your network.  So, don't plan on using Access filtering on your forms.  Add controls that the user can fill to provide criteria so you can bring back the minimum number of records at a time.  Depending on how the user needs the process to work, I might make a pre-form that returns a small set of data and the user then chooses one record from the set to process in detail or in other cases, a form will process one record at a time by using controls in its header that are used to select specific records.  For these forms, the RecordSource query simply references the criteria fields in the header.  Since there will be no values when the form opens, it will open empty.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question