Access 2000 FAILS to relink ODBC tables with correct credentials

[Edwinah63]

i have an access 2000 db in which i dynamically relink the tables from
sqlserver 2000 depending on the user - some users having read only
permissions, others read/write etc.

however access 2000 DOES NOT relink correctly and after looking in
msysobjects in access, have found that it seems to cache the last login
name. needless to say this ruins any permissions i have set on
sqlserver.

an edit of the code is attached below:

step 1. delete attached tables - this works and clears out msysobjects

step 2. get tablenames from sql server as recordset - this works as
well.

step 3. reattach

ODBC_STR = "ODBC;DSN=BMTL;DATABASE=BMTL;UID=" & Usr & ";PWD=;"

Set dbsODBC = OpenDatabase("", False, False, ODBC_STR)

'rs(0) = tablename
Do While Not rs.EOF
Set td = CurrentDb.CreateTableDef(rs(0), dbAttachSavePWD)
td.Connect = dbsODBC.Connect
td.SourceTableName = dbsODBC.TableDefs("dbo." & rs(0)).Name
CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh 'refresh link here hopefully
rs.MoveNext
Loop
'refresh just to be sure
CurrentDb.TableDefs.Refresh

'open msysobjects in access and all are linked on either the current
login or on some previously cached login - which it does is random.

is there some attribute or method i can call that will absolutely
refresh the odbc link on the table???

regards

Edwinah63

 

[TC]

Probably not the cause of your issue, but, you should not use
currentdb() like that. Instead, you should cache it in a variable & use
the variable:

dim db as database
set db -= currentdb()
db.this ... db.that ... db.t'other
set db = nothing

To understand why, google all groups on "currentdb cache kaplan"
(without the quotes).

HTH,
TC

 

[TC]

Also, you might want to check out the refreshlink method.
tabledefs.refresh refreshes the tables collection, which is not the
same thing.

HTH,
TC

 

[david epsom dot com dot au]

You are using DAO methods on Application.CurrentDB, i.e.

Application.dbengine.workspaces(0).databases(0)

dbEngine caches ODBC connections. Since you do not wish
to use cached connections, you can (1) wait ~ 15 minutes
until the cached connections time out, or (2) Change
the timeout interval, or (3) Flush the connections by
closing the application and re-opening, or

(4) use a separate dbEngine object.

set dbe = createobject("dao.dbengine.36")

with a separate database object:

set db = dbe.opendatabase()


or (5) use ADO to create a separate dbengine object

I wouldn't do it that way anyhow: I'd use Windows security
for the read only users, and use 0 instead of dbAttachSavePWD
for those users.

(david)