Solved

Access 2010, Development for SQL Server Backend

Posted on 2013-11-22
9
844 Views
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?
0
Comment
Question by:mlcktmguy
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
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
Me.Requery

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.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
Comment Utility
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).
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
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.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Assisted Solution

by:Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006 earned 100 total points
Comment Utility
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:

http://www.JStreetTech.com/downloads

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

Cheers,
Armen Stein
0
 
LVL 1

Author Comment

by:mlcktmguy
Comment Utility
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
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
Comment Utility
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:

http://msdn.microsoft.com/en-us/library/410e2812-4ebe-48b2-b95f-c7784f1c4336(v=sql.105).aspx

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)  
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

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

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.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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.
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
Comment Utility
Thanks to all that participated
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 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

10 Experts available now in Live!

Get 1:1 Help Now