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
Solved

Advice about migrating Access to SQL

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If your vDisk VHD file gets deleted from the image store accidentally or on purpose, you won't be able to remove the vDisk from the PVS console. There is a known workaround that is solid.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

829 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