VBA-SQL COde Problem...

[SamVBA1]

Hi guys,
I am new to VBA and need your help with the following module, i can't figure out what the problem is despite everything seems fine to me, I have a worksheet in which i wrote a private sub in which i call the main sub routine (RunReport) from the below module, i have the SQL query within the worksheet and i run it from the module below, as the DEBUG shows, all goes well until the debug happens at this point (rs.Open Sql, conn) meaning there is no problem with SQL query at all or in any of the steps before this step, so what is the problem at this step? here is the module:
Option Explicit

Dim sql_string As String
Dim Sql As String
Dim setup_row As Integer
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sConnString As String

Sub RunReport ()
Application.ScreenUpdating=False
Call ConnectSqlServer
Call Build_SQL
rs.Open Sql, conn 'here is the debug, meaning there is no problem with the two sub routines above but it debugs here when i keep clicking F8**/
If Not rs.EOF Then
Sheets("Data"). Range("A10").CopyFromRecordset rs
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If
If CBool(conn.Stste And adStateOpen) Then conn.Close
Set conn=Nothing
Set rs =Nothing
End Sub

Sub Build_SQL

Sql=""
For setup_row = 3 To sheets("Setup").Range("A65536").End(x1Up).Row
If Sheets("Setup").Cells(setup_row, 2)= sql_string Then
Sql= Sql & Sheets("Setup").Cells(setup_row, 1) & Chr(13)
End If
Next setup_row

End Sub

Sub ConnectSqlServer()
sConnString ="Provider=xxxx;Integrated Security=SSPI; Persist Security Info=True;Data Source=xxxxx"
conn.Open sConnString

End Sub

*********
Thanks
Samo

 

[Kyle123]

xposted:VBA-SQL Server connection routine

 

[Norie]

I've rejigged the code and converted Build_SQL to a function.

Option Explicit

Dim sql_string As String

Sub RunReport()
Dim Sql As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sConnString As String

    Application.ScreenUpdating = False

    Sql = Build_SQL

    sConnString = "Provider=xxxx;Integrated Security=SSPI; Persist Security Info=True;Data Source=xxxxx"
    conn.Open sConnString

    rs.Open Sql, conn    'here is the debug, meaning there is no problem with the two sub routines above but it debugs here when i keep clicking F8**/

    If Not rs.EOF Then
        Sheets("Data").Range("A10").CopyFromRecordset rs
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If
    
    If CBool(conn.State And adStateOpen) Then conn.Close
    
    Set conn = Nothing
    Set rs = Nothing

End Sub

Function Build_SQL()
Dim strSQL As String
Dim setup_row As Long

    For setup_row = 3 To Sheets("Setup").Range("A65536").End(xlUp).Row
        If Sheets("Setup").Cells(setup_row, 2) = sql_string Then
            strSQL = strSQL & Sheets("Setup").Cells(setup_row, 1) & Chr(13)
        End If
    Next setup_row

End Function

Note I've left sql_string declared outside the code as I don't really know what to do with it, for one thing it's value doesn't appear to be set anywhere in the code.

PS There was a typo in Build_SQL, you had x1Up instead of xlUp.

 

[SamVBA1]

[h=2]: VBA-SQL COde Problem...[/h]

Hi,

I have solved the problem by setting sql_string="string" , and now if you look atthe photos I attached earlier it can explain why it worked, but nowno debug at the step "rs.Open Sql, conn" as it went through this step, rather the debug happens at the step"If Not rs.EOF Then" and the error is attached, can you help please? i am not able to figure out what the problem is?

Thanks
Sam

 

[Norie]

Can you post the exact code you are using now?

PS When you post the code, or any other code, can you please use code tags - it makes it a lot easier to read/follow etc.

 

[SamVBA1]

Dim sql_string As String 
Dim Sql As String 
Dim setup_row As Integer 
Dim conn As New ADODB.Connection 
Dim rs As New ADODB.Recordset 
Dim sConnString As String

Sub RunReport () 
    Application.ScreenUpdating=False 
    Call ConnectSqlServer 
    Call Build_SQL 
    rs.Open Sql, conn 'here is the debug, meaning there is no problem   with the two sub routines above but it debugs here when i keep clicking   F8**/
    If Not rs.EOF Then 
        Sheets("Data"). Range("A10").CopyFromRecordset rs 
        rs.Close 
    Else 
        MsgBox "Error: No records returned.", vbCritical 
    End If 
    If CBool(conn.Stste And adStateOpen) Then conn.Close 
    Set conn=Nothing 
    Set rs =Nothing 
End Sub

Sub Build_SQL ()
sql_string="string"
     
    Sql="" 
    For setup_row = 3 To sheets("Setup").Range("A65536").End(x1Up).Row 
        If Sheets("Setup").Cells(setup_row, 2)= sql_string Then 
            Sql= Sql & Sheets("Setup").Cells(setup_row, 1) & Chr(13) 
        End If 
    Next setup_row 
     
End Sub

Sub ConnectSqlServer() 
    sConnString ="Provider=xxxx;Integrated Security=SSPI; Persist Security Info=True;Data Source=xxxxx" 
    conn.Open sConnString 
     
End Sub

So now the problem is at the point "If Not rs.EOF Then", meaning no problem with the Sql code anymore, and the error photo is attached earlier, i hope you can help? if it means the recordset cannot be open then why? what is the reason?
Thanks

Can you post the exact code you are using now?

PS When you post the code, or any other code, can you please use code tags - it makes it a lot easier to read/follow etc.