Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    49

    Unanswered: upgrade from access 2003 to SQL server

    We keep having our backend tables corrupt. We want to upgrade only the backend tables to SQL server. I don't even know where to start. What version of SQL server? I guess I find out if we have an existing licence in the company? Any help on this?

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    you can create an Access data project which turns access into a front end to SQL server. You should use SQL server 2000 sp3 to hold the database.

    You can upsize the access database using the upsize wizard in Access 2003. This will load the tables and data into SQL server - it will give you a report of problems with data types etc. Once you have sorted out any upsize problems you should be able to run the database.

    However to get the full power of SQL server you will almost certainly need to re-write a lot of queries and VBA into stored procedures, SQL functions etc. A good book is the Access Developers Handbook volume 1 & 2 to get you going.
    Justin

  3. #3
    Join Date
    Jul 2004
    Posts
    49
    Well that's why we were just going to 'start' with upsizing the backend tables and leave the front end alone for now. It's split. We have a huge frontend db and it would take more time than we have now to convert. So just want to know mainly about upsizing the backend which holds only the tables. Also, we tend to need to change or add tables and fields off and on. How hard with this be in sql server?

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    The upsizing wizard will do the main work for you - there will almost certainly be some conversion issues that you will need to work through. The database should work after the conversion, but it won't be optimised to make use of SQL server, but you can deal with that later.

    Change the front end database to an Access data project, create a new access data project, linking it to the new SQl server database and copy all the forms etc across. You can edit the SQL server objects through the Access front end - the tables and queries are similar
    Justin

  5. #5
    Join Date
    Jul 2004
    Posts
    49
    I wasn't aware that I had to change the front end at all? Or is this an 'option'?

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Changing the frond end is an option. I would recommend upgrading to an ADP, as you are then able to call Functions and Stored Procedures naturally in Access. In otherwords, you can set the source of a report to a function, rather than having to dynamically alter the text of a pass-through query in an MDB.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    just by the way .. what is corrupting your datatables?

Posting Permissions

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