Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

Access 2010, Development for SQL Server Backend

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?
  • 3
  • 3
  • 2
  • +1
6 Solutions
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
mlcktmguyAuthor Commented:
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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
mlcktmguyAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
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.
mlcktmguyAuthor Commented:
Thanks to all that participated
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now