Can I use DSN for OLE DB connection?

Hello all,

I am relatively new to SQL and Database. Your help is greatly appreciated! Thanks in advance!

Recently, I joined a company that uses MS SQL Server. Our team, which is located in Toronto, has been maintaining and updating a database. The following is some information on the connection string that we are currently using.

Dim con As ADODB.Connection

Set con = New ADODB.Connection
con.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog = CBA; Data Source=PROD.CORP.COMPANY.COM"

Just last week, I was informed that people in the New York Office requires access to the database. What they want to do, essentially, is to have the ability to use the same spreadsheet that retrieves data from our database. My concern is that the computers in the New York Office might not be under the same server. The "static" Data Source stated in the connection string might not work. I was advise to switch over to ODBC connection so that I can specify a System DSN name to point to a specific server. This will allow me to change the "path" that the DSN is pointed to, without going through the code and change every Data Source name, is this true?

I've been reading up on ODBC and OLE DB. The impression I got is that OLE DB is superior to ODBC. It just doesn't make sense for me to "downgrade", is it? Please advise on what I should do so that people in both the Toronto and the NY office will have access to the database. In addition, is there a way for me to simulate a "remote access" scenario so taht I can be sure the new connection is working as it should be?

Thank you so much for your help! I really appreciate your time and effort in helping me to solve this issue.

Best regards,

William

For DSNs in specific, you can use the OLE DB provider for ODBC and use IPersistFile::Load to load the dsn. Details available at

http://msdn.microsoft.com/library/en-us/oledb/htm/odbcproviderinitialization_properties_and_kag_files.asp?frame=true

For the SQLOLEDB provider, you can use the UDL files instead of DSN files to allow managing connections to data sources.

Details at http://msdn.microsoft.com/library/en-us/oledb/htm/datalinkapioverview.asp?frame=true

Suroor