SQL Server Backend, Efficiency, Pass Thru Queries

A client's backend data is currently in Access, however they will be outgrowing the 2GB size limit eventually.  In anticipation I am doidng preliminary testing on converting the backend to a SQL Server database.

The good news:
1. I converted the Access DB into a SQL Server DB using SQL Server Management Assistant.
2. I created a DSN for the SQL database
3. In the front end Access module
       I removed all of the tables linked the the Access backend MDB
       I linked all of the same/removed tables from the new SQL DB to the front end Access DB
       I renamed all of the SQL linked tables to the same names as the original Access linked tables.  This way all of the queries and           internal references still worked as is.
4. I ran the front end Access module and everything works.

The Not so Good News
   Everything runs slower.

I use a lot of queries in my application.  Some are created in the query designer and some of the SQL is created and executed ad-hoc in the VBA code behind the app.

As I read more on SQL it seemed clear that I should convert some of my queries to 'Pass Thru' queries.  Particularly the one's that work with large data sets.

I opened one of my existing queries, then did a 'Save as' after selecting 'pass thru' as the query type.  The new query doesn't work.

The original query was created in the query designer but here is the SQL code behind the query:
SELECT tblInstallPay_Main.ID, tblInstallPay_Main.BRT, tblInstallPay_Main.PropertyID, tblInstallPay_Main.PayPlanTypeID, qryADrop_PayPlanTypes.PayPlanType, tblInstallPay_Main.PeriodInMonths, tblInstallPay_Main.StatusOfPlanID, qryADrop_PayPlanStatus.PayPlanStatus, tblInstallPay_Main.DateEstablished, tblInstallPay_Main.AgreementStartDate, tblInstallPay_Main.AgreementEndDate, tblInstallPay_Main.NumOfPayments_ExcludeDownPay, tblInstallPay_Main.DownPayment, tblInstallPay_Main.MonthlyPaymentAmt
FROM (tblInstallPay_Main LEFT JOIN qryADrop_PayPlanTypes ON tblInstallPay_Main.PayPlanTypeID = qryADrop_PayPlanTypes.UseID) LEFT JOIN qryADrop_PayPlanStatus ON tblInstallPay_Main.StatusOfPlanID = qryADrop_PayPlanStatus.UseID;

Open in new window


When I try to double click to open the query it presents me with the 'Select Data Source' dialog box.

After selecting the appropriate DSN I get a message 'ODBC Call failed'  [MIcrosoft][ ODBC Driver for 11 for SQL Server][SqlServer] Invalid object name 'qryADrop_PayPlanTypes'. (#208)

What is the issue?

I also create and execute many SQL statements in the VB code behind the application.  Some examples:

An insert:
insertString = "Insert Into tblzTmpWk_tblTaxRecs_Local  (     [TaxHeaderID], [PropertyID], [BRT], [TaxYear], [PrincipalAmt], [PenaltyAmt], [InterestAmt], [LienCost], [AttyFeesAmt], [EligExpAmt], [DateOfNumbers], [PayStausID], [Active_YN], [PrincipalBal], [PenaltyBal], [InterestBal], [LienBal],  [AttyFeesBal], [EligExpBal], [TRBAmount] ) " & _
                                                     " Select [TaxHeaderID], [PropertyID], [BRT], [TaxYear], [PrincipalAmt], [PenaltyAmt], [InterestAmt], [LienCost], [AttyFeesAmt], [EligExpAmt], [DateOfNumbers], [PayStausID], [Active_YN], [PrincipalAmt], [PenaltyAmt], [InterestAmt], [LienCost], [AttyFeesAmt], [EligExpAmt], [TRBAmount]   " & _
                                                 " From qryTaxRecs_wTRBAmounts_InSelectedHeader " & _
                                                  " Where ( [TaxYear] Between " & passedFromYear & " and " & passedThruYear & " )"
                                                  
DoCmd.RunSQL insertString

Open in new window


A simple update query:
'
updateString = "Update tblzTmpWk_tblTaxRecs_Fees_Local " & _
                    " SET [FeeWorkingBalance] = [CurrBalanceAmt] "
                    
DoCmd.SetWarnings False
DoCmd.RunSQL updateString

Open in new window


Is there any way to make the queries built in code execute more quickly or efficiently?  Can these be turned into pass-thru queries?

Thanks in advance for any help.  I was pleasantly surprised that the migration to a SQL backend was pretty seamless but the performance is much slower to the point of not being acceptable.
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
did you set the ODBC Connection Str  of your Pass-Through queries?


create this query

SELECT Connect, Name, Type
FROM MSysObjects
WHERE Type=4

run it and copy the any of the values in the Connect field

in the design view of your pass-through query, click property and add the the connect string to the  ODBC Connection Str  property

ODBC; <connect string here>
0
PatHartmanCommented:
That always surprises people but is almost always the case.  The reason is the way that Access apps are built by people who don't have any experience with client/server technology.  For example, using only Jet/ACE, people tend to build forms that are bound to queries that have no criteria or even directly to tables.  Users then either scroll or use filters to zero in on what they want.  The Form opens immediately because Access opens it as soon as it has enough data to populate a few records.  Notice that the y part of record x of y is not immediately populated.  With a SQL Server BE, the form also opens pretty quickly but behind the scenes, Access is dutifully sucking down every single record from the table or query from the server.  So, if the BE table has 500,000 records, EVERY SINGLE ONE of them must be dragged down from the server so that the Form can filter the recordset locally.  The obvious change (and it may be all you ever need) is to use criteria up front to do the filtering so that the query only requests the records the user actually wants to see.  No user is going to look at 500,000 records so it is silly to bring them across the network every time the form opens.  So - ALWAYS bind your forms to queries that select the minimum numbers of columns and rows.

FYI - Access attempts to make ALL queries pass-through.  You can defeat it though so you have to be careful what types of functions you use and in which clause you use them.  If the Function is one which has a direct translation to SQL Server, Access will pass through the function.  If not, Access will request the base data and apply the function locally after the recordset has been retrieved.

Although Access attempts to pass-through all queries, pre-defined pass-through queries are definitely faster for bulk updates and deletes.  The other speed enhancer is updatable views.  Whenever you have certain joins and criteria that you use all the time, creating a view can speed up the process.  

Occasionally, you will need to take the final step and create a stored procedure.  I have never had to use one for a form but I have used them in apps where I have extensive batch processes.

So, start by optimizing the queries used as recordsources for your forms.  Also, if you have a lot of lookup tables that don't change frequently, you can consider keeping copies of them locally.  You just have to give the user a way to update them as needed should one change during the day.  In the apps where I use this technique,  I refresh the ones that change with some regularity each time the app opens but don't automatically refresh the others.  Make sure you leave the original tables in the BE to be used for RI.

Pass through queries are not updateable so unless you want to completely rewrite your app, you want to start with the method I suggested (queries with criteria).  I have been using SQL Server, DB2, Oracle, etc for over 20 years with Access as the FE and find that apps bound to linked tables work just fine as long as your forms are all bound to queries with criteria.  In most cases, my main forms select only a single record.  In some cases, I have intermediate forms that return lists and then when you click on a record in the list, you get the bound form with a single record.  The forms bound to the "lists" can use pass-through queries if necessary since you don't use them for updating.

And finally, NEVER use domain functions in queries.  Each domain function runs a separate query so if you are returning 10,000 rows, you are running 10,001 queries.
0
Dale FyeCommented:
The syntax for the pass-thru query must be the appropriate syntax for SQL Server, and you cannot reference any objects that are in the Access application, only SQL Server tables, views, stored procedures, and SQL Server user defined functions.

Your original query looks like:

SELECT tblInstallPay_Main.ID
, tblInstallPay_Main.BRT
, tblInstallPay_Main.PropertyID
, tblInstallPay_Main.PayPlanTypeID
, qryADrop_PayPlanTypes.PayPlanType
, tblInstallPay_Main.PeriodInMonths
, tblInstallPay_Main.StatusOfPlanID
, qryADrop_PayPlanStatus.PayPlanStatus
, tblInstallPay_Main.DateEstablished
, tblInstallPay_Main.AgreementStartDate
, tblInstallPay_Main.AgreementEndDate
, tblInstallPay_Main.NumOfPayments_ExcludeDownPay
, tblInstallPay_Main.DownPayment
, tblInstallPay_Main.MonthlyPaymentAmt
FROM (tblInstallPay_Main 
LEFT JOIN qryADrop_PayPlanTypes 
ON tblInstallPay_Main.PayPlanTypeID = qryADrop_PayPlanTypes.UseID) 
LEFT JOIN qryADrop_PayPlanStatus 
ON tblInstallPay_Main.StatusOfPlanID = qryADrop_PayPlanStatus.UseID;

Open in new window


This includes references to two queries (Access objects).  If you rewrite those two queries as views in SQL Server, you can then reference them in your pass-thru query.  But for starters, try just dropping those two joins and the fields that go with them from the query and see if it runs properly.  

You will also need to set the ReturnsRecords property of the query to Yes in order to see the results of your select statement.  For update, insert, and delete queries, you can set that property to No, unless you want a way to pass error or completions status messages back to the calling code.

Keep in mind that pass-thru queries will not be updateable.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
@Rey/@Pat,

That is strange, I didn't see either of your responses when I started my post, even though you both posted over an hour before I did.
0
PatHartmanCommented:
That's what they all say:)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you're migrating to SQL Server you should transform your Query Access in T-SQL (SQL Server language) or Ansi-SQL (standard SQL that all RDBMS knows).
You'll also need to reindex all the indexes and maybe create few more.
0
PatHartmanCommented:
Vitor,
Do you understand that pass-through queries are not updateable and so using one as the recordSource for a form would render that form not updateable?  Yes, you can do that but do you need to?  No, not unless you are in a position to add code to the form to handle all CRUD operations yourself.

As to indexes and RI - the upsizing wizard copies them if you have checked the correct boxes but it does pay to check.  There is a sweet spot between having enough indexes to make queries work more efficiently and having too many so that you slow down insert/update/delete operations.  If you have sufficient knowledge of SQL Server (and also sufficient permissions), you can take a look at the execution plans that are created for queries sent by Access to determine if indexes are being used.  The query engine uses lots of information to determine if it should use an index or some other method to retrieve data.  Just because you have an index on Gender doesn't mean that the query engine would ever use it and in fact, it probably wouldn't because statistics would indicate that each code value brings back too large a percentage of the overall table population so that a full table scan would ultimately be more efficient if that were your only criteria.
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
I'll add to the other comments here to say that after migrating the BE to SQL Server, there's a big difference between making it work, and making it work well.

We've done whole projects for clients who wanted to migrate, and I can tell you that the upsizing to SQL Server is the easiest part!  All the Access application changes are definitely more time consuming.

Pat makes some good points about update forms that are opened to huge recordsets - this is handled fairly well by ACE, but not so well with a connection to SQL Server.  Passthroughs help a lot, but as noted they have their own limitations (they're read-only, plus they don't work for master/child relationships on subforms and subreports).

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 some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Cheers,
Armen Stein
0
mlcktmguyAuthor Commented:
The specific problem in tis case was that I had references to non-SQL objects so I awarded the most points for that answer.  However, many of the answers had valuable information on the process and considerations that must be addressed when moving to a SQL backend.

All of the information is greatly appreciated.  As I am just beginning the process I'm sure I will have many more questions.

Thanks to all for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.