Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpiscit1
Posting Yak Master
130 Posts |
 Posted - 2004-03-24 : 10:33:58
|
| Hello everyone. I relatively new to MS Access and ODBC connectivity. I developed a MS Access interface that is used to connect to our SQL server systems. All of the tables are Linked tables (to the SQL Server). Here is my problem:Sometimes when I make a change to something on the SQL system (such as deleting a column in a table or modifying security) it screws up the ODBC connection at the Access database. The only way to get things working again is to go to each PC that is running the Access Database (the actual database file (mdb) is on a network drive) and modifying the local ODBC (DSN) connections. Actually its not really being modified, i think its just being re-initialized because all I do is click configure and Next, next, next , etc. in the DSN GUI. Is there some way to avoid this? Can you reference DSN connections somewhere in Access or a file so that it does not rely on the OS DSN? In other words, you would be able to connect and use the Access DB from any machine on the network as long as you had the right SQL security....Thanks,John |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-03-24 : 10:58:12
|
| Many schema changes can be handled using an MS Access Add-In. Go to Tools/Add-Ins/Linked Table Manager. You can then select which linked tables you want to update and it will attempt to update them. |
 |
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2004-03-24 : 12:17:50
|
| Is there a way to automate this? |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2004-03-24 : 12:35:42
|
| You may want to look into using a file DSN instead of a machine based DSN, you could then push that file out to your users. Check the Access docs for info about a file DSN.Justin"Hit me with a shovel 'cause I can't believe I dug you." |
|
|
|
|
|
|
ODBC Connection Help