Refresh SQL links via ADO code

[Stuart]

My access database has some tables that are linked to an SQL2005 database.
When I move between development and production I have to refresh these links
and change the connect string. I would like to do this via ADO code. Can
anyone point me in the right direction?

Thanks, Stuart

 

[Stefan Hoffmann]

hi Stuart,

My access database has some tables that are linked to an SQL2005 database.
When I move between development and production I have to refresh these links
and change the connect string. I would like to do this via ADO code. Can
anyone point me in the right direction?

You may use either ADOX or DAO.

http://www.visualbasic.happycodings.com/Database_SQL_Stuff/code19.html
http://www.mvps.org/access/tables/tbl0010.htm


mfG
--> stefan <--

 

[Stuart]

hi Stuart,

You may use either ADOX or DAO.

http://www.visualbasic.happycodings.com/Database_SQL_Stuff/code19.html
http://www.mvps.org/access/tables/tbl0010.htm


mfG
--> stefan <--

Stefan, thank you. I have looked at these two links. The first appears to
refresh links to an Access database via ADO. The second appears to refresh
links to an SQL database via DAO. Was it your suggestion that I meld these
two together?

Stuart

 

[Stefan Hoffmann]

hi Stuart,

Stefan, thank you. I have looked at these two links. The first appears to
refresh links to an Access database via ADO. The second appears to refresh
links to an SQL database via DAO. Was it your suggestion that I meld these
two together?

This really depends on your needs. As far as you don't need
functionality from ADOX I would suggest only to use the DAO way.


mfG
--> stefan <--

 

[Stuart]

hi Stuart,

This really depends on your needs. As far as you don't need
functionality from ADOX I would suggest only to use the DAO way.


mfG
--> stefan <--

Stefan, thanks for the reply. I want to use ADO for 2 reasons. First, I am
currently using it on the project and am not using DAO. Would rather not add
DAO at this stage of the game if I can get it done in ADO and/or ADOX.

Second, and this is the crux of my original question, a learning experience
for me. I have been looking in vain for documentation on such lines of code
as

catDB.Tables(sLinkTableName).Properties("Jet OLEDB:Link Datasource") =
sLinkToNewDatabase

in one of your quoted examples. How do I change that statement to an MS/SQL
datasource? Can you point me to any general documentation that provides a
guide to the properties of SQL tables. Thanks, Stuart