Access 2000 FAILS to relink ODBC tables with correct credentials - PART2

[Edwinah63]

in a previous post i explained that i was having trouble linking ODBC
tables via code, and that the connection link was being cached.

i received the following response [edit]:

***
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) Change the timeout interval, or

(2) use a separate dbEngine object.


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

with a separate database object:

set db = dbe.opendatabase()

******

i have googled everywhere and i cannot find where to:

1) change the timeout interval of the ODBC connection
2) any code that demonstrates how to use dbengine.36 to link tables
with the correct credentials.

if anyone can explain what to do i would appreciate it immensely!!

regards

Edwinah63

link to original post

http://groups.google.com.au/group/m...dwinah63+access&rnum=1&hl=en#57aa5c57a9ce1fb0

 

[david epsom dot com dot au]

2) any code that demonstrates how to use

dbengine.36 to link tables with the correct
credentials.

Dim dbe 'As DAO.DBEngine
Dim ws 'As DAO.Workspace
Dim db 'As DAO.Database

Set dbe = CreateObject("dao.dbengine.36")
dbe.DefaultUser = "xxxx"
dbe.DefaultPassword = "xxxx"
dbe.SystemDB = "c:\myworkgroup.mdw"
Set ws = dbe.CreateWorkspace

Set db = ws.OpenDatabase("c:\mydatabase")
' put your relink code here
db.Close
ws.Close
Set dbe = Nothing

(david)

in a previous post i explained that i was having trouble linking ODBC
tables via code, and that the connection link was being cached.

i received the following response [edit]:

***
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) Change the timeout interval, or

(2) use a separate dbEngine object.


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

with a separate database object:

set db = dbe.opendatabase()

******

i have googled everywhere and i cannot find where to:

1) change the timeout interval of the ODBC connection
2) any code that demonstrates how to use dbengine.36 to link tables
with the correct credentials.

if anyone can explain what to do i would appreciate it immensely!!

regards

Edwinah63

link to original post

http://groups.google.com.au/group/m...dwinah63+access&rnum=1&hl=en#57aa5c57a9ce1fb0

 

[Edwinah63]

Hi David,

thank you for replying to this and my other post. my question is can i
use this method to link tables to the current database with the correct
ODBC credentials? if so, code please.

my problem is that all the forms are bound against tables linked to the
database. i need to be able to relink these at run time with the
correct details.

you wrote before that Access caches the ODBC connections for approx 15
mins, but the links could be refreshed by opening and closing the
database which is not a useable option.

how can i clear the cache or change the timeout settings?

reagrds

Edwinah63

 

[Edwinah63]

Hi David,

thank you for your reply to this and my other post. i am sorry to tax
your good nature any further but i am not an access programmer and have
tried to apply your code without success [ODBC string still incorrect]
to my existing code. could you please expand the snippet here:

Set db = ws.OpenDatabase("c:\mydatabase")
' put your relink code here

regards

Edwinah63

 

[Guest]

Assuming that you have an Access 2000 database which
includes links to tables, you use your Relinking Code to
relink that database. Do you have Relinking Code?

If not, you should get Relinking Code from
http://www.mvps.org/access/tables/tbl0012.htm
http://www.mvps.org/access/tables/tbl0010.htm
http://www.mvps.org/access/tables/tbl0009.htm

or search the web for simpler examples.

When you have some relinking code, look for the place
that says:
set db = currentdb

replace that with:

Set dbe = CreateObject("dao.dbengine.36")
Set ws = dbe.CreateWorkspace

s = application.currentdb.name
Set db = ws.OpenDatabase(s)

(david)

 

[aaron.kempf]

hey

this MDB route is crap.

outsource a conversion to an ACCESS DATA PROJECT

it's a lot more robust

 

[Edwinah63]

Hi David,

many thanks for the links. i will compare my linking code (posted i
think in my first post to this board) with that of the links provided.

hopefully, it will all come together. either way i shall let you know.

To Aaron,

mdb or otherwise, how about writing the whole thing in C++? easier for
me

 

[Edwinah63]

Hi David,

many thanks for the links. i will compare my linking code (posted i
think in my first post to this board) with that of the links provided.

hopefully, it will all come together. either way i shall let you know.

 

[Edwinah63]

Hi David,

i could not get your code to work but i adapted some code on the 3rd
link and have something that **seems** to b working. i put it here for
anyone else who has the same problems:

[EDIT]

ODBC_STR = "ODBC;DSN=myDSN;DATABASE=MyDB;UID=" & Usr & ";PWD=;"

Set db = CurrentDb

rs.MoveFirst 'recordset contains names of ODBC tables to be linked

Do While Not rs.EOF

Set td = db.TableDefs(rs("tblName"))

With td
.Connect = ODBC_STR
.RefreshLink
End With

db.TableDefs.Refresh

rs.MoveNext
Loop

End Function

 

[Edwinah63]

also, i forgot to add that there is no need to detach and relink the
tables, simply refreshing the link seems to do the job and the SQL
Server permissions for each user ARE HONOURED!!