Consulting

Results 1 to 7 of 7

Thread: VBA-SQL Server connection problem...

  1. #1
    VBAX Regular
    Joined
    Feb 2016
    Posts
    17
    Location

    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:

    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
    Last edited by SamT; 02-21-2016 at 09:37 AM. Reason: Added CODE Tags with # Icon. Replaced invalid comment indicator

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Feb 2016
    Posts
    17
    Location
    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 View Post
    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
    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.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Feb 2016
    Posts
    17
    Location
    Hi SamT, any help regarding my problem?

    Thanks

    Samo

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Feb 2016
    Posts
    17
    Location

    New problem..

    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

    VBA-ERROS1.PNG
    Last edited by SamT; 02-24-2016 at 04:49 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •