[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Advice about migrating Access to SQL

Posted on 2013-05-14
5
Medium Priority
?
287 Views
Last Modified: 2013-05-15
One of my clients has an Access 2003 database running within a Citrix environment, which will be phased out in about a year.  However, one of their other offices wants to use this database for the rest of the year, and there could be up to 40/50 users, concurrently using the database (and potentially accessing the same records).

My initial suggestion was to keep the 2003 front-end and migrate the back end to a SQL2008 back-end.  I know that I need to update the front-end to take advantage of SQL - but just wanted to see what your thoughts were.

If this is to happen, they would probably need to upgrade the Citrix server and I was going to provide a 2003 runtime licence (using the SageKey environment).  However, as it's a pretty basic application, I was also considering moving the front-end to a C# based app.  Again any thoughts would be greatly appreciated.  It is after all an in-trim solution.

Thanks guys.
0
Comment
Question by:Andy Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:Sekar Chinnakannu
ID: 39167480
Here is the DB migration strategies for citrix db, Check these article for clear information
http://support.citrix.com/article/CTX123111
http://support.citrix.com/proddocs/topic/xenapp5fp-w2k3/ps-datastore-migrate-intro.html
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39167777
<<My initial suggestion was to keep the 2003 front-end and migrate the back end to a SQL2008 back-end.  I know that I need to update the front-end to take advantage of SQL - but just wanted to see what your thoughts were.>>

  That would be my suggestion and while it's true that you do need to modify the FE to take full advantage of SQL, there is nothing that says you have to.  You can migrate the data and use it "as is" for the most part.

  Even if not, some of the changes you make need not be all that extensive, like using a view to represent a multi-table join you might do in a query on the Access side.

  Also, take a look at "Best of both worlds" here:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

  which has some really good tips.  And one thing you need to make sure you do is add a time stamp column to all the tables.

  With all that said, JET believe it or not will handle that many users.  It gets a bad rep from poor development practices.  However SQL Server offers features JET doesn't have, such as on-line backups, roll-back/forward capabilities, etc that make it a better choice now for all but the simplest of applications.

<<However, as it's a pretty basic application, I was also considering moving the front-end to a C# based app.  Again any thoughts would be greatly appreciated.  It is after all an in-trim solution.>>

  It would have to be very basic to come out ahead time wise.  Personally I would go the SQL route.  re-writting the FE at this point is not going to gain you all that much.

Jim.
0
 

Author Closing Comment

by:Andy Brown
ID: 39167926
Thanks Jim - that's pretty much what I was hoping to hear.

I am going to provide a runtime licence (via SageKey), for the Citrix box.  Do you think that would cause any issues?  If you're not sure - don't worry, I'll send them an email directly.

Always a pleasure,

Thanks Jim
0
 
LVL 58
ID: 39168181
<<I am going to provide a runtime licence (via SageKey), for the Citrix box.  Do you think that would cause any issues?  >>

 I haven't used Sage myself under Citrix, but I know others have and there hasn't been any major issues.

 Access itself works fine in TS.

 The only thing with Citrix/TS is you need to watch out for references within the application.   Normally you setup a drive mapping that is unique for each user (say drive X, which has their username as part of the path).  That gives everyone their own copy of the FE.  

  The problem with the references is that they will for every FE, point to the C drive.  In 99% of the cases, that's not an issue.   Some DDL's though may have a problem being shared.

  If the reference does not need to be regsitered, then it's not an issue as you can simply place the .DLL in the X drive along with the FE and set the reference to that.   If it does need to be registered however, then it must remain in one place and will be shared by all the FE's.

Jim.
0
 

Author Comment

by:Andy Brown
ID: 39168207
Jim you're a star - thank you so much (yet another beer I owe you).
0

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
How to install and configure Citrix XenApp 6.5 - Part 1. In this video tutorial we have explained step by step installation of Citrix XenApp 6.5 Server on Windows Server 2008 R2 is explained in this video. We have explained the difference between…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question