Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access 2010, Development for SQL Server Backend

Posted on 2013-11-22
Medium Priority
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 39

Accepted Solution

PatHartman earned 1000 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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 39

Assisted Solution

PatHartman earned 1000 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 400 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 85

Assisted Solution

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

Assisted Solution

PatHartman earned 1000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

604 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