Forms Subform Ms Access Conversion to SQL Server - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Forms Subform Ms Access Conversion to SQL Server

Ira Saunders

8 months ago

Hi Guys,
First post.
I took Richard's SQL Server Course and watched every episode twice. Just trying to put together all I've learned. I have an MS Access client database with over 600,000 clients and their orders. Over 1,000,000 orders.
Basically, it's a main client form with a subform for orders and then a subform on the orders subform with the order details. Additionally, I do have a listbox on the main client form that displays the client's first name, last name and phone to aid in lookup. I have search box that the user can start typing in the client's name and the listbox will move to the corresponding record after each keystroke and the main form updates to match the record highlighted in the listbox. When the user reaches the desired client, they can view orders, details, totals, etc.

I was hoping for a little guidance on the best way to accomplish the same thing in SQL Server. I've done the conversion to SQL Server and connected all the tables and got everything work in SQL Server. But, without changes to the way the program receives the data, everything is VERY, VERY slow (understandingly). Now is where I go about optimizing everything for SQL and I know I can't pull over 600K records.

I am not asking anyone to write any code, but just point me in the right direction.

Do I use ADO and connect my main form to a query displaying the first 20 records? Then once the user selects the record, I make everything else update. Do I just use the form already connected directly to the table but limit the number of records initially? Do I not bring over any records initially and just let the user search for a single client specifically.

Any guidance or examples would be greatly appreciated. Thank you, Ira Saunders

	Sami Shamma @Reply 8 months ago
Have you made use of pass-through queries?

	Ira Saunders OP @Reply 8 months ago
Yes, I have tried the pass-through queries and been successful with those, but again not sure the best way to go about it. Pass-through queries to base my form on or ADO, or something else?

	Sami Shamma @Reply 8 months ago
Another thing to look out for is combo boxes. They are notoriously slow with SQL Server. The solution we have used is: Once you open up your database, you download your lookup tables that you have in your combo boxes to temporary tables on your front-end. This is a very fast process. Then you go ahead and use your local temporary tables to be the row source for your combo boxes.

Kevin Yip @Reply

8 months ago

Hi Ira, is your SQL Server database on a local network or a host like Winhost on the Internet?  Internet speed is at least 10 times slower than local speed.

Speeding up database operations basically need two things: (1) minimize the number of data retrievals, updates, and insertions, and (2) minimize the number of rows in each retrieval, update, and insertion.  This is highly dependent on the particular needs of your business.  But the rule of thumb is always to minimize the number of rows processed.  This is true whether your database is online or local.

---
To those who don't know me, I'm retired IT manager who have used Access since the mid-1990s.  I was invited here by Richard in 2023 to post in the forum.  I post here occasionally, sometimes rarely.  I've never taken any of Richard courses, so I can't help with those.  I can only help with general Access topics.

	Ira Saunders OP @Reply 8 months ago
My new SQL Server Database in on Winhost. People keep suggesting Pass-Through queries, but pass-through are not updatable so how can I use them as the basis for a client form with orders and order details. Clients need to have their addresses updated and new orders created. So what I am asking is how would you load your client form to minimize the number of data retirevals. ADO?

	Richard Rost @Reply 8 months ago
Use the pass thru query to view large sets of data for something like a continuous form or a report. Then open a single form normally that's bound directly to the table and only pull that record. Should load quick.

	Richard Rost @Reply 8 months ago
If you need to edit data in a continuous form another trick is to pull down the data locally into a temp table, edit that, then have a SAVE button on your form that will write the edited records back to the server. Handy for things like orders.

	Ira Saunders OP @Reply 8 months ago
So maybe the passthrough query for the client listbox and once selected just load that record only? I was using TOP 10 via ADO to load the first 10 records, then drilling down from there.

	Richard Rost @Reply 8 months ago
Yeah that should work.

Kevin Yip @Reply

8 months ago

Ira  For complex queries, the most expedient way is running stored procedures on SQL Server instead of passing through a complex SQL statement to SQL Server.  For simple queries, sometimes you can get by with just regular Access queries.  The key is, as I mentioned above, few queries and few records per query.  For instance, running one query that returns 100 records is a lot faster than running 100 queries that return 1 record per query.

In your first post, you say you have some kind of search-as-you-type feature.  Do you run a query every time the user presses a key?  That is one way the slowness can occur.

What is your Internet speed?  The upload speed is important too, since you retrieve and send data when you run a query.  For a single user, 5 megabits per second (the bare minimum nowadays) should be fine.  If you have multiple concurrent users, you naturally need more speed.

Also, ask your users if the slowness affects their work.  A query that takes 10 seconds to run may be slow to some users but not to others.  When we search full text on this site, it takes several seconds too, but I don't think any of us is bothered by that.  We often see occasional delays in websites.  The point is that you don't want to spend time on re-designing your database when your users aren't even bothered by its shortcomings.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View