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] [/color] 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] [/color] 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)

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

"Maarten" <(E-Mail Removed)> wrote in message
news:74AC9F14-5EFC-4F9F-B820-(E-Mail Removed)...
> 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] [/color] 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)
>[/color]

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)


Maarten wrote:
> 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] [/color] 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)
>[/color]