Solved

Access and SQL Server

Posted on 2013-12-30
3
456 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
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 167 total points
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now