Solved

Advice about migrating Access to SQL

Posted on 2013-05-14
5
284 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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