none
Compare data in SQl Server tables through VBA RRS feed

  • Question

  • Hi everyone,

    I need to compare two Database tables with their column name through VBA. Please consider the below example.

    In Sheet1 I have couple of fields like:

    •Provider=TextBox1

    •Data Source=TextBox2

    •ID=TextBox3

    •Password=TextBox4

    •Database Name=TextBox5

    •Table Name(1)=TextBox6

    •Table Name (2)=TextBox7

    •Primary Key=TextBox8

    In the above mentioned fields user will provide input.After that when a user clicks on a button named as "Fetch" all the columns(only column name) associated to both the mentioned table will be automatically populate to two dropdown list named as "Table Name(1) Columns" & "Table Name(2) Columns" respectively.

    Now user will select which columns he/she wants to compare by selecting values from both the dropdown List.After that user clicks on 'Compare' button the code will compare Table Name(1) Columns with Table Name(2) Columns. The idea is to check the columns value associated with a particular primary key are same in both the table.If a particular primary key contains same column value in both the tables then system will return No Mismatch otherwise it will write the discrepancies in Sheet2.

    Any help would be highly appreciated.

    Saturday, September 6, 2014 6:28 PM

Answers

  • The code from the link shows how to import the data, so you have to compare the data. That can be complex and leads to a code as in my CompareData, but you can also break it down to a very simple form, see the code below.

    Andreas.

    Sub Test()
      Dim Arr1, Arr2
    
      'Let us assume that your SQL request has returned data
      'Means the data is (resp. should be) stored in arrays
      
      'To make the basics as simple as possible, this is the data:
      Arr1 = Array(1, 2, 3, 4, 2)
      Arr2 = Array(1, 2, 7, 4, 5)
      
      'Now you have to compare these arrays:
    
      Dim Item1, Item2
      Dim Found As Boolean
    
      'Search each item in Arr1 in Arr2
      For Each Item1 In Arr1
        Found = False
        For Each Item2 In Arr2
          If Item1 = Item2 Then
            Found = True
            Exit For
          End If
        Next
        If Not Found Then Debug.Print Item1 & " is missing in Arr2"
      Next
    
      'And the other direction
      For Each Item2 In Arr2
        Found = False
        For Each Item1 In Arr1
          If Item1 = Item2 Then
            Found = True
            Exit For
          End If
        Next
        If Not Found Then Debug.Print Item2 & " is missing in Arr1"
      Next
    End Sub

    Wednesday, September 10, 2014 7:07 AM
  • I love that link, Andreas!!

    DA_87, maybe you want something like this . . .

    Sub ADOExcelSQLServer()
         ' Carl SQL Server Connection
         '
         ' FOR THIS CODE TO WORK
         ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
         '
         
        Dim Cn As ADODB.Connection
        Dim Server_Name As String
        Dim Database_Name As String
        Dim User_ID As String
        Dim Password As String
        Dim SQLStr As String
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
         
        Server_Name = "Excel-PC\SQLEXPRESS" ' Enter your server name here
        Database_Name = "Northwind" ' Enter your database name here
        User_ID = "" ' enter your user ID here
        Password = "" ' Enter your password here
        SQLStr = "SELECT * FROM dbo.Orders" ' Enter your SQL here
         
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
         
        rs.Open SQLStr, Cn, adOpenStatic
         ' Dump to spreadsheet
        With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
            .ClearContents
            .CopyFromRecordset rs
        End With
         '            Tidy up
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End Sub
    
    

    Or . . .

    Sub Create_Connectionstring()
    
    '#########################################################
    '# creates a connection string...                        #
    '# References need to be set in the VBE to the following #
    '# reference libraries:-                                 #
    '# Microsoft ActiveX Data Objects x.x Library            #
    '# Microsoft OLE DB Service Component 1.0 Type Library   #
    '#########################################################
    Dim objDL As MSDASC.DataLinks
    Dim cnt As ADODB.Connection
    Dim stConnect As String 'Instantiate the objects.
    
    Set objDL = New MSDASC.DataLinks
    Set cnt = New ADODB.Connection
    
    On Error GoTo Error_Handling 'Show the Data-link wizard
    stConnect = objDL.PromptNew 'Test the connection.
    cnt.Open stConnect 'Print the string to the VBE Immediate Window.
    Debug.Print stConnect 'Release the objects from memory.
    exitHere:
        cnt.Close
        Set cnt = Nothing
        Set objDL = Nothing
    Exit Sub
    
    Error_Handling: 'If the user cancel the operation.
    If Err.Number = 91 Then
        Resume exitHere
    End If
    End Sub
    

    Basically, import everything into two sheets in Excel, then do your comparison.

    But, I think the basic question is, why not just do the comparison in SQL Server?

    http://blogs.msdn.com/b/sqlserverfaq/archive/2013/11/22/how-to-compare-the-rows-of-two-tables-and-fetch-the-differential-data.aspx

    http://www.dba-oracle.com/t_convert_set_to_join_sql_parameter.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, September 11, 2014 3:04 PM

All replies

  • Have a look into this thread:
    http://social.msdn.microsoft.com/Forums/de-DE/92d3b1a4-e23a-458e-9bc0-d5fa07a64fc9/compare-two-excel-sheet?forum=exceldev

    My sub CompareData can handle such a scenario.

    See also my post from today (07/Sep/2014) there is a link to a sample file.

    Andreas.

    Sunday, September 7, 2014 7:18 AM
  • Andreas,

    Thank you for your response.What I understand from your thread that it compares two sheets and write the o/p in a new sheets. Please correct me if my understanding is incorrect.

    But I want when I click on "Fetch" button it will fetch all the the columns from Table Name(1) and Table Name(2) and store them in two dropdownlist.   After that user will select which column he/she wants to compare by selecting respective columns from both the dropdownlist followed by clicking on 'Compare' button.

    Note: I don't want to retrieve data from database table to excel sheet. I want the whole comparison will be done in backend through the code.

    Thank you.

    Sunday, September 7, 2014 9:42 AM
  • What I understand from your thread that it compares two sheets and write the o/p in a new sheets. Please correct me if my understanding is incorrect.

    Have a look closer to CompareData, this sub compares 2 Excel.Range, in more details: internally it compares 2 arrays.

    IMHO it is more important how the result should be shown as where and in what form the input data is provided.

    So I suggest when you click the Fetch button, write the data to compare into a (hidden) temporary sheet, then let the user choose the columns to compare, call CompareData and you get ranges of what is different and what is equal.

    Now you have the location, the values of the results and you can show much more as "all data is equal" or not.

    If you want, you can read the data into an array instead of write it into a sheet, but then you have to rip out the internally part of CompareData and feed it with the arrays directly.

    The basic is to build two dictionary's with unique keys and compare them, see "Step 2:" in CompareData.

    Andreas.

    Sunday, September 7, 2014 11:13 AM
  • Andreas,

    What you suggest I understand.But to be very honest I am not that much good in VBA. Can you please share an excel workbook describing how the data of 2 tables are being fetched from SQL server and then it search the columns that a user has been specified with respect to primary key.

    I would be very grateful to you if you do this for me.

    Sunday, September 7, 2014 6:18 PM
  • Can you please share an excel workbook describing how the data of 2 tables are being fetched from SQL server and then it search the columns that a user has been specified with respect to primary key.

    I'm afraid I must disappoint you, I don't such a file and I'm not familiar with SQL.

    But have a look here:

    http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

    Andreas.

    Monday, September 8, 2014 2:17 PM
  • Hi everyone,

    Can anyone help me to find the solution of the above problem.

    Thanks in advance.

    Tuesday, September 9, 2014 2:41 PM
  • The code from the link shows how to import the data, so you have to compare the data. That can be complex and leads to a code as in my CompareData, but you can also break it down to a very simple form, see the code below.

    Andreas.

    Sub Test()
      Dim Arr1, Arr2
    
      'Let us assume that your SQL request has returned data
      'Means the data is (resp. should be) stored in arrays
      
      'To make the basics as simple as possible, this is the data:
      Arr1 = Array(1, 2, 3, 4, 2)
      Arr2 = Array(1, 2, 7, 4, 5)
      
      'Now you have to compare these arrays:
    
      Dim Item1, Item2
      Dim Found As Boolean
    
      'Search each item in Arr1 in Arr2
      For Each Item1 In Arr1
        Found = False
        For Each Item2 In Arr2
          If Item1 = Item2 Then
            Found = True
            Exit For
          End If
        Next
        If Not Found Then Debug.Print Item1 & " is missing in Arr2"
      Next
    
      'And the other direction
      For Each Item2 In Arr2
        Found = False
        For Each Item1 In Arr1
          If Item1 = Item2 Then
            Found = True
            Exit For
          End If
        Next
        If Not Found Then Debug.Print Item2 & " is missing in Arr1"
      Next
    End Sub

    Wednesday, September 10, 2014 7:07 AM
  • I love that link, Andreas!!

    DA_87, maybe you want something like this . . .

    Sub ADOExcelSQLServer()
         ' Carl SQL Server Connection
         '
         ' FOR THIS CODE TO WORK
         ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
         '
         
        Dim Cn As ADODB.Connection
        Dim Server_Name As String
        Dim Database_Name As String
        Dim User_ID As String
        Dim Password As String
        Dim SQLStr As String
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
         
        Server_Name = "Excel-PC\SQLEXPRESS" ' Enter your server name here
        Database_Name = "Northwind" ' Enter your database name here
        User_ID = "" ' enter your user ID here
        Password = "" ' Enter your password here
        SQLStr = "SELECT * FROM dbo.Orders" ' Enter your SQL here
         
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
         
        rs.Open SQLStr, Cn, adOpenStatic
         ' Dump to spreadsheet
        With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
            .ClearContents
            .CopyFromRecordset rs
        End With
         '            Tidy up
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End Sub
    
    

    Or . . .

    Sub Create_Connectionstring()
    
    '#########################################################
    '# creates a connection string...                        #
    '# References need to be set in the VBE to the following #
    '# reference libraries:-                                 #
    '# Microsoft ActiveX Data Objects x.x Library            #
    '# Microsoft OLE DB Service Component 1.0 Type Library   #
    '#########################################################
    Dim objDL As MSDASC.DataLinks
    Dim cnt As ADODB.Connection
    Dim stConnect As String 'Instantiate the objects.
    
    Set objDL = New MSDASC.DataLinks
    Set cnt = New ADODB.Connection
    
    On Error GoTo Error_Handling 'Show the Data-link wizard
    stConnect = objDL.PromptNew 'Test the connection.
    cnt.Open stConnect 'Print the string to the VBE Immediate Window.
    Debug.Print stConnect 'Release the objects from memory.
    exitHere:
        cnt.Close
        Set cnt = Nothing
        Set objDL = Nothing
    Exit Sub
    
    Error_Handling: 'If the user cancel the operation.
    If Err.Number = 91 Then
        Resume exitHere
    End If
    End Sub
    

    Basically, import everything into two sheets in Excel, then do your comparison.

    But, I think the basic question is, why not just do the comparison in SQL Server?

    http://blogs.msdn.com/b/sqlserverfaq/archive/2013/11/22/how-to-compare-the-rows-of-two-tables-and-fetch-the-differential-data.aspx

    http://www.dba-oracle.com/t_convert_set_to_join_sql_parameter.htm


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, September 11, 2014 3:04 PM