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

Forcing Linked Tables to recheck System DSN

P: n/a
Hello, I am working on an Access Application that uses Linked Tables to
a SQL Server Backend. The problem I have is that there will be 2 types
of users, one which will use a trusted connection and one that will use
SQL Server authentication. I already have a seperate installation
package that will create the proper System DSN depending on which type
of user I have. The issue is that Access appears to cache the
connection string for the linked tables based on the first time they
are linked. I am looking to find a way to force access (via vba code)
to recheck the system dsn on startup.

Using code like this:

'For intLoop = 0 To dbCurrent.TableDefs.Count - 1
Set tdfTable = dbCurrent.TableDefs(intLoop)
If tdfTable.Connect Like "ODBC*" Then
Debug.Print tdfTable.Name
tdfTable.RefreshLink
End If
Next intLoop'

Does not appear to work as it still just grabs the old cached
connection string.

Any ideas would be greatly appreciated.

Thanks,

Andy

Apr 13 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
you could use a DSN-less connection. I think there's an example on
either Tony Toews's site or Doug Steele's...
http://www.accessmvp.com/djsteele/DSNLessLinks.html

(Sorry, Tony's site points to Doug's... and his link is wrong.)

Apr 13 '06 #2

P: n/a
Hi,

You can use my sample, using DSN less connection :
http://www.logicielappui.com/tips/Ac...ginMDB_SQL.zip
Robert Simard
Logipro
http://www.logicielappui.com/tips
"Andy" <an****@andrewfairbanks.com> a écrit dans le message de news:
11*********************@v46g2000cwv.googlegroups.c om...
Hello, I am working on an Access Application that uses Linked Tables to
a SQL Server Backend. The problem I have is that there will be 2 types
of users, one which will use a trusted connection and one that will use
SQL Server authentication. I already have a seperate installation
package that will create the proper System DSN depending on which type
of user I have. The issue is that Access appears to cache the
connection string for the linked tables based on the first time they
are linked. I am looking to find a way to force access (via vba code)
to recheck the system dsn on startup.

Using code like this:

'For intLoop = 0 To dbCurrent.TableDefs.Count - 1
Set tdfTable = dbCurrent.TableDefs(intLoop)
If tdfTable.Connect Like "ODBC*" Then
Debug.Print tdfTable.Name
tdfTable.RefreshLink
End If
Next intLoop'

Does not appear to work as it still just grabs the old cached
connection string.

Any ideas would be greatly appreciated.

Thanks,

Andy

Apr 13 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.