Thread: Update Access Linked Tables ODBC to SQL Server 2008

Upgrading your Access application with upgrade from SQL Server 2005 to SQL Server 2008?

Regarding - it works only if the correct ODBC drivers are loaded:
Those drivers are updated 7 times!
Spent the morning chasing them down.
The drivers are being updated so fast for SQL Server 2008, buy the time I search and find them, the post is obsolete

Hope this saves someone some time!
1 Download (install) the Native Client listed below on each client workstation.
2 Destroy old ODBC SQL Native Client connections
3 Create new ODBC SQL Native Cleint 10 connections
4 Force Relink each table using Access Linked Table Manager
5 Validate your data base for linked tables did change
(It is easy to get false "re-link successful)

http://www.microsoft.com/downloads/d...displaylang=en
A little over half way down:
Microsoft SQL Server 2008 Native Client

X86 Package (sqlncli.msi) - 4549 KB
X64 Package (sqlncli.msi) - 7963 KB
IA64 Package (sqlncli.msi) - 11112 KB

Will try this instead of the huge hot fixes listed above for the Workstations.
Microsoft SQL Server 2008 Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.

Problem: Relinks of 283 tables take time.
Does anyone have code to force the update?
I can't locate my old code to do this.

I am also looking in my archive for Windows Code that actually creates the ODBC entry. If anyone has that, please post it too!

Audience(s):
Access Linketd Table Manager
Access ODBC
Excel Linked via Linked Tables

Interesting development to keep an eye out for during migration:
This is something to watch and test for before deploying new SQL 2008 ODBC drivers (mentioned in the above article).

One linked table added about 6 months ago used the ODBC SQL Native Client rather than the SQL client.

Once I added the new ODBC client for SQL Server 2008 to my development workstation, that one linked table would not connect to the existing SQL Server 2005 database.

My archived Access databases going back many months before updating the new ODBC Drivers experienced exatly the same non-connect issue for that one linked table.

All users are fine, since their workstations have yet to receive the SQL 2008 ODBC update (in above post).

The linked table had to be deleted and re-created. It would not re-connect with the Access interface.

The test environment includes a Access run-time application connected to SQL 2008 on a stand-alone user test workstation.
The Development workstation connected to the production SQL 2005 that had the SQL 2008 ODBC driver updated is the only workstation to have this problem. And the only workstation to attempt to run both versions of SQL server.

Over the weekend, the plan is to update all the users with the ODBC 2008 drivers. If they have any legacy applications with linked views to Excel Workbooks using the ODBC SQL Client, it may be necessary to re-link them.

Code to relink tables with migrating to SQL Server 2008:
Hint: remove any spaces after a ;
Spaces are here for presentation purposes
If this is helpful, please rate

Code:

Sub ODBCRelink()
' This takes a version of Access 2003 production linked to SQL 2004 and relinks it to SQL Server 2008  using the new Native Client 10 ODBC Driver
' Create a ODBC link for sqlDATAbase8   This represents a database on SQL ver 8
' code for each linked table - designed for search and repalce as ODBC name changes
10      Dim myDB As Database, tdf As TableDef
15
20       On Error GoTo PROC_ERROR
30       Set myDB = CurrentDb

' First linked table  arcCustomerInvoices is a linked table
' MySQL2008 is the SQL Server 2008 name
' DataBaseName1 is the database name (in my case Access connects to 3 different SQL Server Databaes on the same server)
200     Set tdf = myDB.TableDefs("arcCustomerInvoices")
202     tdf.Connect = "ODBC;DRIVER={SQL Server Native Client 10.0};DSN=sqlDATAbase8   ;SERVER=MySQL2008;DATABASE=DataBaseName1;Trusted_Connection=Yes;APP=Microsoft Office 2003"
204     tdf.RefreshLink

' Second Linked table - links to database 2 on the same server
210     Set tdf = myDB.TableDefs("arcCustomers")
212     tdf.Connect = "ODBC;DRIVER={SQL Server Native Client 10.0};DSN=sqlDATAbase8   ;SERVER=MySQL2008;DATABASE=DataBaseName2;Trusted_Connection=Yes;APP=Microsoft Office 2003"
214     tdf.RefreshLink
' Next 220 tables  linked to multiple databases not shown


PROC_EXIT:
11190        'On Error Resume Next
111100       Exit Sub

PROC_ERROR:
111110       Select Case Err.Number
                  'Case ###
              Case Else
111120               Debug.Print Err.Number & "  " & Err.Description
111130               Resume PROC_EXIT
111140       End Select

End Sub