Advice on Upsizing to SQL

DBL

Registered User.
Local time
Today, 23:47
Joined
Feb 20, 2002
Messages
659
Would anyone be willing to have a discussion about upsizing a large split database to SQL? I've managed the split using the wizard and it went very well. The problem is the performance, the form is really slow on opening and as it scrolls through records.

All my queries are saved. I would imagine it's the way I've built the form and base queries that's causing the problem and wondered if anyone would be willing to have a look at it for me and see if they can make any suggestions.

I'm struggling blind with this one.

Thanks.
 
Thank you, this is helpful but I always end up with questions that can't be answered! Also, my brain doesn't work in a read and learn sort of way!
 
The article assumes that you upsized to an .adp which I NEVER do. I always leave my application as an .mdb with linked tables. That way everything still works.

What the upsizing document doesn't tell you is that SQL Server is actually slower than Jet at retrieving recordsets for your forms. The way around it is to NEVER open a form based directly on a table or on a query without criteria. You may now need to insert intermediate forms to allow your user to enter criteria that your form's recordsource uses to limit the number of rows requested from the server.
 
That makes a lot of sense. It's definitely and upsized mdb I want. Can I use the DMax function as the form opens to limit the records in the query recordset, and then would the search combo box still pull out any record selected by the user, or would I need to use an intermediate form every time they wanted to move records?
 
You couldn't use DMax() but you could use TOP.

Rather than that, I would use a query that refers to form fields for criteria. But to make this work, you would need to remove the original query/table from the RecordSource and save the form in an "unbound" state so that when it opens, it is unbound. Only the unbound selection fields would show. I think the others would be hidden. You would then need to add a button to the form to "populate" it. That button would simply replace the empty RecordSource with the name of the query that includes the criteria.
 
That's great, Pat, thank you for that. I'll have a play around and see if I can get it to work. I may come back with more questions as I hit a problem!

One question though, if I use the unbound selection field, once they make their selection do I set the forms recordsource to the query at that point, but with the query filtered to only show the record selected in the selection field?
 
Also! Should I create all my mdb in this way, even if they're not being upsized, or is this specific to ones with sql backends?
 
Last edited:
One question though, if I use the unbound selection field, once they make their selection do I set the forms recordsource to the query at that point, but with the query filtered to only show the record selected in the selection field?
You could use the AfterUpdate event of the last selection control but the problem with that is that the user needs to tab out of the field. That's why I suggested a button. Having a button will be a visible clue to the user as to what he needs to do.
Also! Should I create all my mdb in this way, even if they're not being upsized, or is this specific to ones with sql backends?
I tend to do this with all applications since you don't know what's going to happen later and it doesn't hurt to use this method even for local databases.
 
I'll need to have a play around. My form's got lots of code on various events which are throwing up errors if I make the form unbound. I can change this on future projects but, needless to say, this one is finished and would take a huge amount of reworking. I'll try out your suggestion of using a pop-up form to select the record and then refresh the main form.
 
Changing a form that is already built can be a problem. That's why I suggested an intermediate form. From your menu, open the parameter form. From the parameter form, open your real form using the where argument of the OpenForm method. The Jet developers at Microsoft assure me that even when the tables are ODBC, jet merges the where argument with the form's recordsource query so the filtering is performed on the server. You can turn on the SQL tracing facility (I'm not sure how) to see what Access is sending to the server. So you can actually examine the SQL string and also see what is returned.
 
Once the form's open and they're moving through editing records, does it annoy the user having to click to get an intermediate form every time or, once they've opened the form using the paramater box, can I then use the combo to move through records?

Sorry if these are obvious questions but I'm just trying to get my head round a new way of working!
 
Yes. But I would use a different query. This query should reference the unbound form fields for the selection criteria. Again, I suggest a button. In the click event of the button, replace the RecordSource with the new one.

Me.RecordSource = "yourqueryname"

If you prefer, the first query could have referenced the intermediate form's unbound fields rather than utilizing the where option of the OpenForm method.
 
This is starting to make sense!

I'll let you know how I get on.

Many thanks, Pat.
 
Something else that's come to mind, would nested queries cause a problem? I have one form in particular which is built on nested queries and that form is really slow. But that could all be down to all the records being available as the form opens.
 
I think the SQL server engine prefers subselects to nested queries. Try rewriting the query to see if that helps.
 
My ignorance is really showing through. What's a subselect as apposed to a nested query?
 
Hi there

I've been having problems getting the form to open unbound, without the fields showing Name# I couldn't set the recordsource for the form as it opened without getting a paramater box looking for the search field. I'm now have the form (and the search form) opening at the same time in add mode, with an Edit button. Hit the edit button and you can filter the form and it changes the recordsource at that point. It may not be pretty but it seems to be working! Not sure if there's an issue doing it this way which may not be apparent at the moment.
 

Users who are viewing this thread

Back
Top Bottom