Advice on Migrating from Access to Another RDMS

Posted on 2012-09-19
Last Modified: 2012-09-20

I've been using Access for the past 3 years.  I am not able to design the forms because I am not a programmer but I really like the query interface. And I understand the logic of the relationships and can design the tables and relationships, and have a good idea of what is needed in order to get the product that I need.

So, in short, I wouldn't be designing the UI but I would be managing all the data and doing the data analysis, creating reports, etc.  And I would be able to work with the programmers to explain what we needed.

My question is: What is the next logical progression to move to if I wanted to migrate our data from Access to another RDMS?

Question by:kannika
    LVL 30

    Assisted Solution

    Using Access at the moment, suggest MSSQL server for data and using access as a User Interface.
    I tried Access as a user interface to mange data in Oracle database.

    In general, you need to study the options:
    Set up your requirement list.
    List properties of a suggested database.
    List properties of current database.
    Match your requirements with what other databases can offer.
    LVL 84

    Accepted Solution

    Agree with hnasr that Microsoft SQL Server is the next logical choice. You can use the Express editions, which are free, to begin your learning process. Here's the 2008 download page:

    Be sure to also install Management Studio, which is the visual environment you can use to manage your SQL Server and databases:

    Moving from Access to other platforms (especially MSSQL Server) is fairly straight forward, and MSFT has provided a Migration Assistant:

    That's the nuts-and-bolts of the process, but there are many other factors to consider. For example, will you leave your queries in Access, or will you move them to SQL Server as Views? How much processing will be moved to Stored Procedures or Triggers? There's no way for us to advise you on that, other than to say that, for the most part, the more you can put on the server, the better off you are.

    Here's some more information about moving to SQL Server: - The Best of Both Worlds
    LVL 1

    Expert Comment

    I have converted many Access 2007 Databases to SQL Server using the  wizard provided by Microsoft and it has worked like a champ. The only issue is the version of Access to SQL Server that you are converting too.

    LVL 84
    I've never been a fan of the upsizing wizards provided directly in Access, and in fact many people have significant issues with them (indexes not converting, queries malformed, etc etc)  - but to each his own, I suppose.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    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…

    754 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

    18 Experts available now in Live!

    Get 1:1 Help Now