Relink ODBC tables without restarting Access

[Maarten]

Dear Experts,

In Access 2000 I am trying to relink ODBC linked tabel(s) to a different
file-DSN, based on user input (I rather not have my users use the linked
table manager, though it performs exactly what I want). For this I use code
similar to the suggestions in KB 208295: Procedure to Create Data Scources
and Relink ODBC Tables.

I don't need to create a new DSN or create new tables, I just need to relink
existing tables.

The piece of code that changes the Connect property of the TableDefs of my
tables looks a bit like this: I stole the Connect string using
?CurrentDb.TableDefs("MyTable").Connect

Dim db as DAO.Database, tbl as DAO.TableDef
Dim strTableName As String, strConn As String

Set db = CurrentDb
strTableName = "Table name in the current Access dabatase"
strConn = "very long connection string using user input to assess the
correct DSN"

Set tbl = Db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink

When I run the code, apparently nothing has changed and the table is still
connected through the "old" DSN. However, when I close Access completely
(just closing the database is not enough) and reopen the database, suddenly
the table shows data using the "new" DSN (the one I just relinked the table
to)!!

I also tried to delete the TableDef for this table and build a new
Table(Def), but also here the effect is not reached without restarting Access.

Does anyone of you know if this process can be done without restarting
Access? As I mentioned, the linked table manager relinks without having to
restart, so it should be possible. Or, if this is not possible, is there some
code that can close Access and restart it with the currect databse?

Just a final piece of info: I use a Providex ODBC driver.

Thank you very much for your thoughts and hopefully solution on this matter!!

Best regards,

Maarten
Amsterdam

 

[Alex Dybenko]

Hi,
try to delete old tabledef and create new during relinking, like here:
http://www.mvps.org/access/tables/tbl0010.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Maarten]

Hello Alex,

Thank you for your quick reply! I tried the code and it breaks down
somewhere halfway, on a code line that contains code that goes far beyond my
knowledge of Access or VBA, so I have no clue how to adjust this code to my
specific needs.

Remember the code I presented in my original message works OK, except for
the necesarry restart of Access. This puzzles me somehow, since I haven't
seen this problem descibed anywhere (I have to admit, I haven't searched
everywhere).

Please let me know if you have another suggestion for me. This goes for, of
course, anyone who reads this message.

Thanks again and best regards,

Maarten in a very rainy Amsterdam

 

[Alex Dybenko]

Hi Maarten,
if you write on what line you get an error and what is error message - this
could help to resolve this problem.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Maarten]

Hello again Alex,

Since I have tried many things and found out much since my last post, I have
rephrased my question in a new thread: "Access restart needed after relinking
ODBC tables???" Can you make more of my situation from that question??

Thank you once more,

Maarten