Upsizing to SQL Server

I don't see what advantage trying to upsize again will do you.

You've almost nailed it with your comment
"forms that are retreiving 80,000+ records and access is filtering them on the front end"

No filtering at all in a Client Server application.
You *can* use queries to pull over only selected records first rather than filtering later... but Jet might not always be able to do that - depending on the buildup of the query.

It's not necessarily easy or quick - and moving to an ADP isn't a magic solution. (It too could be pulling over large numbers of records - just that little bit quicker).

Creating Stored Procedures on the server and using those to return your data through code would be your most efficient bet - but that will take longer.
As a makeshift - on the fly SQL or updated queries that don't reference Access objects or functions should imporve performance - a long as you're pulling over only those required records.

There may be no advantage to actually using the upsizing wizard.  The tables are already in SQLServer.  How they got there I'm not exactly sure.  My guess is it wasn't using the upsizing wizard but with SQLServer tools and then a data import from the .mdb.  In any case, the only thing that has changed is the tables are now linked to SQLServer via ODBC.  The app still uses Access queries inside the forms.  

I know that I'm going to have to change the way the application works.  It was built using the old file-system Access methodology, as reflected by the client filtering.  I expect to use stored procedures on the server to return my data.  Would importing all forms, code, etc. into an .adp that points to the SQLServer db help improve performance(eliminating JET)?  And if I went this route, can .adp's be distributed like .mde's(which is currently how they are doing it)?

ADPs can be distributed similarly - and made into ADEs.
But moving to an ADP isn't the standard Client Server technique.
It's one option.
But many (indeed - most) Access clients to server data are still mdb's. (Jet isn't the constant hinderance that many think - it can be a real help).
You just have to use them with more care.

Filtering (as you rightly point out) is an utter no no.  But then I generally don't use in File Server mode either (less of a hike when the time comes).

As I mentioned you could even still use Access queries to carefully pull data over.
But SP's via code are the best bet - whether you're in a mdb or an ADP.

A hike?!  More like a trek!!  Here's where I'm at:  I need to get rid of this filtering nonsense.  Currently, thats the way the users know how to use the system.   They open the form(filter by form) and choose to filter via any one or more of a number of controls(text, combo, checkbox, etc).  Then they hit the darn filter button!  Its great, I know.  

I'm looking for insight into how to maintain the interface for the users, but revise how the data is grabbed.  Instead, of opening the form and having all these records, I would rather it have ZERO records from the start.  THEN, they can play with the controls, hit a "Lookup" button, and return a limited number of records.  The query being used on this form is calling VBA functions.  I was thinking I could create a view in SQLSERVER from the access sql query and then program the where clause to limit records depending on the control values.  But, the query contains VBA functions in it, so I can't do that.  

Can this be as simple as, editing the query so it returns ZERO records, i.e. where 1=2 kind of thing, and then when the LOOKUP button is clicked, the code modify's the query, and refreshes the form??

The functions in the query - do they operate on the returned data?  Or form part of the Where clause?

Looks to me they operate in both, select statement, and where clause.  ???

The "dreaded" Jet can actually do a good job of interpreting what it can send as a TSQL statement without pulling back too much.  But there are limits - and functions everywhere could hamper that (for example returned values the formated using a function should be relatively efficient still).

It shouldn't be too hard to use these queries as a basis - to generate on the fly SQL (perhaps saving that to temporary querydefs if you so chose).

Thanks but this doesn't really help me out.    I have changed the query so it returns no records.  Now I want to modify it in VBA after users chooses criteria.  How can I access this query via vba for modification?

Would I have to go through every control on the form to check if the user has chosen it for a parameter?

Perhaps there is a way to reference controls on a particular page of a tab control???

There's a couple of ways of doing what you need to do here.
One way is indeed to ammend the SQL of that saved query directly.
Another is to create the SQL and assign it directly to the form's recordsource.
And another is to use recordsets to get the precise data - again with an on the fly SQL string - and assign that recordset to the form.
(And yet another of course to use that recordset to write unbound values to the unbound form - and write them back again similarly afterwards).

Fundamentally you need to create an SQL statement though.
The query you have will already give you a good indication of that it will be.  You just need to change/insert the bits you want and then use it.
To change the SQL of a saved query you can just use

Dim strSQL as string

strSQL = "SELECT ......" 'your SQL statement
CurrentDb.QueryDefs("YourQueryName").SQL = strSQL

I think a major issue here is the fact that every control in this form is bound, due to the fact that the filtering was the main way of getting at the data.  I changed the recordsource to limit the records returned to the form to zero, so that the bound controls don't err out.  There is an autonumber field though, that can't be edited, which is an important field to enter search parameter into

I'm not sure I follow what you're saying there.
What is it you're wanting help with in implementing the changes?

1.)  There is a textbox control that is an autonumber field in the table.  In filter mode, users can enter text here and click the filter menubutton, and the record with that textbox value is filtered to.   But because I am returning zero rows now, the textbox in question shows 'autonumber' and cannot be typed into.  How can I implement the search functionality on this bound form, given there is this autonumber field.  The other controls are enabled and seem(haven't tried them all) like I can use them to get parameters for the editing of the underlying query.

2.)  When the user does a search, they can choose on many different controls.  They may choose only one control, maybe a textbox, or they may choose several.  How would you suggest I capture just the control values they have chosen, and leave the others out of the new query.

1) Don't use that autonumber textbox control.
Have that for display somewhere below (as it's obviously something they relate to).  Have a filtering set of controls in the header.  An unbound textbox that they *can* enter data in to.
After they've typed soemthing in there you could have something as simple as

Me.Recordsource = "SELECT * FROM TableName Where AutoNumberField = " & Me!txtFilterBox

2) You could either offer a range of controls - all as above or to limit it one control with a combo where they select which field they want to search in.  (I almost always go for the latter - offering as many controls a they wish).

I truly appreciate your feedback, but I think you are missing some points here.  

This application is already built.  I'm trying to work within the context of what I have.  I do not want to change the interface OTHER than the way it is currently working(which is this darn filtering crap!).  

Because it is filtering, everything is bound.  I have modified the forms underlying query to return zero records when the form opens.  This leaves me with the autonumber textbox disabled.  This textbox needs to be enabled, as users need to use it a search parameter.  The other controls are enabled, and I've successfully created the search with just one of the textbox controls right now.  I need to figure out how to determine which controls are being used as search parameters, and then construct the query based on those parameters only.  

It seems to me my major obstacle here is the fact that this form and the controls in it are bound.  I've built forms that have been unbound, used recordsets as the datasource, and updated all via vba.  Again, I'm trying to work in the context of what I have, and what will be the easiest solution, aside from creating a new unbound form(its a very complex form)

Fair enough - but I think you might be missing a point or two.
There's nothing I suggested that required the data entry form to be unbound.

What you will not be able to do is enter search criteria into the data entry fields any more.
Access allows you to do that in Form filter mode - it's a UI 'gift' (which I and many other developers happen to hate).
Working within the context of what you have is only going to be possible to an extent.  The move to server data requires at least some changes - it's that simple. :-(

If you're wanting to use server data properly (and hence leave filtering behind) you will not be able to offer searching through data entry fields.

What I was suggesting was some alternative fields - usually on a separate 'search' form (depending on how many there are).
Those are unbound - so text can be entered - and what's entered in them you use to construct an SQL statement and use that SQL statement to return the matching records (assigning it directly to the form - or changing the SQL of a query).

Hmm...what about this scenario:  Write code in the Form_applyFilter event that retrieves a recordset from sqlserver, and assign the recordset to the form.recordSource property?

"retrieves a recordset from sqlserver"
That's the pivotal thing here.

You're really going to have to go through Jet - i.e. execute local SQL - or a local query.
So *hopefully* Jet will do its thing and be smart enough to pull over only what it needs.

(And assuming you don't dustribute this via runtime - because of course filter by form isn't available then).

Assuming that your form is still based on a recordsource that returns no records.
In the ApplyFilter event you won't be able to retrieve the values in the different controls - as they'll have been filtered by then - and as the form contains no records - none can be returned.. so every field will be Null.
Opening a recordset (from the server or locally) still requires the creation of an SQL statement.  Just as if it were being applied directly to the form.
And we need values to build one.

So we'll have to be a bit more sneaky than that.
If we grab the filter string that Access generates (it's useless by itself as a filter applied to an empty recordsource) - and use that as a Where clause in an SQL statement.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    Me.RecordSource = "SELECT * FROM TableName WHERE " & Me.Filter

End Sub

You can try that and see how it likes it performance wise.
(Can't guarantee how efficient it will be - but Access uses different SQL syntax to T-SQL and so this is an immediate solution - whereas directly opening a recordset on the server using the filter string could leave possible problems that would need to be catered for).

That is exactly what I had in mind.  Here's my code, go easy on me!

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Dim qry As QueryDef
Dim db As Database
Dim strSQL As String
Dim strNewFilter As String
   
    Set db = CurrentDb
    Set qry = db.QueryDefs("qryInventory2") ****this is the query that returns zero records
    strSQL = qry.SQL
   
    Set qry = Nothing
    Set db = Nothing
     
    If InStr(1, Me.Filter, "FLAG") > 0 Then
              If mID(Me.Filter, InStr(1, Me.Filter, "FLAG") - 1, 1) = "."  Then
                        strSQL = Replace(strSQL, "1=2", " And " & Me.Filter)
                        strSQL = Replace(strSQL, "qryInventory2", "tblINVENTORY")
              Else
                        strNewFilter = Replace(Me.Filter, "FLAG", "tblINVENTORY.FLAG")
                        strSQL = Replace(strSQL, " 1=2", strNewFilter)
              End If
    Else
              strSQL = Replace(strSQL, "1=2", Me.Filter)
    End If
    *************I had to do the nested IF's above because the first time I filter, me.filter returns the field(s)
    *************qualified with the recordSource, i.e. "qryInventory.FLAG=...".  The second time(and all
    *************subsequent times) I click filter, me.filter returns the field(s) UNqualified.  And of course,
    *************the FLAG field is in both tables used in the queryDef.qryINVENTORY2 so I had to handle it.
    *************by qualifying FLAG everytime it was a search parameter.  
    *************Hope that makes sense.
   
    Me.RecordSource = strSQL
End Sub

It seems to work.  At the very least,  it is limiting the number of records being sent over the network(ZERO from the beginning, and then only what is returned by strSQL when searched.  Which is good I think.  But its still using the filter functionality, so I'm not sure its even a decent short-term solution. I'm still not sure whats going on in the background with regard to what JET is actually doing(the RecordSource is simply getting a valid SQL statement).  

This application was built and made to be used as a standalone, as can be seen from the way the forms were built.  WHENEVER data is requested, its done using built-in Access queries, even tho the tables are ODBC linked to SQLServer.   I need to work this out some way or another to get the processing done on the Server and make this a TRUE client/Server app.    Is this something that I'm going to have to bite the bullet and create ALL the queries in VIEWS and use stored procedures for updating, inserting, and deleting???  It seems like given the complexity coupled with the original design of the app, this amounts to practically redesigning the whole system???  thanks for you patience and input, much appreciated.

Question...when I assign the strSQL to the recordSource, the records show up in the form.  Then the user, clicks the 'filter by form' button again, changes the search paramters, and hits the filter button again.  NOW, isn't it filtering the current recordsource(strSQL)??   Should I change the recordSource back to the original query that returns zero records at the beginning of the applyFilter event?

It will briefly attempt to filter the currently loaded records - but they're already loaded (no more overhead) and are immediately replaced by an entirely different set of records.

thanks for you help.