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;

Select allOpen 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

Select allOpen in new window

A simple update query:

'
updateString = "Update tblzTmpWk_tblTaxRecs_Fees_Local " & _
                    " SET [FeeWorkingBalance] = [CurrBalanceAmt] "
                    
DoCmd.SetWarnings False
DoCmd.RunSQL updateString

Select allOpen 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.