我是数据库新手，从我们的数据库人员那里得到了这个查询。 不幸的是，他的最后一天是昨天。当我在Oracle SQL Developer中运行查询时，它可以工作，在不到一秒的时间内返回~20条记录，但是当我在Excel VBA中尝试时，它会在"rst"行上出现运行时错误"-2147217900(80040e14("自动化错误。Open StrQuery， cnn"。
这是我的代码。
'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开发人员中都可以工作(：
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;
我假设你错过了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