SQL查询时出现Excel VBA自动化错误

Question

我是数据库新手，从我们的数据库专家那里得到了这个问题。不幸的是，他的最后一天是昨天。当我在Oracle SQL Developer中运行查询时，它在不到一秒的时间内返回~20条记录，但当我在Excel VBA中尝试它时，它在行"rst.Open StrQuery，cnn“上显示运行时错误'-2147217900 (80040e14)‘自动化错误。

这是我的代码。

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
ConnectionString = "Provider=OraOLEDB.Oracle;Password=XXXXX;User ID=XXXXX;Data Source=XXXXX;"

'Opens connection to the database
cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query
cnn.CommandTimeout = 90

'Query String
StrQuery = "SELECT "
StrQuery = StrQuery & "distinct dbo.mfg_order.mfg_order_name, "
StrQuery = StrQuery & "dbo.mfg_order.mfg_bill_letter, "
StrQuery = StrQuery & "dbo.mfg_order.mfg_order_name || '-' || dbo.mfg_order.mfg_bill_letter as mfg_order_bill, "
StrQuery = StrQuery & "dbo.pcb_header.position_seq_nbr || '00', "
StrQuery = StrQuery & "D2.mfg_catg as catg,D2.mfg_catg_value as catgvalue "
StrQuery = StrQuery & "FROM "
StrQuery = StrQuery & "dbo.sales_order, "
StrQuery = StrQuery & "dbo.planned_shipment, "
StrQuery = StrQuery & "dbo.eco_include_exclude, "
StrQuery = StrQuery & "dbo.mfg_note_header, "
StrQuery = StrQuery & "dbo.pcb_subset, "
StrQuery = StrQuery & "dbo.pcb_detail D2, "
StrQuery = StrQuery & "dbo.pcb_header, "
StrQuery = StrQuery & "dbo.mfg_order "
StrQuery = StrQuery & "WHERE "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_name = dbo.pcb_header.mfg_order_name ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_bill_letter = dbo.pcb_header.mfg_bill_letter ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_revision = dbo.pcb_header.mfg_order_revision ) and "
StrQuery = StrQuery & "( dbo.mfg_order.planned_shipment_nbr = dbo.planned_shipment.planned_shipment_nbr (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.sales_ord_id = dbo.planned_shipment.sales_ord_id (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.sales_ord_id = dbo.sales_order.sales_ord_id (+) ) and "
StrQuery = StrQuery & "( dbo.pcb_header.pcb_header_id = dbo.pcb_subset.pcb_header_id (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_name = dbo.eco_include_exclude.mfg_order_name (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_bill_letter = dbo.eco_include_exclude.mfg_bill_letter (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_revision = dbo.eco_include_exclude.mfg_order_revision (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_name = dbo.mfg_note_header.mfg_order_name (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_bill_letter = dbo.mfg_note_header.mfg_bill_letter (+)  ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_revision = dbo.mfg_note_header.mfg_order_revision (+) ) and "
StrQuery = StrQuery & "(dbo.pcb_header.pcb_header_id = D2.pcb_header_id ) and "
StrQuery = StrQuery & "dbo.mfg_order.bu_mfg_loc_id = 5 AND "
StrQuery = StrQuery & "dbo.pcb_header.prod_code='0050' AND "
StrQuery = StrQuery & "dbo.mfg_order.mfg_order_type = 'SO' and "
StrQuery = StrQuery & "dbo.pcb_header.mfg_prod_grp   = 'FN05' AND "
StrQuery = StrQuery & "(D2.mfg_catg = 'FSIZ') AND "
StrQuery = StrQuery & "D2.MFG_CATG_VALUE='28FB' AND "
StrQuery = StrQuery & "dbo.mfg_order.MFG_BILL_LETTER='A' AND "
StrQuery = StrQuery & "LENGTH(dbo.mfg_order.MFG_ORDER_NAME)=7;"

'Check Query
Debug.Print StrQuery

'Performs the actual query    
rst.Open StrQuery, cnn

'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
'Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

调试程序给出了这样的查询：

SELECT distinct dbo.mfg_order.mfg_order_name, dbo.mfg_order.mfg_bill_letter, dbo.mfg_order.mfg_order_name || '-' || dbo.mfg_order.mfg_bill_letter as mfg_order_bill, dbo.pcb_header.position_seq_nbr || '00', D2.mfg_catg as catg,D2.mfg_catg_value as catgvalue FROM dbo.sales_order, dbo.planned_shipment, dbo.eco_include_exclude, dbo.mfg_note_header, dbo.pcb_subset, dbo.pcb_detail D2, dbo.pcb_header, dbo.mfg_order WHERE ( dbo.mfg_order.mfg_order_name = dbo.pcb_header.mfg_order_name ) and ( dbo.mfg_order.mfg_bill_letter = dbo.pcb_header.mfg_bill_letter ) and ( dbo.mfg_order.mfg_order_revision = dbo.pcb_header.mfg_order_revision ) and ( dbo.mfg_order.planned_shipment_nbr = dbo.planned_shipment.planned_shipment_nbr (+) ) and ( dbo.mfg_order.sales_ord_id = dbo.planned_shipment.sales_ord_id (+) ) and ( dbo.mfg_order.sales_ord_id = dbo.sales_order.sales_ord_id (+) ) and ( dbo.pcb_header.pcb_header_id = dbo.pcb_subset.pcb_header_id (+) ) and ( dbo.mfg_order.mfg_order_name = dbo.eco_include_exclude.mfg_order_name (+) ) and ( dbo.mfg_order.mfg_bill_letter = dbo.eco_include_exclude.mfg_bill_letter (+) ) and ( dbo.mfg_order.mfg_order_revision = dbo.eco_include_exclude.mfg_order_revision (+) ) and ( dbo.mfg_order.mfg_order_name = dbo.mfg_note_header.mfg_order_name (+) ) and ( dbo.mfg_order.mfg_bill_letter = dbo.mfg_note_header.mfg_bill_letter (+)  ) and ( dbo.mfg_order.mfg_order_revision = dbo.mfg_note_header.mfg_order_revision (+) ) and (dbo.pcb_header.pcb_header_id = D2.pcb_header_id ) and dbo.mfg_order.bu_mfg_loc_id = 5 AND dbo.pcb_header.prod_code='0050' AND dbo.mfg_order.mfg_order_type = 'SO' and dbo.pcb_header.mfg_prod_grp   = 'FN05' AND (D2.mfg_catg = 'FSIZ') AND D2.MFG_CATG_VALUE='28FB' AND dbo.mfg_order.MFG_BILL_LETTER='A' AND LENGTH(dbo.mfg_order.MFG_ORDER_NAME)=7;

在这里，它的可读性更好一些(在SQL Developer中，这两种方法都适用)：

SELECT 
distinct dbo.mfg_order.mfg_order_name, 
dbo.mfg_order.mfg_bill_letter, 
dbo.mfg_order.mfg_order_name || '-' || dbo.mfg_order.mfg_bill_letter as mfg_order_bill, 
dbo.pcb_header.position_seq_nbr || '00', 
D2.mfg_catg as catg,D2.mfg_catg_value as catgvalue 
FROM 
dbo.sales_order, 
dbo.planned_shipment, 
dbo.eco_include_exclude,    
dbo.mfg_note_header, 
dbo.pcb_subset, 
dbo.pcb_detail D2, 
dbo.pcb_header, 
dbo.mfg_order 
WHERE 
( dbo.mfg_order.mfg_order_name = dbo.pcb_header.mfg_order_name ) and 
( dbo.mfg_order.mfg_bill_letter = dbo.pcb_header.mfg_bill_letter ) and 
( dbo.mfg_order.mfg_order_revision = dbo.pcb_header.mfg_order_revision ) and 
( dbo.mfg_order.planned_shipment_nbr = dbo.planned_shipment.planned_shipment_nbr (+) ) and 
( dbo.mfg_order.sales_ord_id = dbo.planned_shipment.sales_ord_id (+) ) and 
( dbo.mfg_order.sales_ord_id = dbo.sales_order.sales_ord_id (+) ) and 
( dbo.pcb_header.pcb_header_id = dbo.pcb_subset.pcb_header_id (+) ) and 
( dbo.mfg_order.mfg_order_name = dbo.eco_include_exclude.mfg_order_name (+) ) and 
( dbo.mfg_order.mfg_bill_letter = dbo.eco_include_exclude.mfg_bill_letter (+) ) and 
( dbo.mfg_order.mfg_order_revision = dbo.eco_include_exclude.mfg_order_revision (+) ) and 
( dbo.mfg_order.mfg_order_name = dbo.mfg_note_header.mfg_order_name (+) ) and 
( dbo.mfg_order.mfg_bill_letter = dbo.mfg_note_header.mfg_bill_letter (+)  ) and 
( dbo.mfg_order.mfg_order_revision = dbo.mfg_note_header.mfg_order_revision (+) ) and 
(dbo.pcb_header.pcb_header_id = D2.pcb_header_id ) and 
dbo.mfg_order.bu_mfg_loc_id = 5 AND 
dbo.pcb_header.prod_code='0050' AND 
dbo.mfg_order.mfg_order_type = 'SO' and 
dbo.pcb_header.mfg_prod_grp   = 'FN05' AND 
(D2.mfg_catg = 'FSIZ') AND 
D2.MFG_CATG_VALUE='28FB' AND 
dbo.mfg_order.MFG_BILL_LETTER='A' AND 
LENGTH(dbo.mfg_order.MFG_ORDER_NAME)=7;

Answer 1

我猜你错过了dbo.pcb_header.position_seq_nbr || '00'的别名

试一试

dbo.pcb_header.position_seq_nbr || '00' AS position_seq,

像这样试一下：

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
DIM cmd As New ADODB.Command
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
ConnectionString = "Provider=OraOLEDB.Oracle;Password=XXXXX;User ID=XXXXX;Data Source=XXXXX;"

cnn.Provider = "OraOLEDB.Oracle"
cnn.CursorLocation = adUseClient


'Opens connection to the database
cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query
cnn.CommandTimeout = 90

Set cmd.ActiveConnection = con
Set rst.Source = cmd
rst.CursorType = adOpenStatic

'Query String
StrQuery = "SELECT "
...

cmd.CommandText = StrQuery 
rst.Open