SQL Server 2008 MDAC ODBC Native Client 10 (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 11:45
Joined
Oct 22, 2009
Messages
2,803
http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
A little over half way down:
For anyone upgrading to SQL Server 2008 dB -
install this on every workstation - then update the ODBC connection to the new
SQL Serever 2008 Native Client 10


Microsoft SQL Server 2008 Native Client 10
  • 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.
  • This update will change the way your ODBC interface appears. In my case, it did keep all my old values. This is the same interface that the workstation that had SQL Server 2008 client installed updated to.
    Audience(s): Access 2003, SQL Server 2008, ODBC, Linked Table
 

Rx_

Nothing In Moderation
Local time
Today, 11:45
Joined
Oct 22, 2009
Messages
2,803
With tne new odbc interface, it appears that new VBA code to re-link tables (to a new SQL Server 2008 idatabase for example) is needed.

Please use this site for any code that utilizes SQL Server 2008 using the Native Client 10
 

Rx_

Nothing In Moderation
Local time
Today, 11:45
Joined
Oct 22, 2009
Messages
2,803
Apologies!!! I updated my old post - instead of my new post!
This code was very difficult to find. Looks so easy now.
Nobody respond after 4 hours, so I am posting my own answer.
This works if an Access 2010 Linked table has an exact Table that was migrated to SQL Server 2008.
The Access Table Object icon stays an Access icon until several minutes later. Then it turns into the SQL Server Globe (green) icon.

Does anyone have an idea how to get the icons to refresh quicker?

Hovering over the icon, the tool tips show "Table=DBO.MineralOwner;TABLE=dbo.MineralOwner" as part of the connection string.


Code:
Sub RefreshDNSLess2() ' Got this to work
Dim ConnectString As String
Dim strConnectionString As String
Dim scn As String
Dim sLocalName As String
Dim UID As String
Dim PWD As String
UID = "MyID"
PWD = "MyPW"
sLocalName = "Const_Seed"     ' <<<---pick linked table with Trusted ID
Dim DataBaseName As String
DataBaseName = "RegulatoryDB"
strConnectionString = "ODBC;DRIVER=SQL Server Native Client 10.0;" & _
    "SERVER=DenReg-Test;DATABASE=" & DataBaseName & ";" & _
    "UID=" & UID & ";" & _
    "Password=" & PWD & ";" & _
    "Table=DBO." & sLocalName ' & ";Option=3;"
 
    Debug.Print strConnectionString
With CurrentDb
    .TableDefs(sLocalName).Connect = strConnectionString 'scn
        ' ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=DenReg-Test;DATABASE=RegulatoryDB;UID=MyID;Password=MyPW;Table=DBO.MineralOwner
    .TableDefs(sLocalName).RefreshLink
        ' hover over and Link Table Manager - all show this as trusted
End With
End Sub
 
Last edited:

Rx_

Nothing In Moderation
Local time
Today, 11:45
Joined
Oct 22, 2009
Messages
2,803
A patch may have been released for XP ODBC drivers. Access 2003 using XP can probably just link to SQL Server 2008 with no updates in the ODBC driver.

Followup: Microsoft may have updated Access 2003 & XP for forward compatibility
After testing the ODBC connectivity to SQL Server 2008 and fiinding the new ODBC version was required ((2010 Jan, Feb time frame) as March arrived, my connectivity appeared to work using the existing ODBC drivers.

There was a Microsoft patch / update for XP during that time.
Something allowed me to just repoint the existing ODBC connections to the new SQL Server 2008 with no modification.
I had about 25% of the workstation with the new ODBC drivers. The system ODBC interface has a newer appearance than the old ODBC interface.

Access is linked to very large SQL Server tables. Since some of the workstations were the exact same Dell models, I ran side-by-side test. Most of my forms display a timer of how long the process takes. I do not beleive there was any significant difference between the two ODBC drivers for SQL Server 2008. At least not worth the time to install the upgrade.
The speed of SQL Server 2008 was noticeable. thumgs up!

A few weeks later.... some new Dell dual core computers showed up (with XP installed for Office 2003) to replace a few of the 5 year old single core computers. The speed for Access 2003 run-time lined to SQL Server 2008 was very significant.
A process that took two to four seconds before is now just a fraction of a second.
 

FoFa

Registered User.
Local time
Today, 12:45
Joined
Jan 29, 2003
Messages
3,672
We have had some issues using the Native SQL Client (10), and have reverted some of those back to ODBC, even if they are using SQL 2008.
mostly if you are trying to do fancier stuff, like pass parameters to a SP (and they are not hard coded), stuff like that, mostly using Net Framework stuff.

Just F.Y.I.
 

Users who are viewing this thread

Top Bottom