By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,763 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

change data link sources in batch

P: n/a
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,

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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-----
Nov 13 '05 #2

P: n/a
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-----


Nov 13 '05 #3

P: n/a
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-----


Nov 13 '05 #4

P: n/a
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...

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.