From Access Help:
==============
Connect Property
-------------------------
Sets or returns a value that provides information about the source of an
open connection, an open database, a database used in a pass-through query,
or a linked table. For Database objects, new Connection objects, linked
tables, and TableDef objects not yet appended to a collection, this property
setting is read/write. For QueryDef objects and base tables, this property
is read-only.
==============
Read/Write for linked tables. This fits as I have been using the same method
with linked Access tables with no problem for quite some time.

Hi Doug

I think the Help topic is wrong.  I have been using RefreshLink Method (in
A2K2) to change the Connection String / Connect Property on ODBC-linked
Tables without any problem.

I'm sorry Doug that is not the case.

--
Terry Kreft
MVP Microsoft Access

Interesting, but you should not believe all you read at microsoft.com!

DAO, unlike RDO, can connect to several different kinds of things,
not just to ODBC.  DAO assumes that the first field is a type field:
the default blank value is for Jet databases. That is, if your string
does not start ";Database=" or "ODBC" or "TEXT"  or whatever, you will
get an error message something like:
   "driver=mydb" is not a recognised ISAM

(david)

Well I got that connection string by first linking the tables in Access
manually. Then, in the Debug window I typed:

?CurrentDb.TableDefs("error_log").Connect

I used the Connect string returned as a template. I will try your suggestion
though which seems to dovetail nicely with Doug's (see earlier post). Is
this a so-called "DSN-less" connection string?

They are already ODBC-linked tables - I just want to point them to a
different server.

That would appear to be the problem. Unfortunately, no error is returned
(see response below).

Well I've tried to keep that code in the loop.

I'm checking the return codes for API calls and calling an API error
function for those. Otherwise I am using the standard VBA error
handling/reporting. Here is the entire function:

Public Function RefreshLinksODBC(DSNFile As String) As Boolean

   On Error GoTo Error_RefreshLinksODBC

   RefreshLinksODBC = False

   Dim db As Database
   Dim strError As String
   Dim lRet As Long, lSize As Long, lErrCode As Long
   Dim I As Integer, iSize As Integer
   Dim strDesc As String
   Dim strWS As String
   Dim strPWD As String
   Dim strConnect As String
   Dim tdf As TableDef

   Set db = DBEngine(0)(0)

   'Initialize string variables to fixed length for API calls
   gstrSQLServer = String(32, vbNullChar)
   strDesc = String(32, vbNullChar)
   strWS = String(32, vbNullChar)

   lRet = SQLReadFileDSN(DSNFile, "ODBC", "SERVER", gstrSQLServer, 32,
lSize)
   If lRet Then
       If lSize = 0 Then
       'Need to prompt for server name
           DoCmd.openform "frmSQLServer"
           If Len(gstrSQLServer) < 1 Then
               msgbox "Must select a Database server!", vbCritical, "Hey
hammerhead!"
               GoTo Exit_RefreshLinksODBC
           End If
       Else
           gstrSQLServer = Left(gstrSQLServer, lSize)
       End If
   Else
       GoTo ODBC_Error
   End If

   'Save new server name in DSN file
   lRet = SQLWriteFileDSN(DSNFile, "ODBC", "SERVER", gstrSQLServer)
   If lRet = 0 Then GoTo ODBC_Error

   lRet = SQLReadFileDSN(DSNFile, "ODBC", "Description", strDesc, 32,
lSize)
   If lRet Then
       strDesc = Left(strDesc, lSize)
   Else
       GoTo ODBC_Error
   End If

   If InStr(1, strDesc, "Backup") > 0 Then
       strPWD = "yyyyyyyy"
   Else
       strPWD = "xxxxxx"
   End If

   lSize = Len(strWS)

   If GetComputerName(strWS, lSize) Then
       strWS = Left(strWS, lSize)
   Else
       err.Raise GetLastError()
   End If

   strConnect = "ODBC;Description=" & strDesc & ";DRIVER=SQL
Server;SERVER=" & gstrSQLServer & ";UID=sa;PWD=" & strPWD & ";APP=Microsoft
Open Database Connectivity;WSID=" & strWS & ";DATABASE=Prototype"

   For Each tdf In db.TableDefs
       If tdf.Attributes And dbAttachedODBC Then
           tdf.Connect = strConnect
           tdf.RefreshLink
           db.TableDefs.Refresh
       End If
   Next

   'Set global connect string for PassThrough queries
   gstrODBC = strConnect

   RefreshLinksODBC = True

   GoTo Exit_RefreshLinksODBC

ODBC_Error:
   'ODBC error - enumerate
   For I = 1 To 8
       strError = String(255, vbNullChar)
       lRet = SQLInstallerError(I, lErrCode, strError, 255, iSize)
       strError = Left(strError, CLng(iSize))
       If lErrCode > 0 Then
           LogError "RefreshLinksODBC", lErrCode, strError
       End If
   Next

Exit_RefreshLinksODBC:

   Exit Function

Error_RefreshLinksODBC:
   Dim errX As error

   For Each errX In Errors
       LogError "RefreshLinksODBC", errX.Number, errX.Description
   Next

   Resume Exit_RefreshLinksODBC

End Function

I do append, delete, rename:

2680        Set tbldef = dbRemote.CreateTableDef(sLinkName & "_",
nTDFAttribute, sRemote, sConnect)
2700        dbRemote.TableDefs.Append tbldef        'using modified name
2710        dbRemote.TableDefs.Delete sLinkName
2720        tbldef.Name = sLinkName                 'change name to correct
name

(david)

Excellent stuff everyone - thanks so much! I will do some experimentation
and see what works best for me.