vba help - Type mismatch for code Set rst = New ADODB.Recordset

[Mallesh23]

Hi Team,

I am trying to extract data from sql server getting typemistch error.

for below line, I am not getting it.
Set rst = New ADODB.Recordset ''Error Type Mismatch

Option Explicit

Dim conn As ADODB.Connection
Dim rst As ADODB.Connection

Sub Run_Report()

Dim server_name As String
Dim DatabaseName As String
Dim sql As String

server_name = "DESKTOP-VJ0FV5Q\SQLEXPRESS"
DatabaseName = "Master"
sql = "Select * from Persons;"

Call Connect_TO_SQLSERVER(server_name, DatabaseName, sql)

End Sub

Sub Connect_TO_SQLSERVER(ByVal server_name As String, ByVal Database_name As String, ByVal SQL_STATEMENT As String)

Dim strConn As String
Dim wsReport As Worksheet
Dim col As Integer

strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Server=" & server_name & ";"
strConn = strConn & "Database=" & Database_name & ";"
strConn = strConn & "Trusted_Connection=yes"

Set conn = New ADODB.Connection  

'Print strConn
'Provider=SQLOLEDB;Server=DESKTOP-VJ0FV5Q\SQLEXPRESS;Database=master;Trusted_Connection=yes

With conn
    .Open ConnectionString:=strConn
    .CursorLocation = adUseClient
End With

[B]Set rst = New ADODB.Recordset ''Error Type Mismatch[/B]


With rst
    .ActiveConnection = conn
    .Open SQL_STATEMENT
End With


Set wsReport = ThisWorkbook.Worksheets.Add

With wsReport

    For col = 0 To rst.Fields.Count - 1
        .Cells(1, col + 1).Value = rst.Fields(col).Name
   
    Next col

End With

Set wsReport = Nothing

Call Close_Object



End Sub


Private Sub Close_Object()

If rst.State <> 0 Then rst.Close
If conn.State <> 0 Then conn.Close

Set rst = Nothing
Set conn = Nothing

End Sub

Library Added.

 

[John_w]

Dim rst As ADODB.Connection

Wrong declaration.

 

[Mallesh23]

Hi John,

Thanks for highlighting understood my mistake. ? .


Thanks
mg