Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Query - 2 Parameters | Excel Discussion (Misc queries) | |||
MS Query Oracle syntax | Excel Discussion (Misc queries) | |||
MS Query Oracle syntax | Excel Discussion (Misc queries) | |||
Pivot Table, MS Query and Oracle | Excel Discussion (Misc queries) | |||
How to update ORACLE query definition in Excel file | Links and Linking in Excel |