Excel DSN Query

[Craash]

I understand this is an access forum so I apologize if this is inappropriate.
I have an excel spreadsheet that I have created a query using a DSN to access a sql database. My question; is there a way to change the DSN without creating a new query?

I have a SQL database that is used as a backend to an accounting application. Currently many upper management users have created spreadsheets to pull reports from this database using the backend (DSN connection). Since this is a production database I have created a script to restore the nightly backup to another server and I have denied access to the production server backend. Now I have many spreadsheets with queries set up to use a DSN connection to the production database. I would like to know if there is a known way or script to change the DSN without creating new queries.

Thanks for any help that you can provide.

Sboivin

[MMcCarthy]

I understand this is an access forum so I apologize if this is inappropriate.
I have an excel spreadsheet that I have created a query using a DSN to access a sql database. My question; is there a way to change the DSN without creating a new query?

I have a SQL database that is used as a backend to an accounting application. Currently many upper management users have created spreadsheets to pull reports from this database using the backend (DSN connection). Since this is a production database I have created a script to restore the nightly backup to another server and I have denied access to the production server backend. Now I have many spreadsheets with queries set up to use a DSN connection to the production database. I would like to know if there is a known way or script to change the DSN without creating new queries.

Thanks for any help that you can provide.

Sboivin

I'm no expert although there are some experts here with knowledge in this area I believe.

At a guess I would say that you would have to find a way to access the query properties to change the DSN connection name.

Mary

[Craash]

I'm no expert although there are some experts here with knowledge in this area I believe.

At a guess I would say that you would have to find a way to access the query properties to change the DSN connection name.

Mary

Hi Mary, thanks for the reply. Microsoft does not make it that easy for us. When you access the query properties it lets you change everything but the DSN connection.

I believe I have figured out a way to do it by using a simple script...
ActiveSheet.QueryTables(1).Connection = "ODBC;DSN=newDSN"
ActiveSheet.QueryTables(1).Refresh

Believe it or not this seems to work...Thanks :)

[MMcCarthy]

Hi Mary, thanks for the reply. Microsoft does not make it that easy for us. When you access the query properties it lets you change everything but the DSN connection.

I believe I have figured out a way to do it by using a simple script...
ActiveSheet.QueryTables(1).Connection = "ODBC;DSN=newDSN"
ActiveSheet.QueryTables(1).Refresh

Believe it or not this seems to work...Thanks :)

Good to know ;)