Conversion from Access Jet to SQL Back-end

Eileen Murphy
Eileen Murphy used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
Unfortunately, if all you did was migrate your data and continue to use the same code base you had against MS Access, then in my experience that is typical of the result your will get.  The only way you are going to get an improvement is if you are prepared to spend the time re-analyzing and re-writing your applications to take advantage of MS SQL Server.  That means using either pass-through queries or even better straight ADO calls to Stored Procedures.  Anything else and you might as well stay on MS Access as you will only be disappointed and/or frustrated with the performance.

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.
Eileen MurphyIndependent Application Developer

Author

Commented:
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.
Eileen MurphyIndependent Application Developer

Author

Commented:
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.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2012

Commented:
>>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.
Software Architect
Commented:
Hi Eileen,

There are a number of things you need to be ware of or consider when you are migrating to SQL Server from Access.

1. Access tends to be a mere data storage "pouch" into which you insert or retrieve data, whereas SQL server comes with many features including a high level of programmability all designed to increase efficiency and power.
2. Access is releatively lightweight, whereas SQL Server is heavy duty, a bit like comparing a pick-up truck and a Semi Truck.

Considering the above, when you do your migration, it is important to make sure that you have taken the differences in the two databases into account. The same way you would need to remember that the turning circle for a Semi truck is different than that of a pick-up truck, you need to include these differences in your application design.

For example, with access, most of the code, including queries will tend to be in your application will have to be in the application, whereas for SQL Server, it is more efficient to put your queries as stored procedures and functions in the database. So you will need to make your application a mere "interface" that calls stored procedures in SQL Server and displays results to the user. All the actual processing can be put in the database. This will automatically move your processing to the server, which I think is the whole point since your server will likely have much more computing power than your clients in any case.

Another interesting example is that SQL Server allows you to write special procedures called TRIGGERS, which respond to certain changes in the data, and perform a specific task afterwards. For example, you can set a trigger that automatically deletes all orders for a customer when that customer record is deleted. These kinds of features are not available in JET hence you need to modify your application to take advantage of them.

As for your "Days Out" function, you can write a function in SQL Server that will produce the correct output, and have it called by a stored procedure that performs the updates you require.

This "migration" may seem to be irritating and laborious, but I can assure you it is a move you will NOT regret if you take a bit of time to know the SQL Server platform, and take advantage of the features it provides.
Hi Eileen,

You've gotten some good advice above.  We've designed and deployed lots of Access/SQL Server apps for our clients, and they perform very well.

To elaborate on your "Days Out" calculation on Current - try running the calculation by itself.  Does it take any appreciable time to run?  Any logic in the Current event needs to be lightning fast because it happens so often.  You can write a VBA function that uses an ADO Command Object to call a SQL Server stored procedure.  This will likely be much faster.

Are you opening updatable forms to large recordsets?  We open bound updatable forms to only one record at a time, then use custom "DVD" button record navigation to move between records.

I've written a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads page: http://www.JStreetTech.com/downloads.

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Hope this helps,
Armen Stein, Access MVP
J Street Technology
Eileen MurphyIndependent Application Developer

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial