Linking Access 2000 frontend to a MSSQL2008 database?

[Grunners]

Hi all,

That's it really! Have had to upgrade to SQL2008 on the server and can restore all my backups no problem.

Anyone have any idea how to create an ODBC connection to it (if indeed that's the right way to do it) so I can link the tables back to the 2k frontend? (all mdb's)

+++++++++++++++++++++

Installed Access 2000 on server and can connect to tables (and link) no problem. So do I just need a REALLY updated ODBC driver from my MSSQL2008 dvd?

+++++++++++++++++++++
Another Update:

Downloaded SQL 2008 Native Client to give me the new ODBC driver on client machines. ODBC driver is there and I can now see the Server, but can't connect to the databases...

+++++++++++++++++++++

SOLUTION:

Was to use SQL Server Configuration Manager and enable the TCP/IP protocol for each and every database I wanted to connect to. It's always in the book somewhere!

 

[Rx_]

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

• • 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. Yep! This is what you need to proceed.
  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.
  
  Audience(s): Access 2003, SQL Server 2008, ODBC, Linked Table

 

[Rx_]

Now, the VBA Code that use to work to Re-Link each table for the older ODBC does not seem to work with this driver.

Using the namual Linked Table Manager
The properties show to be DSN=MyODBCMgrName;Database=MyDBName

I have over 250 tables going to over 4 SQL server databases.
While in a quality assurance mode, I need to update this twice a week.
Please post the code if you got it!