ORACLE中的VBA LOOP SQL
问题描述:
我试图编写一个VBA来运行查询分配给MySQL只有当HCR_DM.HCR_DM_FACT
表被完全加载。我使用该表中不同来源的计数来决定是否完全加载。ORACLE中的VBA LOOP SQL
当我运行下面的宏,我得到一个错误信息的做,当行,说Object doesn't support this property or method.
我是很新的VBA,我想不出有什么需要调整。有人可以帮我弄这个吗?
谢谢!
Const CNSTR = "Provider = OraOLEDB.Oracle; Data Source =CSDPRO; ODBC;DRIVER={Oracle ODBC Driver};SERVER=CSDPRO;User ID=HCR_SANDBOX;password=******"
Sub FillWithSQLData(strSQL As String, wkSht As String)
' Given a SQL query and worksheet, fills the worksheet with a data dump of the SQL query results
' Define variables
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql_count As String
' Set variables
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Connect to SQL Server
With cn
.ConnectionString = CNSTR
.Open
End With
' Query SQL Server and return results as a data dump in cell A2 of the specified worksheet
With rs
.ActiveConnection = cn
sql_count = "select count(distinct a.src_table) from hcr_dm.hcr_dm_fact a"
Set rf = cn.Execute(sql_count)
Do While rf.Fields.Value = 8
.Open strSQL
Loop
Worksheets(wkSht).Range("A2").CopyFromRecordset rs
.Close
End With
' Close connection
cn.Close
Set rs = Nothing
Set Conn = Nothing
End Sub
Sub Refresh()
' Define SQL query
Dim mySQL As String
mySQL = "select a.oracle_fin_company_id || ' - ' || a.oracle_fin_company_desc as COMPANY " & _
"From hcr_dm.legal_entity_summary a " & _
"where a.Company_Header = 'Filed'"
' Choose worksheet where results should be displayed
Dim myWkSht As String
myWkSht = "Sheet1"
' Call connection sub
Call FillWithSQLData(mySQL, myWkSht)
End Sub
答
您并未选择字段。该行
Set rf = cn.Execute(sql_count)
Do While rf.Fields.Value = 8
大概应该是
Set rs = cn.Execute(sql_count)
Do While rs.Fields(0).Value = 8
而且,请注意,您声明rs
错字,但你使用Recordset填充。 我建议您使用Option Explicit
声明来帮助找到这些信息。你可以阅读更多关于它here。
看起来有点乱。你在谈论MySQL,在你的ConnectionString中使用'OraOLEDB.Oracle'和'DRIVER = {Oracle ODBC Driver}'。对于正确的ConnectionString,请检查[ConnectionStrings.com](http://www.connectionstrings.com/)或[连接字符串](http://www.carlprothman.net/Default.aspx?tabid=81) – 2014-10-22 06:15:20