query on a network

ce0989 · May 9th, 2002, 11:54 AM

I am attempting to set up a DSN and query from Excel to an Oracle database. The problem I am fighting is where the DSN and .dqy need to be saved. At this point, if I set up a DSN and a copy of the .dqy then it works. I would like to be to just set it up on the server once but I don't know how. I'm new to the Excel part so perhaps I'm missing something basic?

Mark W. · May 9th, 2002, 12:17 PM

See the Excel Help topic for "Ways to retrieve data from an external database".

ce0989 · May 9th, 2002, 12:44 PM

Thanks for the suggestion, but the information did not address how to direct the query to a dsn on the server. If I create the query and/or dsn on a client machine, it remains specific to that machine. Many of the users on this system are not in the same city, so going around to install them is not a viable option. Is there a way to make the dsn and .dqy point to a server?

kkknie · May 9th, 2002, 01:26 PM

I've done similar things to an access an SQLServer database using the connection string and ADO.

For an access example:

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\mps-rfiDatabaseProd_Data.mdb;" & _
"Persist Security Info = False"

I'm sure you can find something similar for Oracle. Probably would be better to surf some of the Oracle message boards or check out Microsoft's ADO information at support.microsoft.com

Good luck,

K

Mark W. · May 9th, 2002, 01:35 PM

Quote:

On 2002-05-09 11:44, ce0989 wrote:
Thanks for the suggestion, but the information did not address how to direct the query to a dsn on the server. If I create the query and/or dsn on a client machine, it remains specific to that machine. Many of the users on this system are not in the same city, so going around to install them is not a viable option. Is there a way to make the dsn and .dqy point to a server?

Use "Oracle Net8 Easy Config" to assign a Service Name to a Host Name. Create DSN (on each user's PC) using "Microsoft ODBC Administrator" that specifies the above Service Name as the Data Source Service Name.

[ This Message was edited by: Mark W. on 2002-05-09 12:38 ]

ce0989 · May 10th, 2002, 10:35 AM

Those are great suggestions, I hadn't thought about Easy Config at all. I'll try both and if I find the solution, I'll post it.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 9th, 2002, 11:54 AM	#1
ce0989 New Member Join Date: May 2002 Posts: 5	I am attempting to set up a DSN and query from Excel to an Oracle database. The problem I am fighting is where the DSN and .dqy need to be saved. At this point, if I set up a DSN and a copy of the .dqy then it works. I would like to be to just set it up on the server once but I don't know how. I'm new to the Excel part so perhaps I'm missing something basic?

May 9th, 2002, 12:17 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	See the Excel Help topic for "Ways to retrieve data from an external database".

May 9th, 2002, 12:44 PM	#3
ce0989 New Member Join Date: May 2002 Posts: 5	Thanks for the suggestion, but the information did not address how to direct the query to a dsn on the server. If I create the query and/or dsn on a client machine, it remains specific to that machine. Many of the users on this system are not in the same city, so going around to install them is not a viable option. Is there a way to make the dsn and .dqy point to a server?

May 9th, 2002, 01:26 PM	#4
kkknie Board Regular Join Date: Apr 2002 Location: Greenwood, SC Posts: 677	I've done similar things to an access an SQLServer database using the connection string and ADO. For an access example: strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=\mps-rfiDatabaseProd_Data.mdb;" & _ "Persist Security Info = False" I'm sure you can find something similar for Oracle. Probably would be better to surf some of the Oracle message boards or check out Microsoft's ADO information at support.microsoft.com Good luck, K

May 10th, 2002, 10:35 AM	#6
ce0989 New Member Join Date: May 2002 Posts: 5	Those are great suggestions, I hadn't thought about Easy Config at all. I'll try both and if I find the solution, I'll post it.