?
Solved

Access and SQL Server

Posted on 2013-12-30
3
Medium Priority
?
468 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 668 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 664 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 40

Accepted Solution

by:
PatHartman earned 668 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

Industry Leaders: 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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

621 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