Access 2000 FAILS to relink ODBC tables with correct credentials

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
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
|||Also, you might want to check out the refreshlink method.
tabledefs.refresh refreshes the tables collection, which is not the
same thing.
HTH,
TC
|||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)
"Edwinah63" <edwinah@.customercare.com.au> wrote in message
news:1128573589.087302.168780@.g43g2000cwa.googlegr oups.com...
>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
>

Access 2000 FAILS to relink ODBC tables with correct credentials

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
Edwinah63Probably 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|||Also, you might want to check out the refreshlink method.
tabledefs.refresh refreshes the tables collection, which is not the
same thing.
HTH,
TC|||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)
"Edwinah63" <edwinah@.customercare.com.au> wrote in message
news:1128573589.087302.168780@.g43g2000cwa.googlegroups.com...
>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
>

About dynamic URL link

I want the URL link to be generated dynamically by a function written in a
custom assembly. However instead of generating the link each time the link is
clicked. The function only called once when the report is generated.
Therefore that link always return the same value. How can i implement this?
Thank you veru much.You can put per-report initialization in the OnInit() event in the Code
property of the report.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"mcngan" <mcngan@.discussions.microsoft.com> wrote in message
news:16560A52-A252-4796-AAA3-CE8755473631@.microsoft.com...
> I want the URL link to be generated dynamically by a function written in a
> custom assembly. However instead of generating the link each time the link
is
> clicked. The function only called once when the report is generated.
> Therefore that link always return the same value. How can i implement
this?
> Thank you veru much.