Load SQL query into a recordset

[MNpoker]

I can do the following when running Queries from access to put them in Excel.

' PUT QUERY TO RECORD SET
Set rst = New ADODB.Recordset
rst.Open VtSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

How do I do the same thing using SQL?

objCommand.CommandText = VtSQL

objCommand.Execute

With XLwb.Worksheets("Primary")
.Range("B2").CopyFromRecordset rst



THANKS!!!

 

[RoryA]

I'm not sure what you mean - you already are using SQL in your rst.open code

 

[MNpoker]

I'm not sure what you mean - you already are using SQL in your rst.open code

When I tried using that one (In excel) I get the following error:

Run-time error '424'
Object required
AND it stops at this line ----> rst.Open VtSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

It was working when I had the code in Access.

Here is the rest of the code:

    'CREATE AND RUN QUERY
    VtSQL = ""
    VtSQL = VtSQL & "SELECT * FROM portinfo;"
       
 
    ' PUT QUERY TO RECORD SET
'Modify below variables accordingly
sServer = "OLEDB;Provider=SQLOLEDB.1;password=xxxxxx;Persist Security Info=True;User ID=sa;Data Source=xxxxxxx"
'Stop modifying

'Prompt for EDM name and comma separated list of analysis IDs
sEDM = InputBox("Please enter the name of the EDM to use", "Enter EDM name")

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCommand = 1

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objCommand = CreateObject("ADODB.Command")

objConnection.Open "Provider=SQLOLEDB;Data Source=" & sServer & ";Trusted_Connection=Yes;Initial Catalog=" & sEDM
    
objCommand.CommandType = adCommand
objCommand.ActiveConnection = objConnection
    
     
    'Command to copy analysis data to temporary table
   
   ' objCommand.CommandText = VtSQL
          ' PUT QUERY TO RECORD SET
    Set rst = New ADODB.Recordset
    rst.Open VtSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   ' objCommand.Execute
       
    With Worksheets("Primary")
        .Range("B2").CopyFromRecordset rst

 

[MNpoker]

GOT IT!!

Simply had to change it to Set rst = objCommandExecute

 

[RoryA]

That's because CurrentProject.Connection doesn't mean anything in Excel, but it's got nothing to do with using SQL, which is why I was confused. If you want to know how to get Access data from Excel, then you can use something like:

Sub GetAccessData()
   ' Sample demonstrating how to return a recordset from an Access db
   ' requires a reference to the Microsoft ActiveX Data Objects Library.
   
   Dim cnn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
   Dim strPathToDB As String, strFormula As String, i As Long
   Dim wks As Worksheet
   Dim lngNextNum As Long, lngRow As Long, lngCol As Long
   Dim varData
   
   
   ' output to activesheet
   Set wks = ActiveSheet
   
   ' Path to database
   strPathToDB = "C:\db1.mdb"
   
   Set cnn = New ADODB.Connection
   ' open connection to database
   With cnn
      .ConnectionTimeout = 500
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .Open
      .CommandTimeout = 500
   End With
   ' SQL query string - change to suit
   strQuery = "tblTest"
   
   ' create new recordset
   Set rst = New ADODB.Recordset
   
   ' open recordset using query string and connection
   With rst
      .Open strQuery, cnn, adOpenForwardOnly, adLockPessimistic, adCmdTable
      ' check for records returned
      If Not (.EOF And .BOF) Then
         'Populate field names
         For i = 1 To .Fields.Count
            wks.Cells(1, i) = .Fields(i - 1).Name
         Next i
         ' Copy data from A2
         wks.Cells(2, 1).CopyFromRecordset rst
      End If
      .Close
   End With
   ' clean up
   Set rst = Nothing
   cnn.Close
   Set cnn = Nothing
End Sub

 

[MNpoker]

That's because CurrentProject.Connection doesn't mean anything in Excel, but it's got nothing to do with using SQL, which is why I was confused. If you want to know how to get Access data from Excel, then you can use something like:

Sorry for the confusion, I had it set up previously to get data from Access but speed became an issue and I need to go directly to the SQL box.

So what I am doing is startying with the Access code and modifying it.

Thanks for the help!

 

[RoryA]

Understood. Important distinction between SQL (the language) and SQL Server (the database)!