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
