Is MS Download needed for Access 2010 to link tables to SQL Server 2008

[Rx_]

Should this package be installed on my Windows 7 development workstation AND on the distribution Windows Server? Has anyone tried this?

The concern is that the Access 2007 Front-End linked tables using ODBC or DNS-Less connection on the Windows 7 Development Workstation will work the same when the Front-End is moved over to the distribution Windows Server.

Access 2010 with a Front-End connected to a Back-end via linked tables.
Used the Microsoft SQL Server Migration Assistant to migrate the Access 2010 Back-End to SQL Server 2008 R2.

Distribution of the Front-End uses Citrix. This means that Access 2010 front-end will be installed on a Windows Server to run. Access 2010 is not installed on each of the client's workstations.

Microsoft® SQL Server® 2008 R2 Feature Pack
http://www.microsoft.com/download/en/details.aspx?id=16978
Contains: Microsoft® SQL Server® 2008 R2 Native Client

 

[pbaldy]

I've never used that package, so I can't help you there. I would advise you to make sure each user on Citrix has their own copy of the FE. When I first started using Citrix, I used my normal version control utility, which placed the FE in a folder on the C drive. In a Citrix environment, this means all users are running that same copy. I had twitchy problems until I changed my program to put the FE in a user-specific subfolder.

 

[Rx_]

Thanks: Been there done that, opened a glass of red wine to celebrate...
Thanks to Bob Larson who provided the script... somewhere on this site.

For the rest of you, a script on the server makes a copy of the Front-End and places it in a Profile folder for each user. Each user has an individual Front-End with linked tables to the back-end. This way, the Front-End is placed on a Windows Server.

Pertaining to my question: The Distribution Windows Server should probably have the same ODBC drivers (version) and the ODBC.
One of my options is to look at writing a script for DNS-Less links for example.

Hopefully Windows 7 and Wondows Server keep the ODBC DLL versions syncronized? Otherwise, something like this might run into trouble:

strConnectionString = "ODBC;Driver={MySQL ODBC 3.51 Driver};" & _
"Server=localhost;Database=" & DataBaseName & ";" & _
"User=" & UID & ";" & _
"Password=" & PWD & ";" & _
"Option=3;"

Update: Found the answer with trial and error.
It is absolutely necessary. Not from the data migration point. The tool will update datetime data types to date time data types in SQL Server.
However, odbc on Windows 7 Access 2010 will read the SQL Server date datatypes as type Text.
Download the Native Client DLL on each client machine and link using it instead of ODBC. It works great. While typing this update, my very complex report that uses VBA to create a very large and detailed Excel report completed.
The only forms that did not work are because of index migrations or some invalid Openrecordset language that is easily corrected.
Don't leave home without this DLL on your thumbdrive.