ODBC connection strings won't update

[DFS]

This code fails to update the connection strings (Access97 - SQL Server 2000
table links). It works in Access2000.

Public Sub updateConnStrings()
For i = 0 To db.TableDefs.Count - 1
Set td = db.TableDefs(i)
td.Connect = "ODBC;DSN=dsnName;UID=DFS;PWD=DFSpw"
td.RefreshLink
Next i
db.TableDefs.Refresh
End Sub

[Tom van Stiphout]

On Mon, 26 Jan 2004 22:41:12 -0500, "DFS" <no****@nospam.com> wrote:

I think you need to add the tablename to the Connect string. Attach
one manually, then check what the Connect string is.

-Tom.

This code fails to update the connection strings (Access97 - SQL Server 2000
table links). It works in Access2000.

Public Sub updateConnStrings()
For i = 0 To db.TableDefs.Count - 1
Set td = db.TableDefs(i)
td.Connect = "ODBC;DSN=dsnName;UID=DFS;PWD=DFSpw"
td.RefreshLink
Next i
db.TableDefs.Refresh
End Sub

[Steve Jorgensen]

I don't recall that I've ever had to include the table name in the connect
string. Personally, I can't see any reason the code below should not work,
though I'm not sure why an index number was used instead of a For Each loop.

On Mon, 26 Jan 2004 22:09:11 -0700, Tom van Stiphout <to*****@no.spam.cox.net>
wrote:

On Mon, 26 Jan 2004 22:41:12 -0500, "DFS" <no****@nospam.com> wrote:

I think you need to add the tablename to the Connect string. Attach
one manually, then check what the Connect string is.

-Tom.

This code fails to update the connection strings (Access97 - SQL Server 2000
table links). It works in Access2000.

Public Sub updateConnStrings()
For i = 0 To db.TableDefs.Count - 1
Set td = db.TableDefs(i)
td.Connect = "ODBC;DSN=dsnName;UID=DFS;PWD=DFSpw"
td.RefreshLink
Next i
db.TableDefs.Refresh
End Sub

[DFS]

"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:ei********************************@4ax.com...

On Mon, 26 Jan 2004 22:41:12 -0500, "DFS" <no****@nospam.com> wrote:

I think you need to add the tablename to the Connect string. Attach
one manually, then check what the Connect string is.
Tom,

Adding the tablename didn't affect it. Turns out my code is working, sort
of.

RefreshLink in Access 97 doesn't update the Connect string to exactly what I
say; it will change the DSN, UID and DATABASE, but it adds (or won't drop)
the WSID, the App=MicrosoftAccess, and the Trusted_Connection settings.

Access 2000 sets the Connect string to whatever you say (as long as it's
valid). So you can have a minimal Connect string in A2000:
ODBC;DSN=ProductionSystem;DATABASE=ProductionDB

Thanks

-Tom.

This code fails to update the connection strings (Access97 - SQL Server 2000table links). It works in Access2000.

Public Sub updateConnStrings()
For i = 0 To db.TableDefs.Count - 1
Set td = db.TableDefs(i)
td.Connect = "ODBC;DSN=dsnName;UID=DFS;PWD=DFSpw"
td.RefreshLink
Next i
db.TableDefs.Refresh
End Sub