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
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