By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,499 Members | 829 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,499 IT Pros & Developers. It's quick & easy.

ODBC connection strings won't update

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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


Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.