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

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
863
Office Version
  1. 2010
Platform
  1. Windows
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



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

1614447808733.png
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
863
Office Version
  1. 2010
Platform
  1. Windows
Hi John,

Thanks for highlighting understood my mistake. (y) 🕺 .


Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,129,522
Messages
5,636,817
Members
416,943
Latest member
kitkat22

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