?
Solved

Advice about migrating Access to SQL

Posted on 2013-05-14
5
Medium Priority
?
285 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

752 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