Solved

Advice about migrating Access to SQL

Posted on 2013-05-14
5
280 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
  • 2
  • 2
5 Comments
 
LVL 24

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 57

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 57
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HasData 9 36
Server Error 11 47
Set focus on next field when character count = 5 9 12
I need to be able to get MAX(date)-1 from table. 4 26
#Citrix #Internet Explorer #Enterprise Mode #IE 11 #IE 8
Several part series to implement Internet Explorer 11 Enterprise Mode
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

911 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now