1) Is there a way to discern if the linked tables are using a dsn or
dsn-less connection to SQL Server?
It really don't matter
2) What do I need to change in Access (and how) to point it to the 2008
instance?
You can use the linked table manager
Actually, once the connection is setup, then you can often delete the DSN.
In fact, even if you use the linked table manager, you often wind up with a
DSN less connection. (if you link through the file data source as opposed to
a Machine Data Source, then the resulting string will function without the
original file data source).
You can simply check if you using a DSN in two ways:
Simply look at a current linked table connection string (hover your cursor
over the linked table name in the table view tab. You should see the
connection
info).
Even better, just whack ctrl-g and in the debug window you can
printout the connection string for a table by going:
Type in the following:
? currentdb.TableDefs("dbo_Resmain").Connect
In the above the linked table called dbo_Resmain
The result output is:
ODBC;DSN=zooMachineTest;Description=zoo2 test;UID=Albert;APP=Microsoft
Office 2003;WSID=COMPAQNOTE1;DATABASE=zoo2;Trusted_Connection=Yes
Note how there is a direct reference to the DSN in the above. For a dsn-less
connection, you get:
? currentdb.TableDefs("dbo_Resmain").Connect
ODBC;Description=zoo2 test database4;DRIVER=SQL
Server;SERVER=(local);UID=Albert;APP=Microsoft Office
2003;WSID=COMPAQNOTE1;DATABASE=zoo2;Trusted_Connection=Yes
So, in the DSN connection you will NOT see the specified driver being used
(sql server). In the DSN less connection (2nd example) you see the sql
driver being specified. As mentioned, if you used the linked table manager
and use a file dsn you usually get a DSN less connection anyway (you could
in theory delete the file dsn, and the link would continue to function).
On the other hand, it is MUCH MOOT if your current connection is DSN less or
not, since as Mentioned in both cases the wind of using the built in linked
table manager to create that an action in the first place.
So, to link and get a connection working to sql 2008, simply use the
linked table manager and re-link the tables to the new instance of sql
server.
tools->database utilities->linked table manager.
Furthermore if you are using a DSN less connection, then the developer of
the application MUST HAVE provided a re-link system in the code, else how
could the links have been setup? So, dig up instructions on this was
setup or simply take a look at the startup code in the current
application. You see if any code runs that sets up the table links. In other
if this is a DSN less connection then somebody must have set this up along
the way.
You'll also notice that you have a choice of two types of drivers, you have
SQL server, and you have SQL native. The "native" driver is the new one and
is for 2005 and 2008 (it supports new features like compression and
encrypting
etc). For best compatibility raises, you're probably best to use the
standard "sql server" driver in place of "sql native client".
)