Trouble creating DSN-less connection

R

Red

Hi,

Im trying to get a connection to an Oracle database using Excel.
Following the advice on:

http://www.dicks-clicks.com/excel/ExternalData3.htm#CreateVBA

I can get a successful connection using DSN. But I cannot seem to
connect without DSN. The following code generates an error 1004
(application-defines or object-defined error). Any pointers
appreciated. Thanks.

Sub CreateQT()

Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable

'This connection string works
sConn = "ODBC;DSN=WCS;" & _
"UID=username;" & _
"PWD=password;" & _
"SERVER=server_name.world;"

'This connection string does not, Error 1004
'sConn = "Driver={Microsoft ODBC For Oracle};" & _
"Server=server_name.world;" & _
"Uid=username;" & _
"Pwd=password"

sSql = "SELECT * FROM SKU"

Set oQt = ActiveSheet.QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("a1"), _
Sql:=sSql)

oQt.Refresh

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top