Access 2010, Development for SQL Server Backend

Posted on 2013-11-22
Last Modified: 2013-11-30
I had originally written an app for a client in Access 2003.  The database was split with the data residing on the server and a local copy on each users desktop.

The data storage requirements have grown beyond the 2GB upper limit so we are going to make the backend SQL Server, also moving the application to Access 2010.

I moved the app to Access 2010 and ran the SQL upsize wizard to create the backend in SQL Server.

I have many reports, queries and coding in modules.  The coding directly interacts with the database.  Adding records, retrieving records etc...

When I first 'upsized' I had an issue because my linked tables all had a prefix of 'dbo_'.  For example 'tblAccounts' became 'dbo_tblAccounts'.  Any queries based on 'tblAccounts' didn't find it.  I resolved that by removing the 'dbo_' prefix from the linked table names.  Then the queries worked just as they had before the 'upsize'.

The good news is that as I run thru all the functionality of the application it all seems to work and the response is good.  Of course I'm the only one in the database.

Should I be changing any application options, coding techniques, query design or form layouts to  perform better using SQL Server?
Question by:mlcktmguy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
LVL 36

Accepted Solution

PatHartman earned 250 total points
ID: 39669092
FYI - When you use the upsizing wizard in Access and choose the option to link the tables, Access handles this for you.  It renames the local tables with a suffix of _local and removes the owner prefix from the linked SQL Server tables.  If you use some other method to upsize, you will need to remove the owner prefix manually as you did.

The biggest difference between old-style Access applications and client/server applications is that with c/s, forms should be bound to queries that select only the columns you need and there should be selection criteria to limit the data.

In my apps I use several techniques depending on the function of a form.  Forms that have only one basic search criteria such as customerID or customer name have 1 or 2 search boxes in the form header.  If the list is less than 10,000 rows, I use a combobox otherwise I use a text box (which is actually more flexible).  The RecordSource query refers to these two controls.

Select ... From ... Where (fld1 = Forms!yourform!fld1 or Forms!yourform!fld1 Is Null) AND (fld2 LIKE "*" & Forms!yourform!fld2 & "*" or Forms!yourform!fld2 Is Null);

I have two buttons also.  One to clear the criteria fields and the second to requery the form.
If Me.fld1 & "" = "" Then
    If len(Me.fld2) < 5 Then
        Msgbox "Selection criteria is required.  Please select from the list or enter at least 5 characters in the customer name search box.", vbOKOnly
        Exit Sub
    End If
End If

Open in new window

I just picked 5 as an arbitrary number.  Use whatever you think is reasonable and I only did that because since we are using two fields, I have to allow for one of them to be null and I don't want to run a query with no criteria.
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 39669175
Some other issues:

You will likely need "RowVersion" or "TimeStamp" columns in each of your tables (the wizard may have added these). These are special columns in SQL Server.

Make sure all tables have a Primary Key, and that the PK is also showing on the linked table.

Boolean fields are translated over to Bit fields. You need to insure that all of those existing fields have a value (0=False, 1=True), and that you set the Default of those Bit fields to 0 (or 1, if they should default to True).

If you've used any Access-centric coding, like referring to a Form directly in a query, then you'll have to either change that, or leave the query in Access. Other examples would include using IIF in a query (you would use a CASE statement in SQL, perhaps), or using Date() or Now() (you use GetDate() in SQL).

Author Comment

ID: 39669183
Thanks you, very helpful.  I also remember, or at least think I remember that the default record locking on all forms should be set to 'No Locking'.
Has anyone found that to be true?

How are record clashes handled?  For example: Two, or more users are editing and revising a record at the same time but they Save the record at different times.

Since there is no one right answer but I'm hoping for lot's of input,  have increased the points and will split them amongst all that are helpful.
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!

LVL 36

Assisted Solution

PatHartman earned 250 total points
ID: 39669300
When the BE is SQL Server, you have no control over record locking.  The server handles it all.  For Jet/ACE tables use "No Locking".  SQL Server has options also but they are controlled by the DBA.

Access can tell immediately when two users are accessing the same record because of its close connection with Jet/ACE but when the tables are ODBC, there is no way to know until you try to save a record.  The use of RowVersion/TimeStamp fields makes the process more efficient since it allows the database engine to determine with one test if the record has been changed.  If your tables don't include this type of column, the engine needs to compare each field to the corresponding table field to determine if they are different.  There are some tables where you will not want to use RowVersion.  If parts of a record are updated on one form and parts on another, not using RowVersion may result in fewer conflicts since the database engine only cares about the columns that actually changed.  So if user A updated col1 and col2 after user B read the record and before he saved but user B updated col 3 and col 4, there would be no conflict message but if RowVersion is used, there will be a conflict message.

If a conflict occurs, SQL Server returns an error.  It really depends what type of application you have and how your users work whether conflicts will be a problem.  For most applications, users will rarely be updating the same record at the same time.

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 100 total points
ID: 39671749
The other experts have some great advice.  If you want more information, I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

It includes many of the topics discussed, including front-end coding requirements, update concurrency, RowVersion, managing locks, performance, etc.

Armen Stein

Author Comment

ID: 39680981
Thanks for the responses.  For performance purposes it sounds like the local Access queries would be better converted to SQL Server queries.  Is that correct?

I'm not a SQL person so how wold I go about doing that
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 39681149
In general, anything you can pass off to the server will enhance your performance. The only way to know for sure is to try out both and see which is better.

Queries in SQL are Views (for SELECT type queries). Views can be updateable, but there are some restrictions, and you must be sure to Join the tables correctly (just like in Access). As a general rule, if the query is updateable in Access, the View will be the same in SQL Server. See this MSDN article:

Stored Procedures (for UPDATE/DELETE/INSERT queries). SPs are used a little differently, in that you often pass "parameters" to the SP, and let the server take care of the rest. For example, here's one from a project I'm working on:

ALTER PROCEDURE [dbo].[UpdateAutoNumber] 
	-- Add the parameters for the stored procedure here
	@TableName varchar(50), 
	@NewValue varchar(50)  
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

    -- Insert statements for procedure here
	UPDATE Auto_Number SET Last_Nbr=@NewValue WHERE Type=@TableName

Open in new window

To use that, I'd create a Command, add two Parameters to that command named the same as the ones shown above, and then execute that command.
LVL 36

Assisted Solution

PatHartman earned 250 total points
ID: 39681986
For performance purposes it sounds like the local Access queries would be better converted to SQL Server queries.  Is that correct?
Access makes every effort to "pass-through" ALL queries to the server.  You can defeat it so you need to understand a little about how this works.  The things that cannot be passed-through are pretty obvious when you think about it.  The server doesn't run VBA so functions you create or that are specific to VBA cannot be passed to the server.  If the function is used in the SELECT clause, it won't cause a problem.  Access sends the query without the function to the server and then applies the function on the returned recordset.  So stick to functions that have SQL equivalents whenever possible.  I almost always develop with SQL Server or other RDBMS as the BE.  ACE is a rarity and in only a few cases did I need to actually create a pass-through query.  Certain bulk updates are faster when sent to the server.  Occasionally, I will also create a view to make querying easier.

Technically, a pass-through query would be faster but that's only because Access tends to be chatty.  In either case, the query itself would be processed at the server and only the requested rows returned.  In most cases, for a select query, you would be hard pressed to find a significant speed difference so giving up an updateable query in favor of a non-updateable one will just cause you more work.

Author Closing Comment

ID: 39687584
Thanks to all that participated

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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 Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

749 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