Access restart needed after relinking ODBC tables???

[Maarten]

Hello again Experts,

I have asked this before recently, but since I have new information after a
lot of trial (and even more error) I felt free to start a new thread...

I am facing a problem with relinking ODBC tables a few times during one
Access session.

The database I am linking to uses many data sources (1 file-DSN for each
company code, and we have quite a few). The Connect property of TableDef,
once I linked one with the linked table manager, contains the company code
and the path to the data. I have made a query that manipulates this company
code within the connect string and store it in a table.

The following possible solutions all work, but ONLY ONCE (during or just
after Access startup)

1) tbl.refreshlink method (get the TbleDef, reset the Connect Propert and
refresh te link)
2) db.CreateTableDef (first delete the table or even the TableDef and then
recreate it using the correct connect string)
3) DoCmd.TransferDatabase (first delete the table or the TableDef and the
relink using the TranferDatabase method)

I choose the Company code on a form, change that code within the connect
string and then use one of the 3 mentioned solution.

I already mentioned the problem: All method will work but only one time. Say
I choose company 001 on my form, the first time after starting Access and
using one of the methods despribed, the table will indeed show records for
company 001. If I then choose company 002 on my form and try to relink, the
data in the table are still those of company 001, even if I delete the table
of the TableDef with methods 2 and 3.

With methods 1 and 3, even the connect property of the table is set right
after I run them (the string refers to company 002 if I use
?CurrecntDb.TableDefs("mytable").Connect), the table itself however still
shows the company 001 data.

After a restart of Access (restarting the database is not enough,
apparently) the suddenly the company 002 data are visible in the table.

I have tried the solution provided over here:
web page because
it mentions that "The reason you are having to shut down Access is you do not
close out your connections in Access". Provided code breaks down however on
the following line:

Public Function DropLinked(LinkTab as string) As Boolean

because of a type mismatch or something.

Funny thing is that I can do what I want whithout any problem if I use the
linked table manager: The connect string is set properly AND the table is
refreshed. Since I am writing my program for non-Access-experts, I don't want
to have my users being confronted with the manager, especially because the
file-DSN's needed are located on a specific place on the network.

So finally my questions:

[color:red] [/QUOTE] 1) Is there a way to refresh links more than once
during 1 Access session by manupulating the Connect property of the
table(def) and have the tables refresh without restarting Access???

2) Or, if this cannot be done, is there code to quit Access and restart it
with the same database???

[color:black] [/QUOTE] Please let me know if you have an answer for me.
After all, what the Linked Tabel Manager can do, probably YOU can do better
:yay:

Thank you very much for you answers,

Maarten
Amsterdam (The Netherlands, indeed)

 

[Alex Dybenko]

Hi Maarten,
the only idea I have so far - that you have a form opened, bound to your
link table, or recordset is opened, and this can prevent changing the link
I personally use method 2 and it works for me, but this is for SQL Server
What you can also try - to open your ODBC table using query and IN clause:
select * from mytable IN'...'

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

Hello again Experts,

I have asked this before recently, but since I have new information after
a
lot of trial (and even more error) I felt free to start a new thread...

I am facing a problem with relinking ODBC tables a few times during one
Access session.

The database I am linking to uses many data sources (1 file-DSN for each
company code, and we have quite a few). The Connect property of TableDef,
once I linked one with the linked table manager, contains the company code
and the path to the data. I have made a query that manipulates this
company
code within the connect string and store it in a table.

The following possible solutions all work, but ONLY ONCE (during or just
after Access startup)

1) tbl.refreshlink method (get the TbleDef, reset the Connect Propert and
refresh te link)
2) db.CreateTableDef (first delete the table or even the TableDef and
then
recreate it using the correct connect string)
3) DoCmd.TransferDatabase (first delete the table or the TableDef and the
relink using the TranferDatabase method)

I choose the Company code on a form, change that code within the connect
string and then use one of the 3 mentioned solution.

I already mentioned the problem: All method will work but only one time.
Say
I choose company 001 on my form, the first time after starting Access and
using one of the methods despribed, the table will indeed show records for
company 001. If I then choose company 002 on my form and try to relink,
the
data in the table are still those of company 001, even if I delete the
table
of the TableDef with methods 2 and 3.

With methods 1 and 3, even the connect property of the table is set right
after I run them (the string refers to company 002 if I use
?CurrecntDb.TableDefs("mytable").Connect), the table itself however still
shows the company 001 data.

After a restart of Access (restarting the database is not enough,
apparently) the suddenly the company 002 data are visible in the table.

I have tried the solution provided over here:
web page
because
it mentions that "The reason you are having to shut down Access is you do
not
close out your connections in Access". Provided code breaks down however
on
the following line:

Public Function DropLinked(LinkTab as string) As Boolean

because of a type mismatch or something.

Funny thing is that I can do what I want whithout any problem if I use the
linked table manager: The connect string is set properly AND the table is
refreshed. Since I am writing my program for non-Access-experts, I don't
want
to have my users being confronted with the manager, especially because the
file-DSN's needed are located on a specific place on the network.

So finally my questions:

[color:red]

1) Is there a way to refresh links more than once
during 1 Access session by manupulating the Connect property of the
table(def) and have the tables refresh without restarting Access???

2) Or, if this cannot be done, is there code to quit Access and restart it
with the same database???

[color:black] [/QUOTE] Please let me know if you have an answer for me.
After all, what the Linked Tabel Manager can do, probably YOU can do
better
:yay:

Thank you very much for you answers,

Maarten
Amsterdam (The Netherlands, indeed)
[/QUOTE]

 

[DAVID]

Try using a new db object created from the
file name: (using the default workspace,
as you have been doing, I just show it
explicit)

set ws = application.dbengine.workspaces(0)
set db = ws.opendatabase(codedb.name)

That might work. if it doesn't, try using
CreateWorkspace to create a separate workspace
for each DSN. Open a (shared copy of your)
database in the new workspace as shown above,
relink and refresh a table, then close the
workspace:
set ws = application.dbengine.createworkspace(


That will work, but if it doesn't, use
CreateObject to create a separate dbengine
object for each DSN.

(david)

Hello again Experts,

I have asked this before recently, but since I have new information after a
lot of trial (and even more error) I felt free to start a new thread...

I am facing a problem with relinking ODBC tables a few times during one
Access session.

The database I am linking to uses many data sources (1 file-DSN for each
company code, and we have quite a few). The Connect property of TableDef,
once I linked one with the linked table manager, contains the company code
and the path to the data. I have made a query that manipulates this company
code within the connect string and store it in a table.

The following possible solutions all work, but ONLY ONCE (during or just
after Access startup)

1) tbl.refreshlink method (get the TbleDef, reset the Connect Propert and
refresh te link)
2) db.CreateTableDef (first delete the table or even the TableDef and then
recreate it using the correct connect string)
3) DoCmd.TransferDatabase (first delete the table or the TableDef and the
relink using the TranferDatabase method)

I choose the Company code on a form, change that code within the connect
string and then use one of the 3 mentioned solution.

I already mentioned the problem: All method will work but only one time. Say
I choose company 001 on my form, the first time after starting Access and
using one of the methods despribed, the table will indeed show records for
company 001. If I then choose company 002 on my form and try to relink, the
data in the table are still those of company 001, even if I delete the table
of the TableDef with methods 2 and 3.

With methods 1 and 3, even the connect property of the table is set right
after I run them (the string refers to company 002 if I use
?CurrecntDb.TableDefs("mytable").Connect), the table itself however still
shows the company 001 data.

After a restart of Access (restarting the database is not enough,
apparently) the suddenly the company 002 data are visible in the table.

I have tried the solution provided over here:
web page because
it mentions that "The reason you are having to shut down Access is you do not
close out your connections in Access". Provided code breaks down however on
the following line:

Public Function DropLinked(LinkTab as string) As Boolean

because of a type mismatch or something.

Funny thing is that I can do what I want whithout any problem if I use the
linked table manager: The connect string is set properly AND the table is
refreshed. Since I am writing my program for non-Access-experts, I don't want
to have my users being confronted with the manager, especially because the
file-DSN's needed are located on a specific place on the network.

So finally my questions:

[color:red]

1) Is there a way to refresh links more than once
during 1 Access session by manupulating the Connect property of the
table(def) and have the tables refresh without restarting Access???

2) Or, if this cannot be done, is there code to quit Access and restart it
with the same database???

[color:black] [/QUOTE] Please let me know if you have an answer for me.
After all, what the Linked Tabel Manager can do, probably YOU can do better
:yay:

Thank you very much for you answers,

Maarten
Amsterdam (The Netherlands, indeed)
[/QUOTE]