March 25, 2017 at 1:38 pm
Hello folks,
I have what I think is a rather general question (and this is actually a hybrid question, but figured the folks on the SQL Server side may be more in the know) I have tables in SQL Server Express (local machine) and an Access front end. In my Access application, I have a form based on a table (on SQL Server) that opens up very very slow (consistently 16 seconds). I read somewhere that often times using SQL Server as the back end will slow things up. Their suggestion was instead of bringing over all the records to the form from the table, to bring just the one record that's needed at that time. To my way of thinking, I am already doing that. When I open the form, I'm essentially requesting the ID to match the record from the table with the same ID. I should add that this form does contain a sub form with related records, so I'm sure that's slowing things up as well. Maybe 16 seconds is normal and I have to live with it, but I figured I'd ask around.
For the record, let's say when I select a Veterinarian's name, it brings up a form (Form1) with her demographic info, and the sub form (Form2) details all the offices she works for and info on those offices...
thank you kindly. I would appreciate any help wrapping my head around this concept
March 25, 2017 at 2:46 pm
John524 - Saturday, March 25, 2017 1:38 PMHello folks,
I have what I think is a rather general question (and this is actually a hybrid question, but figured the folks on the SQL Server side may be more in the know) I have tables in SQL Server Express (local machine) and an Access front end. In my Access application, I have a form based on a table (on SQL Server) that opens up very very slow (consistently 16 seconds). I read somewhere that often times using SQL Server as the back end will slow things up. Their suggestion was instead of bringing over all the records to the form from the table, to bring just the one record that's needed at that time. To my way of thinking, I am already doing that. When I open the form, I'm essentially requesting the ID to match the record from the table with the same ID. I should add that this form does contain a sub form with related records, so I'm sure that's slowing things up as well. Maybe 16 seconds is normal and I have to live with it, but I figured I'd ask around.
For the record, let's say when I select a Veterinarian's name, it brings up a form (Form1) with her demographic info, and the sub form (Form2) details all the offices she works for and info on those offices...
thank you kindly. I would appreciate any help wrapping my head around this concept
hmmm...lots that could be causing you problems and without more detail tis hard to understand and provide possible solutions.
16 secs does seem very slow to return..............I am assuming that we are not talking about GBs of data ?....
.one suggestion I have is to write the query in SQL to get all the necessary info required for Form1 and Form2 based on the where clause of "Veterinarian's name". How long does that take in SQL.?
Assuming you have reasonable indices the SQL code should return pretty quickly.......if the result set returns in an acceptable amount of time, then you need to move on to how the Access form controls are passing this info to SQL and returning only the relevant data to your forms.
It is many years since I did such work, but in spite of the "purists" who scorn MS Access, .it was a quick and easy way to make a functional FE to SQL..with a very low cost of development. (free user runtime as well!)
IIRC there is someone that frequents this site ("Wendell??").....who regularly responds to MS Access queries, hopefully they will pick this thread up.
I would also suggest you post your question on specific MS Access forums (eg http://www.utteraccess.com) with as much detail that you are able to provide.
not much help, I know...but Good Luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 25, 2017 at 2:53 pm
thank you! Yes, indexes are good and I can return the same info in SQL in less than a second. I'll think about your suggestion t return all the info I need in Form1 and Form2 and see how it goes.
again, thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply