change data link sources in batch

[argniw]

I have a number of OBDC connections to SQL server tables in my access
database. I want to change the data source for each of the links at
once. With the linked table manager you can only change the source one
link at a time.

I will want to provide the user with a way to switch the data source
from a form interface.

How can this be accomplished?

Thanks,

[MGFoster]

ar****@yahoo.com wrote:

I have a number of OBDC connections to SQL server tables in my access
database. I want to change the data source for each of the links at
once. With the linked table manager you can only change the source one
link at a time.

I will want to provide the user with a way to switch the data source
from a form interface.

How can this be accomplished?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just change the .Connect property of the TableDef object, then run the
..RefreshLink method. E.g.:

CurrentDB.TableDefs("my table").Connect = "ODBC;Driver=SQL Server;..."
CurrentDB.TableDefs("my table").RefreshLink

Of course, this should be done in a VBA module w/ error traps.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjesK4echKqOuFEgEQItGQCfbF7qHFWJ630oZE9JP+d9FW qL/60AoLV5
+hd/NJsFnV8oCl8oAy3Qi65G
=fp64
-----END PGP SIGNATURE-----

[argniw]

I will want to loop through all of my linked tables so I don't have to
input each table name in the code. How can I incorporate this method
with VBA in Access?

MGFoster wrote:

ar****@yahoo.com wrote:

I have a number of OBDC connections to SQL server tables in my access database. I want to change the data source for each of the links at
once. With the linked table manager you can only change the source one link at a time.

I will want to provide the user with a way to switch the data source from a form interface.

How can this be accomplished?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just change the .Connect property of the TableDef object, then run

the .RefreshLink method. E.g.:

CurrentDB.TableDefs("my table").Connect = "ODBC;Driver=SQL Server;..." CurrentDB.TableDefs("my table").RefreshLink

Of course, this should be done in a VBA module w/ error traps.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjesK4echKqOuFEgEQItGQCfbF7qHFWJ630oZE9JP+d9FW qL/60AoLV5
+hd/NJsFnV8oCl8oAy3Qi65G
=fp64
-----END PGP SIGNATURE-----

[argniw]

The linked table manager does not seem to consistently change the links
on all tables. How can I change the links for all of my linked tables/
ar****@yahoo.com wrote:

I will want to loop through all of my linked tables so I don't have to input each table name in the code. How can I incorporate this method
with VBA in Access?

MGFoster wrote:

ar****@yahoo.com wrote:

I have a number of OBDC connections to SQL server tables in my access database. I want to change the data source for each of the links at once. With the linked table manager you can only change the

source

one link at a time.

I will want to provide the user with a way to switch the data source from a form interface.

How can this be accomplished?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Just change the .Connect property of the TableDef object, then run

the

.RefreshLink method. E.g.:

CurrentDB.TableDefs("my table").Connect = "ODBC;Driver=SQL

Server;..."

CurrentDB.TableDefs("my table").RefreshLink

Of course, this should be done in a VBA module w/ error traps.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjesK4echKqOuFEgEQItGQCfbF7qHFWJ630oZE9JP+d9FW qL/60AoLV5
+hd/NJsFnV8oCl8oAy3Qi65G
=fp64
-----END PGP SIGNATURE-----

[pietlinden]

you mean you've looped through the tabledefs and reset each .Connect
property and then refreshed and it still didn't work?

How about ending the mystery and posting the code you used...