VBA-SQL Server connection problem...

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:

Code:

---

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

---

Code:

---

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

---

Code:

---

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

---

Code:

---

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

End Sub

---

*********

Thanks

Samo

The # Icon will insert CODE Tags that you can paste the code between. You can also select the code, then use the # Icon. You can also type the CODE Tags manually.

Try this

Code:

---

Sub RunReport ()
    Application.ScreenUpdating=False
    Dim rs as New ADODB RecordSet
    Dim conn As New ADODB.Connection

    Call ConnectSqlServer
    Call Build_SQL

    rs.Open Sql, conn .........
'
'
'
End Sub

---

Row and Column Counters should always be Longs so that you don't habitually declare them as Integers when dealing with more than 32K Rows. Memory is cheap.

Variables that are only used in one Function or Sub should be declared in that Procedure.

Hi SamT,

Thanks for your reply, but i didn't really get the idea of # Icon and you didn't even use it in the code you suggested. The code you suggested is exactly same as mine except that you brought Dim rs and Dim conn from my Option Explicit statement into the sub RunReport() statement, so can you eleborate on what you were trying to refer to?



Samo

Quote:

---

Originally Posted by SamT

The # Icon will insert CODE Tags that you can paste the code between. You can also select the code, then use the # Icon. You can also type the CODE Tags manually.

Try this

Code:

---

Sub RunReport ()
    Application.ScreenUpdating=False
    Dim rs as New ADODB RecordSet
    Dim conn As New ADODB.Connection

    Call ConnectSqlServer
    Call Build_SQL

    rs.Open Sql, conn .........
'
'
'
End Sub

---

Row and Column Counters should always be Longs so that you don't habitually declare them as Integers when dealing with more than 32K Rows. Memory is cheap.

Variables that are only used in one Function or Sub should be declared in that Procedure.

---

The # icon is in the VBA Express Post Editor, it will insert CODE Tags into your post. If you encapsulate the code in your post inside the CODE Tags, the Editor will format your code so it is easier to read.

For an example of how they work look at your first post in this thread, which I edited to encapsulate your code in the post in CODE Tags.

Hi SamT, any help regarding my problem?

Thanks

Samo

You posted the problem.
I made suggestions.
You asked a question.
I answered the question.
I have no new information regarding your problem, therefore, I have no new suggestions.

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
Samo

Attachment 15469