SQL Select,Insert,Update queries from Excel vba?

[ravikumar00008]

I am having an excel workbook with some sheets.From that i am able to read the current month and previous month sheets data and inserting those columns data into sql table.


But to avoid insertion of same data all the time,


I just want to check a condition like if particular columns of sql table data and excel sheets data may match then i want to update those records in the database otherwise i want to insert as new records in to the database.


Some part i am successfully done(like insertion) but


i am not succeeded at select statement in the code(to check the rows count based on conditions for columns data match) and
at updating those records if condition met.


Please look at the sample files along with database creation and please provide any solution to solve my problem


Thanks for any solutions.


Regards
Kumar

[Bob Phillips]

Fits of all, thanks for the table creation script, that was a nice touch.

Rewrote the code a bit

​Option Explicit

Private Cn As Object 'ADODB.Connection
Private RS As Object 'ADODB.Recordset


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Const connString As String = _
    "Driver={SQL Server};" & _
    "Server=<server>;" & _
    "Database=<database>;" & _
    "Uid=<user>;" & _
    "Pwd=<password>;"
Dim CurrMonth As String, PrevMonth As String
Dim ServerName As String
Dim DatabaseName As String
Dim TableName As String
Dim UserID As String
Dim Password As String
Dim Msg As String
    
    Msg = MsgBox("Do you really want to save the workbook?", vbYesNo)
    
    If Msg = vbYes Then
    
        ServerName = "."
        DatabaseName = "Upload"
        TableName = "Test"
        UserID = ""
        Password = ""
    
        Set Cn = CreateObject("ADODB.Connection")
        With Cn
        
            .CursorLocation = 3 'adUseClient
            .Open Replace(Replace(Replace(Replace(connString, _
                                            "<server>", ServerName), _
                                    "<database>", DatabaseName), _
                            "<user>", UserID), _
                    "Password>", Password)
            .CommandTimeout = 0
        End With
        
        CurrMonth = Format$(Date, "mmm 'yy")
        PrevMonth = Format$(Date - Day(Date), "mmm 'yy")
        
        Call upload(Worksheets(PrevMonth))
        Call upload(Worksheets(CurrMonth))
    
        Cn.Close
        Set Cn = Nothing
    Else
    
        MsgBox "Operation was Cancelled"
    End If
    
End Sub


Sub upload(ByRef sh As Worksheet)
Const sSQLSelect As String = _
    "SELECT Count( ID ) " & vbNewLine & _
    "FROM Test " & vbNewLine & _
    "WHERE Country='<country>' AND " & vbNewLine & _
    "      Name='<name>'"
Const sSQLInsert As String = _
    "INSERT INTO Test(Country" & vbNewLine & _
    "                ,Name" & vbNewLine & _
    "                ,Month" & vbNewLine & _
    "                ,Year) " & vbNewLine & _
    "VALUES ('<country>'" & vbNewLine & _
    "       ,'<name>'" & vbNewLine & _
    "       ,'<month>'" & vbNewLine & _
    "       ,'<year>')"
Const sSQLUpdate As String = _
    "UPDATE Test " & vbNewLine & _
    "SET Month = '<month>'" & vbNewLine & _
    "   ,Year = '<year>'" & vbNewLine & _
    "   ,Name = '<name>'" & vbNewLine & _
    "WHERE Country = '<country>'"
Dim shtSheetToWork As Worksheet
Dim lRow As Long, lCol As Long
Dim sSQL As String
Dim SplitMonthYear As String
Dim SplitMonth As String
Dim SplitYear As String
Dim SqlRowCount
Dim LastRow As Long
Dim mtxRecords As Variant


    With sh
    
        SplitMonthYear = .Name
        SplitMonth = Left(SplitMonthYear, 3)
        SplitYear = "20" & "" & Right(SplitMonthYear, 2)
        
        Set RS = CreateObject("ADODB.RecordSet")
    
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).row
    
        For lRow = 2 To LastRow
            
            sSQL = Replace(Replace(sSQLSelect, _
                                "<country>", .Cells(lRow, "B").Value), _
                        "<name>", .Cells(lRow, "C").Value)
            RS.Open sSQL, Cn, 2, 3 'adOpenDynamic, adLockOptimistic
            mtxRecords = RS.GetRows
            If mtxRecords(0, 0) = 0 Then
    
                sSQL = Replace(Replace(Replace(Replace(sSQLInsert, _
                                                    "<country>", .Cells(lRow, "B").Value), _
                                            "<name>", .Cells(lRow, "C").Value), _
                                    "<month>", SplitMonth), _
                            "<year>", SplitYear)
                Cn.Execute sSQL
            Else
    
                sSQL = Replace(Replace(Replace(Replace(sSQLUpdate, _
                                                    "<country>", .Cells(lRow, "B").Value), _
                                            "<name>", .Cells(lRow, "C").Value), _
                                    "<month>", SplitMonth), _
                            "<year>", SplitYear)
                Cn.Execute sSQL
            End If
            RS.Close
        Next lRow
    End With
End Sub

[ravikumar00008]

Hi Bob,

Many thanks for your reply.

Actually i'm new to excel vba coding.But some how i am doing this.

I have tried your code and i am getting the following error.Can you please solve it.


Sorry to say that i am not able to understand your code exactly. Can you please add the comments to the lines of your code.

Sorry for giving the trouble.Because i can't go forward without understanding the code.

Please consider my request.

Regards
Kumar

[ravikumar00008]

Bob,

I have found the solution for the error.

This is because of table creation(I didn't give the Identity constraint for the column [ID]).

The new problem is i am not getting the 2 sheets (currmonth and prevmonth) data into the sqltable.It is showing either one sheet data only.
I want 2 sheets data in to the sql table.

Please help me in this.

Regards
Kumar

[Bob Phillips]

Kumar,

In your original code you were trying to insert the ID into the table on news, but you had specified ID as an identity column. SO I removed the ID on the insert, and thus the data for Jul will update the Jun data as the country and the name are identical. I cannot see how you got that Null error if you were defining the ID as an identity column.

If you want both Jun and Jul data to go into the table, then you need to decide what the key is. Is it ID? I struggle to see the relevance of ID when the real key would be month & year, that is what I would use if both sheets of data are required. And if so, would there ever be updates, wouldn't it all just be inserts.

You need to make these design decisions before the code can be finished.

[ravikumar00008]

Bob,

I want to catch the ID comming from the sqlTable like this but i am not succeeded

ColA = shtSheetToWork.Cells(lRow, 1)
ColB = shtSheetToWork.Cells(lRow, 2)
SQLSelect = "select ID from Test where Country= ' " & ColA & " ' and Name=' " & ColB & " ' and Month=' " & SplitMonth& " ' and Year=' " & SplitYear & " ' "
Cn.Execute SQLSelect

Here how can i catch the result in a variable.

Please help me to achieve this.

Regards
Kumar

[Bob Phillips]

You totally ignored every point I made, every question I asked. You are telling me what you are trying to do rather than what you want to do, which quite honestly is irrelevant to me as I know you don't really know how to do it.

[ravikumar00008]

Bob,

Sorry for giving the trouble to you.

Hope this is the last post on this one from my side and expecting the reply from you.

I am able to execute the query with the values hold in ColA,ColB,SplitMonth,SplitYear for each iteration and I'm getting the ID's too in sql.

But what my question is why can't i get the ID for each iteration in excel from sql table and if the ID holds some value,i will update the record with that ID other wise i can insert the record.

weather am i going or thinking in a not possible way?
Please gave clarity on this one.

Thanks for your patience and replies.

Regards
Kumar