Relinking ODBC Tables in Access, Not Saving UID/PWD

[mha...@my-dejanews.com]

Here's one that Microsoft Technical Support couldn't answer for me.

I have an Access Database with linked tables to a SQL server. My problem
occurs when I try to link a new table using the following VBA code.

****************
Set newTable = dbs.CreateTableDef("TableName")
newTable.Connect = "ODBC;DRIVER=SQL Server;SERVER=MyServer;UID=vb;PWD=pass;
WSID=MyComputer;DATABASE=Northwind"
newTable.SourceTableName = "TableNameOnServer"
dbs.TableDefs.Append newTable
****************

This code adds the linked table but it always drops the UID/PWD, so the
linked table is useless unless I give all my end users the UID/PWD (which I
can't do).

Microsoft Tech. Support told me that it is not possible to save the UID/PWD in
a connect string unless I use the graphical interface for linking tables in
Access. This seem to be very limiting to the developer, The user can do it
but the developer can't?

Any solution would be greatly appreciated.
Thanks,
Marc Hache

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Dev Ashish]

Hi Marc,

Look at this link, specifically at the dbAttachSavePwd usage. Last I
checked, this (Access 95) code was still working fine in Oracle environment.

http://home.att.net/~dashish/tables/tbl0010.htm

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

mha...@my-dejanews.com wrote in message <78sf6l$fpt$1...@nnrp1.dejanews.com>...
:Here's one that Microsoft Technical Support couldn't answer for me.

[Martin Connelly]

This may be a SWAG but Micheal Mee in a white paper on DAO and Client Sever
recommends to add the following after the append.

dbs.Close 'to close database but keep the connection
'Jet caches connection internally

You might also refresh the tabledefs after append.

(mha...@my-dejanews.com) writes:
> Here's one that Microsoft Technical Support couldn't answer for me.
>
> I have an Access Database with linked tables to a SQL server. My problem
> occurs when I try to link a new table using the following VBA code.
>
> ****************
> Set newTable = dbs.CreateTableDef("TableName")
> newTable.Connect = "ODBC;DRIVER=SQL Server;SERVER=MyServer;UID=vb;PWD=pass;
> WSID=MyComputer;DATABASE=Northwind"
> newTable.SourceTableName = "TableNameOnServer"
> dbs.TableDefs.Append newTable
> ****************
>
> This code adds the linked table but it always drops the UID/PWD, so the
> linked table is useless unless I give all my end users the UID/PWD (which I
> can't do).
>
> Microsoft Tech. Support told me that it is not possible to save the UID/PWD in
> a connect string unless I use the graphical interface for linking tables in
> Access. This seem to be very limiting to the developer, The user can do it
> but the developer can't?
>
> Any solution would be greatly appreciated.
> Thanks,
> Marc Hache
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--
Marty Connelly ai...@ncf.ca Ottawa, Canada

[Doug Steele]

We had this problem at work recently, and I did some digging through
TechNet.

KB Article Q164003 talks about two possible reasons for this occuring.

The second cause (Network Password Caching Has Been Disabled) does about
having to change a setting in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\Network

It turns out, though, that the same entry exists in another place:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Common\Security

and you need to correct BOTH places!

Creating and running a .REG file containing the following 2 settings
should fix you up:

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\Network]
"DisablePwdCaching"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Common\Security]
"DisablePwdCaching"=dword:00000000

In our case, we had a copy of the front end db on each users' desktop,
and we wanted them to be able to store their own userid and password in
their copy. To do this, we used the following code to re-establish the
ODBC connection to the table in SQL Server:

Dim MyDB As Database
Dim MyTD As TableDef

Set MyDB = CurrentDb
Set MyTD = MyDB.CreateTableDef("AccessTableName")

'Set OnError to delete table def if already exists as follows,
'then resume

MyDB.TableDefs.Delete "AccessTableName"

MyTD.Connect = "odbc;dsn=...;database=..." 'no userid or password
MyTD.Attributes = dbAttachSavePWD
MyTD.SourceTableName = "SQLServerTableName"
MyDB.TableDefs.Append MyTD

'When the Append occurs, the user is prompted for his SQL Server
'userid and passwd.
'The dbAttachSavePWD attribute allows for local id and password
'storing, but only if the two registry entries mentioned above
'are first set to 0.

It turned out that the 2 registry entries get reset to 1 by the system
policies the next time the user logs in at our place.

The other thing to note is that the SQL Server table MSysConf need not
be present.

Hope this helps.

mha...@my-dejanews.com wrote:
>
> Here's one that Microsoft Technical Support couldn't answer for me.
>
> I have an Access Database with linked tables to a SQL server. My problem
> occurs when I try to link a new table using the following VBA code.
>
> ****************
> Set newTable = dbs.CreateTableDef("TableName")
> newTable.Connect = "ODBC;DRIVER=SQL Server;SERVER=MyServer;UID=vb;PWD=pass;
> WSID=MyComputer;DATABASE=Northwind"
> newTable.SourceTableName = "TableNameOnServer"
> dbs.TableDefs.Append newTable
> ****************
>
> This code adds the linked table but it always drops the UID/PWD, so the
> linked table is useless unless I give all my end users the UID/PWD (which I
> can't do).
>
> Microsoft Tech. Support told me that it is not possible to save the UID/PWD in
> a connect string unless I use the graphical interface for linking tables in
> Access. This seem to be very limiting to the developer, The user can do it
> but the developer can't?
>
> Any solution would be greatly appreciated.
> Thanks,
> Marc Hache
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

--

Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://webhome.idirect.com/~djsteele/