Conversion from Access Jet to SQL Back-end
I recently posted a request for assistance with converting a client from the Jet Back-end to SQL Server 2008 -- your responses were extremely helpful. BUT now that I've implemented the recommended changes have noticed significant delays during testing -- after users make record selections from combo-boxes - as well as during form loading. I could really use some help to know where to look for possible reasons for this. The application loads up fine and some forms are okay -- but the most important forms of course are creating the problems.
There are some calculations that need to be performed OnCurrent -- to calculate # of days out --and to update some other fields with results based on the days out x a factor -- this function may be the culprit. I don't know where else to put this code. It's pretty important. I don't know how to push some of the computing to the server -- if that would help... I've stepped through the code and of course when I do that I don't see any snags anywhere...
Any help would be appreciated. Best, Eileen
There are some calculations that need to be performed OnCurrent -- to calculate # of days out --and to update some other fields with results based on the days out x a factor -- this function may be the culprit. I don't know where else to put this code. It's pretty important. I don't know how to push some of the computing to the server -- if that would help... I've stepped through the code and of course when I do that I don't see any snags anywhere...
Any help would be appreciated. Best, Eileen
Last Comment
ASKER
Thanks a lot. I did change combo-boxes to pass-thru's -- and modified all SQL code queries as suggested. I went through 100 queries and made the recommended changes -- so I'm not using the same code -- I basically rewrote the whole thing -- The straight ADO calls is probably where I'm lacking -- since most of the code is DAO --
Anyway -- I'll wait to see if anyone has any suggestions... Thanks for your comments. Sorry - won't appoint points for opinions as that's not what I'm seeking here. Hope you understand.
Best, Eileen.
Anyway -- I'll wait to see if anyone has any suggestions... Thanks for your comments. Sorry - won't appoint points for opinions as that's not what I'm seeking here. Hope you understand.
Best, Eileen.
ASKER
P.S. You implied that I wasn't willing to "spend the time" when I've spent 2 weeks doing nothing but rewrite this thing. What in my question made you think that I did nothing but convert the data -- I'm just curious because I will more specific in the future.
>>since most of the code is DAO <<
That is a problem.
>>Sorry - won't appoint points for opinions as that's not what I'm seeking here. Hope you understand.<<
Absolutely. Besides I don't need them.
>>You implied that I wasn't willing to "spend the time" when I've spent 2 weeks doing nothing but rewrite this thing.<<
2 weeks ? Wow! I impressed. Depending on the complexity, I would estimate at least 3 or 4 months. But then I am talking about re-analyzing the entire application to take advantage of the functionality of SQL Server. That includes changing the table structure to take advantage of more appropriate data types than available in MS Access. Adding indexes and constraints where necessary. And above all else remove all Dynamic SQL from your code. By that I mean re-write all calls using Stored Procedures and not using any Dynamic SQL in them either.
>>What in my question made you think that I did nothing but convert the data <<
The outcome you portrayed very vividly in your original question.
But you seem annoyed, so I will wish you the best of luck in your endeavors.
That is a problem.
>>Sorry - won't appoint points for opinions as that's not what I'm seeking here. Hope you understand.<<
Absolutely. Besides I don't need them.
>>You implied that I wasn't willing to "spend the time" when I've spent 2 weeks doing nothing but rewrite this thing.<<
2 weeks ? Wow! I impressed. Depending on the complexity, I would estimate at least 3 or 4 months. But then I am talking about re-analyzing the entire application to take advantage of the functionality of SQL Server. That includes changing the table structure to take advantage of more appropriate data types than available in MS Access. Adding indexes and constraints where necessary. And above all else remove all Dynamic SQL from your code. By that I mean re-write all calls using Stored Procedures and not using any Dynamic SQL in them either.
>>What in my question made you think that I did nothing but convert the data <<
The outcome you portrayed very vividly in your original question.
But you seem annoyed, so I will wish you the best of luck in your endeavors.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hey all -- I appreciate all of your comments and direction --
acPerkins, I was not as annoyed as I was confused (and tired) -- I guess emotions don't come through too clearly in text, so I'm sorry about that. I'm getting confused because I have found disparate views/opinions on whether or not I should/need to convert to ADO, which I had thought was recommended -- and if you were to pull up my previous questions might see some of those opinions --- but I agree, I need to do that (once I learn how).
Goodangel, I will need to learn how to use the Triggers -- I'm long overdue in getting up to speed on SQL -- so thanks.
ArmenStein, I just downloaded your presentation and plan to study it now.
This application is a 10-user app -- with maybe 20 forms, 20+ reports, too many queries (as I came into it late and am sure many do the same thing) --- all the users are accessing their own copy of the FE on Terminal Server (which is also a new environment for me - to have all users come in this way) and the SQL Server is on a different server accessible to the Terminal Server.
The application becomes horrendously slow after the 3rd user logs in. I have sped things up by utilizing temp tables in the front-end for many of the combo-box values (since the front-end gets overlaid each time it's opened, so bloat was not a concern) and have minimized the # of records displayed in the one "continuous-form" form - which has been the worst performer. The db only has 20,000 records in it so I didn't think migrating to SQL server was needed at this time, but we decided to go that route to see if we couldn't improve things -- so here I am.
With the Terminal Server environment being unfamiliar to me I was unable to pinpoint exactly where the slow-downs happened. Other users indicated they experienced slow-downs using other software (Outlook, Quickbooks, Excel, etc.) so we brought network guys in to analyze whether or not there was a hardware or configuration issue. They replaced the nic card which mitigated the number of network drops (and their associated Access/Jet corruptions) to a couple a month instead of per day -- which was a move in the right direction -- but their business is growing as is their staff, so this performance issue is of extreme importance, which is why I am working like mad (on my own dime for most of the time - because if it's the way it's designed, it's my fault -- and if it should have been designed for SQL Server in the first place, then that is an error on my part as well ) to fix this.
In my Access_SQL version FE (not yet in production) I have added passthrough queries in many of the combo-boxes - at someone's recommendation -- is that a good way to go in your opinions?
I have found this site immensely helpful to me over the years - and because I work alone consider it/you all my tech-support group. I am trying to keep abreast of all the new technology (am now learning .net) but with my existing client base of 20+ clients - most in maintenance mode - have little time to spend learning new things -- but I must -- unless I win the lotto.
If anyone else has any suggestions, they'll be appreciated. In the mean time I will begin studying what you've sent me so far and attempt to implement the suggested changes. Nothing like experiencing a learning curve under pressure.
Have a great day!
Eileen
acPerkins, I was not as annoyed as I was confused (and tired) -- I guess emotions don't come through too clearly in text, so I'm sorry about that. I'm getting confused because I have found disparate views/opinions on whether or not I should/need to convert to ADO, which I had thought was recommended -- and if you were to pull up my previous questions might see some of those opinions --- but I agree, I need to do that (once I learn how).
Goodangel, I will need to learn how to use the Triggers -- I'm long overdue in getting up to speed on SQL -- so thanks.
ArmenStein, I just downloaded your presentation and plan to study it now.
This application is a 10-user app -- with maybe 20 forms, 20+ reports, too many queries (as I came into it late and am sure many do the same thing) --- all the users are accessing their own copy of the FE on Terminal Server (which is also a new environment for me - to have all users come in this way) and the SQL Server is on a different server accessible to the Terminal Server.
The application becomes horrendously slow after the 3rd user logs in. I have sped things up by utilizing temp tables in the front-end for many of the combo-box values (since the front-end gets overlaid each time it's opened, so bloat was not a concern) and have minimized the # of records displayed in the one "continuous-form" form - which has been the worst performer. The db only has 20,000 records in it so I didn't think migrating to SQL server was needed at this time, but we decided to go that route to see if we couldn't improve things -- so here I am.
With the Terminal Server environment being unfamiliar to me I was unable to pinpoint exactly where the slow-downs happened. Other users indicated they experienced slow-downs using other software (Outlook, Quickbooks, Excel, etc.) so we brought network guys in to analyze whether or not there was a hardware or configuration issue. They replaced the nic card which mitigated the number of network drops (and their associated Access/Jet corruptions) to a couple a month instead of per day -- which was a move in the right direction -- but their business is growing as is their staff, so this performance issue is of extreme importance, which is why I am working like mad (on my own dime for most of the time - because if it's the way it's designed, it's my fault -- and if it should have been designed for SQL Server in the first place, then that is an error on my part as well ) to fix this.
In my Access_SQL version FE (not yet in production) I have added passthrough queries in many of the combo-boxes - at someone's recommendation -- is that a good way to go in your opinions?
I have found this site immensely helpful to me over the years - and because I work alone consider it/you all my tech-support group. I am trying to keep abreast of all the new technology (am now learning .net) but with my existing client base of 20+ clients - most in maintenance mode - have little time to spend learning new things -- but I must -- unless I win the lotto.
If anyone else has any suggestions, they'll be appreciated. In the mean time I will begin studying what you've sent me so far and attempt to implement the suggested changes. Nothing like experiencing a learning curve under pressure.
Have a great day!
Eileen
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
That having been said, there are others here (most notably Jim Dettman) who disagree with this verdict. I would suggest you search this website for precisely this saame scenario and you will see it repeated over and over again. I have yet to see a single instance of someone who has converted over a complex application from MS Access to MS SQL Server and have not had the same problem you are encountering.
Good luck.