Solved

Access and SQL Server

Posted on 2013-12-30
3
458 Views
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?
0
Comment
Question by:WoodrowA
3 Comments
 
LVL 2

Assisted Solution

by:russell12
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.
0
 
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:

http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/
http://www.jstreettech.com/downloads.aspx - the Best of Both  Worlds
0
 
LVL 35

Accepted Solution

by:
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.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

861 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