Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Moving Access to a SQL 2005 Backend (Access 2003)

    Hi,

    Just out of curiosity, is there a huge amount of work involved in moving an Access database out of Access onto SQL 2005, but keeping the Access front end??

    Is there anything that I should watch out for in particular?

    The front end contains a number of different screens.

    The reason being that at the moment I have an Access application which is supporting around 12-14 users and has become too unstable to ignore anymore.

    Many thanks for your help.

    Regards,
    Lee

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    There is an upsizing wizard in Access, but it doesn't work with SQL Server 2005, only with older versions. So you'd have to import the Access tables into an SQL Server 2005 database manually. You can then re-link the tables in your Access frontend to SQL Server 2005. Forms and reports should still work, in general.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    I have converted two databases from Access to SQL Server. One had bound forms and one had unbound forms. The one with unbound forms was very easy to convert. The unbound form captured data to be edited, or records to be added and then added them using VBA.

    The one with bound forms was a nightmare.

    One big huge difference for me was that Access and SQL Server assign primary keys at different times, and that created some challenges as well. If you are not using linked tables this will not impact you.

    I hope this helps.

    Ken

  4. #4
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    Is there a significant speed advantage to be gained from migrating to SQL server?
    Best Regards,

    Luke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    If you have very large tables - yes.
    If you have many simultaneous users - yes.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    Hi,

    Thanks for the response.

    We have forms bound to the linked tables and the queries in the front end database, the back end has no objects other than the tables. With this in mind, would the forms, bound to linked tables create a nightmare, seeing it is only the back end that needs to be moved??

    Regards,
    Lee

  7. #7
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    Thanks Hans,

    I do have the option of moving to SQL 2000, which would have the advantage of being able to use the upsizing wizard, but the downside of not have the 2005 refinements.

    Regards,
    Lee

  8. #8
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    Hi Lee,
    You might want to check out SQL Server Migration Assistant for Access
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    Francois

  10. #10
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    Ok will do.

    Many thanks.
    Lee

  11. #11
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    Many Thanks for that, I'll see if I can track it down in the bookshop.

    Regards,
    Lee

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    There are substantial speed advantages to SQL Server if you are returning small datasets from large tables, but they aren't automatically accrued by simply converting or upsizing. Never the less, we do use SQL Server as the the backend to nearly all of our client projects. It is much more robust - I've seen one corruption in a SQL Server database in nearly 15 years of working with it, and that was the result of RAID drives being being installed in the wrong sequence after removing them to do some Y2K testing. And I wouldn't be too concerned about using bound forms that are based on either linked tables or queries on linked tables. 95% of what we do uses bound forms - some with several levels of subform nesting. As noted in another response, if you are doing lots of stuff in code and trying to capture the value of an autonumber field, there can be issues since Access gets the autonumber when you start an insert operation, and SQL Server doesn't get the value until the record is actually saved. There are some fairly simple solutions to that issue - if you have it, post back and we can lay them out for you. As to the performance gains, they accrue mostly when you are getting a small dataset, such as you typically do with a single record form. Good luck with the upgrade if you decide to do it, and do get the book Francois suggested.
    Wendell

  13. #13
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    We have an access database front and backend at present. Remote sites connect via Terminal Server to use the Database. Would migrsting the backend enable us to do away with the terminal server, or would the speed be too slow?
    Best Regards,

    Luke

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    Sorry - SQL Server won't help with the speed issue when trying to run remotely. Terminal Services is still your best bet. That or one of the Remote PC services are the only options that really work well with Access - there's just too much data going back and forth between the front-end and the back end. But you should be able to have your users share a single SQL Server backend and get good response. We do this with several of our clients and we simply use their Terminal Services login as their login for SQL Server and it works just fine.
    Wendell

  15. #15
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving Access to a SQL 2005 Backend (Access 2003)

    I have been working for the last year converting all of our organizations databases to a SQL Server 2005 back end Access front end. We have users in connected offices by T1 lines as far away as 300 miles away and the performance degrades slightly at that distance but is well within an acceptable range. We also have users with laptops and air cards that are using a VPN connection that have acceptable preformance. To replatform the back ends we are using the upsize wizard in Access 2002 to convert to SQL Server 2005. Here is a list of a few things we learned. There are books, but some of these things were not in the books I read:

    Memo fields will not upsize. We add the field to the SQL table set the data type to allow for enough characters then use an append query in Access to add the data
    When tables are upsized SQL appends the name of the owner to the table name. This is typically .dbo When we relink the tables we just rename them in the Access database window to exclude the dbo. this allows all the old code to work
    All code that uses the Open Recordset method will have to have the dbSeeChanges argument added to the line of code. We just search all of the code using the search tool for Open Recordset
    A lot of our forms did not work correctly with Access yes/no check boxes. Prior to upsizeing we converted them to text fields with combo boxes on the forms
    The SQL words like SELECT, WHEN etc. are not allowed as field names. Someone in our group was naming fields to track who made changes to forms When. This produced an error in every form in that database.
    Changes made to linked SQL table design do not dynamically update in Access you must relink the tables in the Access front end.
    SQL security can be a new challenge. We are using schemas to manage permissions. This is a new feature in SQL 2005
    Realtionships in SQL 2005 are created and controlled in separate diagrams
    The upsize wizard requires all tables have a primary key
    If you are using Switchboard forms users are not prompted for a SQL login until the first time they try to access data. We have a table called tblStart with one field in it. We put a hidden field on the startup Switchboard to force the login to occur when the database is opened.
    When you install SQL 2005 you do not get the management studio unless you select to install workstation features
    WE have out Server in the downstairs Server room so I have installed the management studio on my workstation PC so I can manage the SQL from my desk. I started by using remote access to the server, but that is not a good solution.

    Carla

Page 1 of 2 12 LastLast

Posting Permissions

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