ADO Excel vba

[Kurt]

Hello Everyone,

I am still having some problems this code. I am trying to import some data into Excel from SLQ using ADO. Please advise.

Sub command_dyer3()
    'Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open Drive=Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=checsqldev1\neo3;Use Procedure for Prepare=1
    conn.OpenDriver=SSPI;Server=CHEC;Database=DAT01;UID=myUsername;PWD=myPassword;"
    Dim conn As ADODB.Connection, rs As ADODB.Recordset
    Set conn = New ADODB.Connection

    conn.Open "Provider=microsoft.jet.oledb.4.0;" & _
    "Data Source=" + "C:\My Documents\aProcess Counts\Retail Processing Counts.xls"
    Set comm.ActiveConnection = conn
    strSQL = "USE CHEC;" & _
    "SELECT DISTINCT" & _
        "DAT01.[_@051] AS Branch," & _
        "DAT01.[_@550] AS LoanType," & _
        "DAT01.[_@040] AS Date," & _
        "DAT01.[_@LOAN#] AS LoanNum" & _
        "FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]" & _
        "ON DAT01.[_@040] = [_@040]" & _
        "INNER JOIN [SMT_BRANCHES]" & _
        "ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]" & _
    "WHERE" & _
        "DAT01.[_@040] Between '06/01/2006' And '06/30/2006'" & _
        "AND DAT01.[_@051] = '540' " & _
        "And DAT01.[_@LOAN#] Like '2%' " & _
        "And DAT01.[_@550] = '3' " & _
    "Group BY" & _
        "DAT01.[_@051]," & _
        "DAT01.[_@550]," & _
        "DAT01.[_@TP]," & _
        "DAT01.[_@040]," & _
        "DAT01.[_@LOAN#]" & _
    "ORDER BY" & _
        "DAT01.[_@051]"
    comm.CommandText = strSQL
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

TIA for any and all help.

Kurt

 

[Norie]

Kurt

What problems?

 

[Kurt]

Connection to sql

Hello Norrie,

Thanks for asking. The problem is that I believe I am not correctly connecting to the sql server.

Is there a connection string that you have used before that works in ADO to move data from SQL to an Excel worksheet?

If I comment out the two conn string lines I get an error message that vba cannot find the file or path that it is not correct.

TIA

Kurt

 

[Norie]

Kurt

As I've mentioned before I'm no ADO expert.

Looking at the code though, shouldn't the connection string(s) be enclosed in quotes?

The code won't even compile for me without them.