OLE DB Query Table to Oracle using Parameters
 

In Excel, I'm trying to create an OLE DB type QueryTable to an Oracle
database.
I've tried from the Data | Get External Data menu and I've tried creating it
programatically.

I can do that OK and it returns data as it should, but I'm unable to use
parameters. It throws an error.

My query is:
select * from my_table where my_id = ?

If I change the connection to ODBC, then it works fine.

Is someone else out there running Excel 2002 and Oracle able to give it a go
and confirm it's a bug or that I'm just plain doin' it wrong?

Thanks

OLE DB Query Table to Oracle using Parameters
 

I've put some more work into this request and supplied some code:
Can anyone help?

I'm struggling to get the "Get External Data" feature of Excel 2002 working.
I'm doing it by code (VBA)
I want it to have an OLEDB connection to an Oracle 8.1.7 database using a
parameter query.

When the code runs, it errors at the .Refresh statement:
Run-time error '1004':

The query did not run, or the database table could not be opened.

Check the database server or contact your database administrator.
Make sure the external database is available and hasn't been moved
or reorganized, then try the operation again.

I've tried Oracle's provider OraOLEDB.Oracle but error 1004 still occurs but
with a slightly less descriptive error message.
Run-time error '1004':

Application-defined or object-defined error

While troubleshooting, I've been able to get the parameter query working
with an ODBC connection (which by the way is not an option for us).


Example code as below:

AddVesselQueryTable_OLEDB_NoParam - works
AddVesselQueryTable_OLEDB_Param - doesn't work
AddVesselQueryTable_ODBC_Param - works
AddVesselQueryTable_ODBC_NoParam - works


Sub AddVesselQueryTable_OLEDB_NoParam()
Dim qtbVessel As QueryTable
Dim strConnection As String

strConnection = "OLEDB;Provider=MSDAORA.1;Data Source=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 1))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name =
'MyShip'"
.CommandType = xlCmdSql
.Refresh False
End With
End Sub

Sub AddVesselQueryTable_OLEDB_Param()
Dim qtbVessel As QueryTable, prmVesselName As Parameter
Dim strConnection As String

strConnection = "OLEDB;Provider=MSDAORA.1;Data Source=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 3))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name =
?"
.CommandType = xlCmdSql
Set prmVesselName = .Parameters.Add("Vessel Name",
xlParamTypeVarChar)
prmVesselName.SetParam xlConstant, "MyShip"
.Refresh
End With
End Sub

Sub AddVesselQueryTable_ODBC_NoParam()
Dim qtbVessel As QueryTable
Dim strConnection As String

strConnection = "ODBC;DSN=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 1))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name =
'MyShip'"
.CommandType = xlCmdSql
.Refresh False
End With
End Sub

Sub AddVesselQueryTable_ODBC_Param()
Dim qtbVessel As QueryTable, prmVesselName As Parameter
Dim strConnection As String

strConnection = "ODBC;DSN=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 3))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name =
?"
.CommandType = xlCmdSql
Set prmVesselName = .Parameters.Add("Vessel Name",
xlParamTypeVarChar)
prmVesselName.SetParam xlConstant, "MyShip"
.Refresh
End With
End Sub


"Rob van Gelder" wrote in message
...
In Excel, I'm trying to create an OLE DB type QueryTable to an Oracle
database.
I've tried from the Data | Get External Data menu and I've tried creating
it
programatically.

I can do that OK and it returns data as it should, but I'm unable to use
parameters. It throws an error.

My query is:
select * from my_table where my_id = ?

If I change the connection to ODBC, then it works fine.

Is someone else out there running Excel 2002 and Oracle able to give it a
go
and confirm it's a bug or that I'm just plain doin' it wrong?

Thanks

OLE DB Query Table to Oracle using Parameters
 

I don't have Oracle or any experience with this, but his article seems to
say that it isn't supported for a combination similar/related to what you
are using:

http://support.microsoft.com/default...05&Product=odb
PRB: ADO Parameters.Refresh Fails with MSDAORA Provider and Parameterized
Query

maybe it will give you some insight into your problem.

--
Regards,
Tom Ogilvy


Rob van Gelder wrote in message
...
I've put some more work into this request and supplied some code:
Can anyone help?

I'm struggling to get the "Get External Data" feature of Excel 2002
working.
I'm doing it by code (VBA)
I want it to have an OLEDB connection to an Oracle 8.1.7 database using a
parameter query.

When the code runs, it errors at the .Refresh statement:
Run-time error '1004':

The query did not run, or the database table could not be opened.

Check the database server or contact your database administrator.
Make sure the external database is available and hasn't been moved
or reorganized, then try the operation again.

I've tried Oracle's provider OraOLEDB.Oracle but error 1004 still occurs
but
with a slightly less descriptive error message.
Run-time error '1004':

Application-defined or object-defined error

While troubleshooting, I've been able to get the parameter query working
with an ODBC connection (which by the way is not an option for us).


Example code as below:

AddVesselQueryTable_OLEDB_NoParam - works
AddVesselQueryTable_OLEDB_Param - doesn't work
AddVesselQueryTable_ODBC_Param - works
AddVesselQueryTable_ODBC_NoParam - works


Sub AddVesselQueryTable_OLEDB_NoParam()
Dim qtbVessel As QueryTable
Dim strConnection As String

strConnection = "OLEDB;Provider=MSDAORA.1;Data Source=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 1))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name =
'MyShip'"
.CommandType = xlCmdSql
.Refresh False
End With
End Sub

Sub AddVesselQueryTable_OLEDB_Param()
Dim qtbVessel As QueryTable, prmVesselName As Parameter
Dim strConnection As String

strConnection = "OLEDB;Provider=MSDAORA.1;Data Source=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 3))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name =
?"
.CommandType = xlCmdSql
Set prmVesselName = .Parameters.Add("Vessel Name",
xlParamTypeVarChar)
prmVesselName.SetParam xlConstant, "MyShip"
.Refresh
End With
End Sub

Sub AddVesselQueryTable_ODBC_NoParam()
Dim qtbVessel As QueryTable
Dim strConnection As String

strConnection = "ODBC;DSN=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 1))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name =
'MyShip'"
.CommandType = xlCmdSql
.Refresh False
End With
End Sub

Sub AddVesselQueryTable_ODBC_Param()
Dim qtbVessel As QueryTable, prmVesselName As Parameter
Dim strConnection As String

strConnection = "ODBC;DSN=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 3))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name =
?"
.CommandType = xlCmdSql
Set prmVesselName = .Parameters.Add("Vessel Name",
xlParamTypeVarChar)
prmVesselName.SetParam xlConstant, "MyShip"
.Refresh
End With
End Sub


"Rob van Gelder" wrote in message
...
In Excel, I'm trying to create an OLE DB type QueryTable to an Oracle
database.
I've tried from the Data | Get External Data menu and I've tried
creating
it
programatically.

I can do that OK and it returns data as it should, but I'm unable to use
parameters. It throws an error.

My query is:
select * from my_table where my_id = ?

If I change the connection to ODBC, then it works fine.

Is someone else out there running Excel 2002 and Oracle able to give it
a
go
and confirm it's a bug or that I'm just plain doin' it wrong?

Thanks

OLE DB Query Table to Oracle using Parameters
 

Tom,

Thanks for that. It sounds very similar to my problem. <sigh
I wonder if it's fixed in 2.8?

Cheers


"Tom Ogilvy" wrote in message
...
I don't have Oracle or any experience with this, but his article seems to
say that it isn't supported for a combination similar/related to what you
are using:

http://support.microsoft.com/default...05&Product=odb
PRB: ADO Parameters.Refresh Fails with MSDAORA Provider and Parameterized
Query

maybe it will give you some insight into your problem.

--
Regards,
Tom Ogilvy


Rob van Gelder wrote in message
...
I've put some more work into this request and supplied some code:
Can anyone help?

I'm struggling to get the "Get External Data" feature of Excel 2002
working.
I'm doing it by code (VBA)
I want it to have an OLEDB connection to an Oracle 8.1.7 database using
a
parameter query.

When the code runs, it errors at the .Refresh statement:
Run-time error '1004':

The query did not run, or the database table could not be opened.

Check the database server or contact your database administrator.
Make sure the external database is available and hasn't been moved
or reorganized, then try the operation again.

I've tried Oracle's provider OraOLEDB.Oracle but error 1004 still occurs
but
with a slightly less descriptive error message.
Run-time error '1004':

Application-defined or object-defined error

While troubleshooting, I've been able to get the parameter query working
with an ODBC connection (which by the way is not an option for us).


Example code as below:

AddVesselQueryTable_OLEDB_NoParam - works
AddVesselQueryTable_OLEDB_Param - doesn't work
AddVesselQueryTable_ODBC_Param - works
AddVesselQueryTable_ODBC_NoParam - works


Sub AddVesselQueryTable_OLEDB_NoParam()
Dim qtbVessel As QueryTable
Dim strConnection As String

strConnection = "OLEDB;Provider=MSDAORA.1;Data Source=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 1))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name
=
'MyShip'"
.CommandType = xlCmdSql
.Refresh False
End With
End Sub

Sub AddVesselQueryTable_OLEDB_Param()
Dim qtbVessel As QueryTable, prmVesselName As Parameter
Dim strConnection As String

strConnection = "OLEDB;Provider=MSDAORA.1;Data Source=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 3))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name
=
?"
.CommandType = xlCmdSql
Set prmVesselName = .Parameters.Add("Vessel Name",
xlParamTypeVarChar)
prmVesselName.SetParam xlConstant, "MyShip"
.Refresh
End With
End Sub

Sub AddVesselQueryTable_ODBC_NoParam()
Dim qtbVessel As QueryTable
Dim strConnection As String

strConnection = "ODBC;DSN=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 1))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name
=
'MyShip'"
.CommandType = xlCmdSql
.Refresh False
End With
End Sub

Sub AddVesselQueryTable_ODBC_Param()
Dim qtbVessel As QueryTable, prmVesselName As Parameter
Dim strConnection As String

strConnection = "ODBC;DSN=shipping"
With ActiveSheet
Set qtbVessel = .QueryTables.Add(strConnection, .Cells(1, 3))
End With
With qtbVessel
.CommandText = "select vessel_name from vessel where vessel_name
=
?"
.CommandType = xlCmdSql
Set prmVesselName = .Parameters.Add("Vessel Name",
xlParamTypeVarChar)
prmVesselName.SetParam xlConstant, "MyShip"
.Refresh
End With
End Sub


"Rob van Gelder" wrote in
message
...
In Excel, I'm trying to create an OLE DB type QueryTable to an Oracle
database.
I've tried from the Data | Get External Data menu and I've tried
creating
it
programatically.

I can do that OK and it returns data as it should, but I'm unable to
use
parameters. It throws an error.

My query is:
select * from my_table where my_id = ?

If I change the connection to ODBC, then it works fine.

Is someone else out there running Excel 2002 and Oracle able to give
it
a
go
and confirm it's a bug or that I'm just plain doin' it wrong?

Thanks