Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2012
    Posts
    8

    Unanswered: Is migrating to outdated version of SE from Access worth it?

    Hello,
    I apologize if this is a repeat of some thread, but I have searched but could not find answers to my SQL Server questions that would be understable to someone who has never worked outside of Access.
    I am currently working in Access 2003 in a Windows XP environment (same for all my users). We're having corruption problems due to network traffic, etc, so I started looking at SQL Server Express as the free alternative to a more robust back end.

    We do not meet the technical requirements for SQL SE 2008 and beyond. It seems very inlikely that the organization would accomodate Windows 7 clients for me and my five users (this is a small scale database, designed for the specific purchasing needs of the department and everything 'big' in our organization is done in Oracle, not SQL Server).

    My question is this: given that our technical specifications and office 2003 limitation would only allow us to work in SQL SE 2005, is it even worth starting down this path? The path of migrating to an edition of SE that is being phased out, I mean. Would this be like being excited about just having bought a corldless landline phone when everyone is walking around with smartphones? Has anyone migrated their back end to an outdated version of SE? If so, have you had success with retaining your front end in Access 2003?

    If my idea about migrating to SQL SE 2005 is not worth the time it would take, could anyone suggest better alternatives that would serve us in the XP environment? I am trying to find the least painful way of making our back end healthier. We just can't stay in Access anymore. It's suicide.

    Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because your question is focused on Microsoft Access instead of on SQL Server, I'm going to move this thread to the Microsoft Access Forum. It ought to get more useful answers there.

    Windows XP is effectively ten years old. Windows Vista, Windows 7, and now Windows 8 have been released since XP. According to Microsoft's XP Lifecycle, Mainstream support ended more than three years ago and true end-of-life for XP is about 18 months from now. Depending on the size of your organization and how much you depend on your computers, it can't be too long before you have a "forced march" to a more current version of Windows!

    Returning to your original problem, there are multiple ways to solve or at least mitigate the problem.

    The easiest answer would be to change your MS-Access app to use a frontend/backend model, so that only the machine hosting your MDB file would actually read from and write to that file. This will only mitigate your problem, but it is relatively quick and inexpensive compared to fixing the underlying issues. It also doesn't require you to do anything with the OS, which is a real plus!

    Another inexpensive fix would be to migrate to an Open Source database such as PostGreSQL or Maria Db. Both of these databases are Open Source (which implies that they cost nothing), and can be hosted on an XP machine. This could be combined with the frontend/backend idea above to drastically simplify the transition to a new platform. This is significantly more work than just separating the backend, but it also offers a lot more benefits.

    At the far end of the scale, you could migrate your existing MS-Access database to Oracle. You would then need to decide if you wanted to write a new front end using Microsoft Access, or if you wanted to switch to an entirely different front end (I have lots of opinions here, but won't go into them unless you ask). This is MUCH more work and many orders of magnitude more expensive than either of the other soluitons, but it offers the most opportunity for improvement and for support from your IT group.

    Feel free to ask quesitons, I expect that you'll have some!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    Because your question is focused on Microsoft Access instead of on SQL Server, I'm going to move this thread to the Microsoft Access Forum. It ought to get more useful answers there.

    Windows XP is effectively ten years old. Windows Vista, Windows 7, and now Windows 8 have been released since XP. According to Microsoft's XP Lifecycle, Mainstream support ended more than three years ago and true end-of-life for XP is about 18 months from now. Depending on the size of your organization and how much you depend on your computers, it can't be too long before you have a "forced march" to a more current version of Windows!

    Returning to your original problem, there are multiple ways to solve or at least mitigate the problem.

    The easiest answer would be to change your MS-Access app to use a frontend/backend model, so that only the machine hosting your MDB file would actually read from and write to that file. This will only mitigate your problem, but it is relatively quick and inexpensive compared to fixing the underlying issues. It also doesn't require you to do anything with the OS, which is a real plus!

    Another inexpensive fix would be to migrate to an Open Source database such as PostGreSQL or Maria Db. Both of these databases are Open Source (which implies that they cost nothing), and can be hosted on an XP machine. This could be combined with the frontend/backend idea above to drastically simplify the transition to a new platform. This is significantly more work than just separating the backend, but it also offers a lot more benefits.

    At the far end of the scale, you could migrate your existing MS-Access database to Oracle. You would then need to decide if you wanted to write a new front end using Microsoft Access, or if you wanted to switch to an entirely different front end (I have lots of opinions here, but won't go into them unless you ask). This is MUCH more work and many orders of magnitude more expensive than either of the other soluitons, but it offers the most opportunity for improvement and for support from your IT group.

    Feel free to ask quesitons, I expect that you'll have some!

    -PatP

    Pat, thank you for the quick and detailed response!
    What we currently have is the split front end/ back end set-up. I have tried to set this database up 'textbook' style, so that we get as much out of Access functionality as possible. However, we still find ourselves in a situation where we defintiely need to break out of Access.

    The reason I have looked at SQL Server as the most likely alternative is because there is so much online support for this commonly used system. Also, I have read that the dialect of SQL Server is closer to Jet SQL than any other SQL dialect, and I am really only 'fluent' in Jet SQL. So in searching for Access alternatives, I have shied away from database system names that I have not encountered in a blog at least four times (I am embarrassed to admit- I have never heard of the two that you mentioned in your reply).

    As for Oracle, that actually seems like the most logical solution to me because one- the organization already does a lot of Oracle development, and two- Oracle Developer 2000 Suite is already available to us and we don't have to worry about installing any new products of service packs. I'm not sure my boss would go for that because that would really make this into a much larger scale project, and the goal is to keep it small within the department and not seek the help of the 'big' Oracle developers in the IT department. Beisdes, we want to keep the developer and administrator role in one person (currently myself) because any new developer coming onboard would not have knowledge of the in's and out's of our expenses and purchasing data.

    So, my follow up questions:
    supposing that we don't go with Oracle, and we can't count on Windows 7 for all our users in the immediate future (though one can hope!), what is the best thing to do- go with an outdated version of SQL Server, or try to get over my fear of non-Microsoft sql dialects and look into one of the other open source database management systems that you mentioned?
    Which of these systems would you say has the easiest interface for the administrator and developer? That is, I want something as close to the easy Access graphical interface as possible so I can quickly construct forms and reports.

    Thank you!

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Just to be clear, is there a copy of the Front End on each user's PC? I only ask because I've seen many, many posts where the developer split the database, but placed the Front End on a shared drive, and this, too, can cause problems, including corruption!

    Using Oracle as the Back End seems the best approach, to me, and really shouldn't require using the IT boys, I wouldn't think! You'd simply be using Oracle to hold the data.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    IMHO you can use SQL SE 2005 without any problem.

    From the moment you use the database engine as a data container only with linked tables in the front-end, you won't encounter any hard problem. On the contrary, trying to use an Oracle server as a back-end can be more difficult, mainly because you won't have any control over your database on this server: you'll have to rely on the DBA(s) for everything.

    A MS SQL Server is easy to set up and administer, provided that you use it as a data storage only. You just need to define some security options and a maintenance plan comprising a backup step. Moreover no additional program is needed to control and work with it (SQL Server Management Studio is included into the package).

    Do not worry too much about obsolescence: SQL Server 2005 is still widely used, and you do not need any complex functionalities present in the latest version as you won't use them.
    Have a nice day!

  6. #6
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by Missinglinq View Post
    Just to be clear, is there a copy of the Front End on each user's PC? I only ask because I've seen many, many posts where the developer split the database, but placed the Front End on a shared drive, and this, too, can cause problems, including corruption!

    Using Oracle as the Back End seems the best approach, to me, and really shouldn't require using the IT boys, I wouldn't think! You'd simply be using Oracle to hold the data.

    Linq ;0)>
    Hello, I guess my biggest hang-up about Oracle is, to put it in layman's terms- I just want to be able to go into any table and delete any number of records if I need to, or change the field names simply by going into design view, and pretty much have full control over the tables. With Oracle, even if, as you say, I keep the front end in Access- I am afraid all of that will be super complicated.
    The only time I worked with Oracle tables in an Access environment was when one of the Oracle admin people set me up with several views. That's the extent of my knowledge of how Access plays with Oracle. If I have an ODBC connection to an Oracle table- not a view- through Access, would I still be able to go into design view and mess with the table design? Would I have the same control as I do over my Access linked back end tables? (These are embarrassing, complete-noob questions, I know, but please bear with me- I am finally able to communicate with someone on my level!)
    Thank you!

  7. #7
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by Sinndho View Post
    IMHO you can use SQL SE 2005 without any problem.

    From the moment you use the database engine as a data container only with linked tables in the front-end, you won't encounter any hard problem. On the contrary, trying to use an Oracle server as a back-end can be more difficult, mainly because you won't have any control over your database on this server: you'll have to rely on the DBA(s) for everything.

    A MS SQL Server is easy to set up and administer, provided that you use it as a data storage only. You just need to define some security options and a maintenance plan comprising a backup step. Moreover no additional program is needed to control and work with it (SQL Server Management Studio is included into the package).

    Do not worry too much about obsolescence: SQL Server 2005 is still widely used, and you do not need any complex functionalities present in the latest version as you won't use them.
    Thank you for weighing in on this question. It's really great to have a lot of perspectives. My question about going with SQL Server 2005 is this: how much changed between 2005 and 2010? Our database will be in use by the department for at least the next 5 years. If in that time, we decide to upgrade to a later version, how much work would the upgrade entail? Would I have to re-write code, etc?

    Thank you!

  8. #8
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by MargaritaG View Post
    I just want to be able to go into any table and delete any number of records if I need to, or change the field names simply by going into design view, and pretty much have full control over the tables.
    This is a horribly bad idea! Why would you want to do this? You have a front end application that is built off these tables, they should not be changed.

    If you are going to have an Access front end with an ODBC connection to an Oracle backend, you really should be using Oracle Views rather than tables. This way, if the table does need to be changed you can do it without impacting the application.

    Steve

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Several points need to be made.

    SQL 2012 will allow you to upgrade a SQL 2005 database, as will all of the SQL versions between them. Upgrading isn't difficult, you back up the original database and restore it onto the destination server. If you try to span more generations of SQL, you may have to be a bit more creative in the upgrade process but it still isn't hard.

    The difference between simply using tables created for you as a data store and having the ability to create/delete/rename/etc. those tables is significant. In Oracle, it is frequently dangerous to have untrained users make those changes. Oracle requires highly trained, highly technical administrators to keep the database safe and secure... From a layman's perspective, an Oracle database is high performance but VERY fragile. SQL Server SE is far simpler to setup, configure, and use. You could easily create and manage your schema (tables, etc) for yourself.

    You can use MS-Access with all of the database engines that we've discussed so far... Access will happily cope with almost any ODBC compliant database, and that includes nearly every database in widespread use today.

    The only stupid questions are the ones that you don't ask. Even if you think that the question is silly, we're glad to answer and odds are good that either we hear the question often or that it will do someone good later because they'll find it and thank heaven that the answer is there for them!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by sps View Post
    This is a horribly bad idea! Why would you want to do this? You have a front end application that is built off these tables, they should not be changed.

    If you are going to have an Access front end with an ODBC connection to an Oracle backend, you really should be using Oracle Views rather than tables. This way, if the table does need to be changed you can do it without impacting the application.

    Haha! Well, yes, I realized later how bad that must have sounded: 'Well, I just want to mess with the tables any time I feel like it, just to throw all the users off...' But that's not what I meant. I didn't mean I need the ability to destroy a table any time I am in an aggressice mood. I guess, I am used to thinking of the database as constantly in developement and I envision scenarios in which, even after the database has been in use for some time, it would need some minor design changes such as adding a field or changing a primary key. And it is my concern that if the database is in the hands of the bad and evil Oracle programmers upstairs, I could never, ever get my hands on the tables. That's all... But I am aware of the advantage of views. Way more peace of mind that way.

    Thank you!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by sps View Post
    This is a horribly bad idea! Why would you want to do this? You have a front end application that is built off these tables, they should not be changed.

    If you are going to have an Access front end with an ODBC connection to an Oracle backend, you really should be using Oracle Views rather than tables. This way, if the table does need to be changed you can do it without impacting the application.
    For someone afflicted with the idea that "IT owns the data, not the business" this is a common thought. It is what lead to the "Glass palace" problem that crippled American Business in the 1980s, and lead to the "dot com" riot of the 1990s.

    To make it clear, the business owns the data. The business makes the money, and they pay for the existance of the IT department.

    IT has stewardship issues, and sometimes that means that they have to serve as a buffer between corporate management and the business people with "boots on the ground" that keep the company growing. IT can help the business is many ways, but telling them that the way that they work is a "Horribly bad idea" without offering a better answer really doesn't help the business at all...

    I'm not arguing that there are better ways to perform many tasks. MargaritaG is working as one person in a RAD/SCRUM environment which means that the schema is evolving rapidly. This is a very normal state for someone working on the fringe between the business and IT, and this state is where everyone I've seen gets their start programming. The criteria for this kind of work is "does it satisfy the immediate need", and I haven't seen anything to indicate that is a problem.

    You are correct that there are better ways. You are certainly correct that Oracle can't cope with this kind of development well. That doesn't make it "Horribly wrong" in any sense that I recognize. MargaritaG is learning, and has just found a place where she can get some information. Feel free to add whatever you can.

    Feel free to offer suggestions, or to provide a bit of code to demonstrate a better way to do things. Do NOT tell a new DBForums user (or anyone else here) that their idea is "Horribly wrong" without material backup (mutlipe web links from recognized authorities describing why the idea is bad). That behavior is unacceptable at DBForums.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    Several points need to be made.

    SQL 2012 will allow you to upgrade a SQL 2005 database, as will all of the SQL versions between them. Upgrading isn't difficult, you back up the original database and restore it onto the destination server. If you try to span more generations of SQL, you may have to be a bit more creative in the upgrade process but it still isn't hard.

    The difference between simply using tables created for you as a data store and having the ability to create/delete/rename/etc. those tables is significant. In Oracle, it is frequently dangerous to have untrained users make those changes. Oracle requires highly trained, highly technical administrators to keep the database safe and secure... From a layman's perspective, an Oracle database is high performance but VERY fragile. SQL Server SE is far simpler to setup, configure, and use. You could easily create and manage your schema (tables, etc) for yourself.

    You can use MS-Access with all of the database engines that we've discussed so far... Access will happily cope with almost any ODBC compliant database, and that includes nearly every database in widespread use today.

    The only stupid questions are the ones that you don't ask. Even if you think that the question is silly, we're glad to answer and odds are good that either we hear the question often or that it will do someone good later because they'll find it and thank heaven that the answer is there for them!

    -PatP
    I am convinced by all of your arguments in favor of SQL Server. It seems that it would provide the best of both worlds- the robustness of a true data server, as well as ease and flexibility for the administrator and front end developer. I am also very encouraged by what you say about the upgrade process to more recent versions.

    So the plan for my project is to start the process of migrating to SQL Server 2005 and look towards upgrading to later version once the organization goes Windows 7.
    Thank you all for your suggestions. I will be back for some more of your database wisdom very soon, I am sure!

  13. #13
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by Missinglinq View Post
    Just to be clear, is there a copy of the Front End on each user's PC? I only ask because I've seen many, many posts where the developer split the database, but placed the Front End on a shared drive, and this, too, can cause problems, including corruption!
    Linq ;0)>
    Oh yes- I forgot to answer your question. Yes- we definitely have as many copies as there are users. I've customized some of the forms differently for different users.
    Thanks.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm glad that you found an answer that suits your needs!

    Keep in mind that the frontend/backend design is going to become even more important as you move toward n-tier computing (workstation, processing server, database server). If you design your front end so that it is pure MS-Access (ignorant of the SQL Server storage medium), and put all of the SQL Server specific code in your back-end, it will save you a lot of trouble.

    Another point to consider is that using a database engine will solve problems with storage instability (losing access to your storage file). It will give you the ability to enforce integrity at the storage level (you'll probably LOVE constraints as you discover them). Even so, it can't fix everything!

    If you have connectivity issues (network drops), you'll need to find ways to mitigate those... The database engine has nothing to do with connectivity issues.

    If you have version-itis where you have a front-end MDB and a back-end MDB that are "out of sync" with each other, that usually gets worse instead of better!

    Come by often. We've experienced many of your problems, solved many of those, and can help you understand and work-around the problems that don't have clear answers!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  15. #15
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by Pat Phelan View Post
    For someone afflicted with the idea that "IT owns the data, not the business" this is a common thought. It is what lead to the "Glass palace" problem that crippled American Business in the 1980s, and lead to the "dot com" riot of the 1990s.

    To make it clear, the business owns the data. The business makes the money, and they pay for the existance of the IT department.

    IT has stewardship issues, and sometimes that means that they have to serve as a buffer between corporate management and the business people with "boots on the ground" that keep the company growing. IT can help the business is many ways, but telling them that the way that they work is a "Horribly bad idea" without offering a better answer really doesn't help the business at all...

    I'm not arguing that there are better ways to perform many tasks. MargaritaG is working as one person in a RAD/SCRUM environment which means that the schema is evolving rapidly. This is a very normal state for someone working on the fringe between the business and IT, and this state is where everyone I've seen gets their start programming. The criteria for this kind of work is "does it satisfy the immediate need", and I haven't seen anything to indicate that is a problem.

    You are correct that there are better ways. You are certainly correct that Oracle can't cope with this kind of development well. That doesn't make it "Horribly wrong" in any sense that I recognize. MargaritaG is learning, and has just found a place where she can get some information. Feel free to add whatever you can.

    Feel free to offer suggestions, or to provide a bit of code to demonstrate a better way to do things. Do NOT tell a new DBForums user (or anyone else here) that their idea is "Horribly wrong" without material backup (mutlipe web links from recognized authorities describing why the idea is bad). That behavior is unacceptable at DBForums.

    -PatP

    Thank you, Pat. I really appreciate this post, especially since this is my first thread in this forum. But I was in no way offended by sps's post. I understand that 'I need to go in and delete stuff as I like' is not the kind of database administrator behavior that this forum should be encouraging. I was just unlcear in my original post. I need to have that flexibility and control over the tables while the database is in development. And it seems that when you work on these small-scale 'fringe' projects, the development stage is never over! When the immediate needs of the users change, the design often has to adapt too!
    Thank you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •