Define OLEDB connection string

I am having trouble defining an OLEDB connection string to
an Oracle database. I have no problem using an ODBC
connection.

Works (ODBC)
obj.Connection = "ODBC;DRIVER={Microsoft ODBC for
Oracle};SERVER=IFAS;UID=scott;PWD=tiger"

Doesn't work (OLEDB)
obj.Connection = "OLEDB; Provider=MSDAORA; Data
Source=ifas; User ID=scott; Password=tiger;"

Does a DSN need to be created for OLEDB connections? What
am I missing? TIA...

John

Hi John,

You should be able to do it DSN-less. The connection string should be:

"Provider=msdaora;Data Source=MyOracleDB;" & _
"User Id=myUsername;Password=myPassword"

I don't know if this slightly-different version will work for you or not.
Info from:

http://www.able-consulting.com/MDAC/...eFromMicrosoft

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


John wrote:
> I am having trouble defining an OLEDB connection string to
> an Oracle database. I have no problem using an ODBC
> connection.
>
> Works (ODBC)
> obj.Connection = "ODBC;DRIVER={Microsoft ODBC for
> Oracle};SERVER=IFAS;UID=scott;PWD=tiger"
>
> Doesn't work (OLEDB)
> obj.Connection = "OLEDB; Provider=MSDAORA; Data
> Source=ifas; User ID=scott; Password=tiger;"
>
> Does a DSN need to be created for OLEDB connections? What
> am I missing? TIA...
>
> John

Jake Marx wrote:
> You should be able to do it DSN-less. The connection string should
be:
>
> "Provider=msdaora;Data Source=MyOracleDB;" & _
> "User Id=myUsername;Password=myPassword"

I seem to remember Oracle's own OLEDB provider being preferred to
Microsoft's version.

The OP can roll their own OLEDB connection string using the following:

Sub ShowConnString()
Dim oDLink As Object
Set oDLink = CreateObject("DataLinks")
On Error Resume Next
MsgBox oDLink.PromptNew
End Sub

Jamie.

--

Jamie Collins wrote:
>> "Provider=msdaora;Data Source=MyOracleDB;" & _
>> "User Id=myUsername;Password=myPassword"
>
> I seem to remember Oracle's own OLEDB provider being preferred to
> Microsoft's version.

Interesting - I remember hearing the opposite some time ago. But when I
google it now, I get conflicting results on which provider is better.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

This string works very well

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourFilename;Extended Properties="Excel 8.0;IMEX=1;HDR=No"

The IMEX=1 is needed to ensure that the file is read with actual and not guessed data types in each cell.

If you have a header (with column names) in row 1, then HDR=Yes should be included so it will be skipped.