Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KnooKie
Aged Yak Warrior
623 Posts |
 Posted - 2002-10-09 : 05:33:28
|
| We have an Access97 database with SQL7 tables linked into it.If we want to refresh the links to these tables using a different DSN we find that sometimes it'll ask for the pswd initially and then happily go down the list of tables refreshing their links. Lovely.However, sometimes it pops up with the login screen after every single table and we have to enter the pswd every single time for each table in order to refresh the links to each table.Any ideas why we have to do this sometimes ?? Have searched the site and not found the answer.We use machine DSNs only.===========Paul |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-09 : 09:35:32
|
| Take a look here:http://www.mvps.org/access/tables/tbl0010.htmIt's a lot of code, but basically it allows you to re-link tables automatically. Plus, you can store the ODBC connection strings in a table and modify them at your leisure. You'll end up with DSN-less connections, which are WONDERFUL and highly recommended. You need to ensure that your ODBC connection strings look like the following:ODBC;DRIVER=SQL Server; SERVER=whpntdb1; UID=general; DATABASE=general; Network=DBMSSOCN; Address=whpntdb1,1433; TABLE=dbo.AgentListThe portions in red are necessary to ensure that this works without a DSN (notice that there is no DSN section in this string) When using a DSN to link tables the Network and Address portions are usually left out. The 1433 in the Address section refers to the port SQL Server listens on.I've used a very similar system to the one described in the article, and the code was much simpler. It didn't use any of the registry stuff or have the constant declarations. You really only need the part that loops through the list of ODBC tables and performs the CreateTableDef's for them. Once you get it set up you can trim it down to do only what you need.Edited by - robvolk on 10/09/2002 13:20:13 |
 |
|
|
JozzaTheWick
Starting Member
16 Posts |
Posted - 2002-10-09 : 13:17:04
|
| Knookie:Access asks for the password login when one or more of the tables is linked to a different DSN. And it won't ask for a dsn just for the tables that are linked to that different DSN, it'll ask for a password for every table that it tries to relink.If you get this problem, cancel the relink operation, and go through th e list of tables, idenitifying which ones are linked to the different DSNs, and do them separately.Summary: If all tables selected for a relink operation are linked through the same DSN, Access asks for a DSN once. If ANY of the tables are linked through a different DSN, Access ask for the DSN for ALL of them.Hope this helps you avoid the situation again!J. |
|
|
|
G37Sam
Starting Member
16 Posts |
Posted - 2011-10-18 : 18:36:27
|
| Sorry for bumping an old thread, but how would I go around storing the password anywhere (whether Registry or a DSN file) to avoid Access asking for a password at all?Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-10-18 : 19:55:35
|
| Just add it to the connection string:ODBC;DRIVER=SQL Server; SERVER=whpntdb1; UID=general; PWD=password; DATABASE=general; Network=DBMSSOCN; Address=whpntdb1,1433; TABLE=dbo.AgentListAs long as you're comfortable with the password being accessible like that. |
|
|
|
G37Sam
Starting Member
16 Posts |
Posted - 2011-10-19 : 05:06:43
|
| Well I tried adding "PWD=xxx" in a new line in the DSN file that Access has created but Access seemed to just ignore it and kept asking for the passwordSam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-10-19 : 07:47:07
|
| The only way to avoid password prompting is to go DSN-less, file or machine DSNs will not store the password due to security concerns. |
|
|
|
G37Sam
Starting Member
16 Posts |
Posted - 2011-10-19 : 08:01:36
|
| Thanks for your prompt response :)Sam |
|
|
|
|
|
|
|
|
Linking SQL tables in Access