CODEWITHSUNDEEP
vbaexcel
Rahul Nere
Rahul Nere

Reputation: 1

DB trusted_connection not working

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

The code is not compiling in excel , error :Sub or function not defined

Upvotes: 0

Views: 39

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

If this is in your IDE exactly like this:

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

Then you're missing instructions separators, or better, separate lines:

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

Also, on this line:

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

These parentheses are force-passing objMyRecordset by value (i.e. a copy of the object pointer) to the CopyFromRecordset method. This would be a more typical call:

ActiveSheet.Range("A1").CopyFromRecordset objMyRecordset

If CopyFromRecordset is taking the recordset ByVal, then it's passed by value. If it's taking it ByRef, then it's passed by reference. Forcing ByVal is normally not needed.

Upvotes: 1

Related Questions