Access and SQL Server

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.