ADO Excel vba

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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.

Code:
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
Kurt

What problems?
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,143,657
Messages
5,720,122
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top