Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    We are currently using Access 2007 as a front end with a SQL 2005 database as a back end, both on the same server. The database is part of an application that is about to be upgraded and moved to a different server. The contents of the database will then be migrated from the SQL 2005 database on the old server to a SQL 2008 database on the new server, but we want to continue using Access 2007 (on the old server) as a front end with the new SQL 2008 database (on the new server) as a back end. How can we set up a new ODBC link between the servers to do this?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I presume your front-end is an MDB rather than an ADP, so that you are already using an ODBC link which points to your existing server, and you are using terminal services to execute Access on the server.. If that is the case, you will want to install the SQL Native Client 10 on the old server, and then create an ODBC connection pointing to the new server, and using the native client driver. If you make the name of the data source the same as your old data source, you won't need to relink tables.

    If your configuration is different than I assumed, post back with the details.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. Yes, the front end is an MDB, and we are using an ODBC link between Access 2007 and SQL server 2005 on the old server, which users log on to using RDP.

    The current link uses the "SQL Server" driver, and the ODBC Date Source Administrator also lists a "SQL Native Client" driver (v. 2005.90.3042.00), but neither seems to have provision for linking to a database on a different server, and it sounds like Native Client 10 is a later version that provides this?

    Currently, users in a particular user group are automatically connected to the SQL 2005 database when they run Access 2007. Can we continue this with the setup that you suggest; i.e., users will continue to log on to the old server using RDP and to run Access 2007 on the old server, and then transparently will be connected to the SQL 2008 database on the new server?

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    An ODBC link allows you to change the server name in the connection properties. Have you used the Control Panel > Administrative Tools > Data Source (ODBC) tool to modify the properties?

    cheers, Paul

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. I may be confusing "server" (hardware) and "SQL Server" (software). Are you referring to the option: "Which SQL Server do you want to connect to" in the Control Panel > Administrative Tools > Data Source (ODBC) tool? Currently, this lists only one option; i.e., the old server (hardware), which has a SQL Server 2005 database on it. When the new server (hardware) with a new SQL Server 2008 database on it is added to the network, will the new database on the new server automatically appear in the list for the "Which SQL Server ..." option in the ODBC link on the old server?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Once the new server is up and running on your network, it should appear on the list of available servers in the Data Sources (ODBC) applet, although I have seen cases where you have to actually type the name of the new server. And you should definitely install the Native 10 client software on the old server - we've seen occasional issues with both SQL Server 2005 and 2008 where the older MDAC SQL Server driver was used. The Native 10 client is available on the Microsoft download site, and takes just minutes to install. Once you've done that, the driver will appear on the list of drivers when you add a new ODBC data source.
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for yor reply, although I am still confused about the relation between each server (hardware) and the SQL Server (software) running on it, and which of these the "SQL Server" option in the ODBC link refers to.

    Currently, the Access 2007 front end and the SQL 2005 back end are on the same server (hardware), and users are automatically and transparently connected to the SQL back end when they run the Access front end. Can the connection continue to work like this when the back end is moved to a different server (hardware)?

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The SQL Server that you select when you configure and ODBC connection will point to the appropriate server and establish a connection to the database you specify on the server you select. I should imagine that you will want to shut down the SQL Server 2005 instance running on the old server. Are you going to be administering the SQL Server 2008 instance, or do you have a tech support group that will do that?

    And yes, as long as the new server is configured to be able to talk on the network (using either TCP/IP or named pipes), then the process will be just as transparent as it has been connecting to the local 2005 instance on the same server.
    Wendell

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. It seems that the migration should be fairly straight-forward then. I am doing some preparation now but additional support will be available later.

Posting Permissions

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