Connecting Vba to MS SQL (return & display value)

[Forestq]

Hi

I'm doing simple connection to MS SQL and want to get count and display into excel:

Sub test()
    Dim conn As ADODB.Connection
    Dim rcrds As ADODB.Recordset
    Dim iRowNo, i As Long
    Dim id, name, value As String

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets("Sheet1")

    Dim login,passwd,host,dbname  As String
    
    login = ""
    passwd = ""
    host = ""
    dbname = ""
  
    Set conn = New ADODB.Connection
    
    With conn
        .ConnectionString = "driver={sql server};Server=" + host + _
                            ";Database=" + dbname + _
                            ";User ID=" + login + _
                            ";Password=" + passwd + _
                            ";Trusted_Connection=False;"
        .Open
    End With
    
   Dim SQLstr As String
   SQLstr = "SELECT count(*) FROM table1"

   'Set rcrds = conn.Execute(SQLstr)

    ws.Range("K3").Vaue = 'how to display here count number????

   rcrds.Close
   conn.Close
   Set rcrds = Nothing
End Sub

How to display into Range("K3") count number?

 

[pbornemeier]

Good Info In this area:
https://msdn.microsoft.com/en-us/library/ms681436(v=vs.85).aspx
https://msdn.microsoft.com/en-us/library/ms678207(v=vs.85).aspx

  Dim lCount as long

  Do While rcrds.EOF <> True
    lCount = lCount + 1
    rcrds.movenext
  Loop

  ws.Range("K3").Value = lCount

 

[Forestq]

thanks!I have already did something like:

Dim SQLstr As String   SQLstr = "SELECT count(*) FROM t1"   Set rcrds = conn.Execute(SQLstr)      Do While Not rcrds.EOF        ws.Range("E8").CopyFromRecordset rcrds  Loop

 

[RoryA]

You don't really need CopyFromRecordset when you only have one value:

    ws.Range("K3").Value = rcrds(0)

 

[Forestq]

when I did just as you wrote:

SQLstr = "SELECT count(*) FROM t1"
Set rcrds = conn.Execute(SQLstr)
ws.Range("E8").Vaue = rcrds(0)

I got error: Run time error - 438
object doesn't support this property or method

 

[RoryA]

I didn't spot the typo in your original code - it's Value, not Vaue. I'll correct my post.

 

[Forestq]

me neither,

just tested and it's working,

thank you RoryA!