DB trusted_connection不工作

Question

Sub INTL()   
  Dim conn As ADODB.Connection 
  Dim rec1 As ADODB.Recordset 
  Dim thisSql As String  
  Set conn = New ADODB.Connection 
  Dim sConn As String   sConn = "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=mydb;trusted_connection=yes" conn.Open sConn      
  'Set and Excecute SQL Command'     
  thisSql = "select top 5 * from useraccount"               
  'Open Rcordset'     
  Set rec1 = New ADODB.Recordset        
  rec1.Open thisSql, conn       
  'Copy Data to Excel'         
  ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)  
End Sub

代码未在excel中编译，错误:子或函数未定义

Answer 1

如果您的IDE中的代码与以下代码完全相同：

Dim sConn As String   sConn = "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=mydb;trusted_connection=yes" conn.Open sConn      

那么你就缺少了指令分隔符，或者更好的，分隔行：

Dim sConn As String
sConn = "Provider=SQLOLEDB;Trusted_Connection=Yes;Server=mydb;trusted_connection=yes"
conn.Open sConn      

另外，在这一行上：

ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)  

这些括号是通过值(即对象指针的副本)强制传递给CopyFromRecordset方法的objMyRecordset。这将是一个更典型的调用：

ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset

如果CopyFromRecordset接受记录集ByVal，那么它是通过值传递的。如果它接受ByRef，那么它是通过引用传递的。通常不需要强制ByVal。