'if exist' SQL query in VBA with ADODB database connection

Question

I need to uplad data from excel into a database, but I need to check first if there is data in the table for each upload so that I Update or Insert data.

To diferentiate Update or Insert, I'm usign a SQL IF EXIST command, which works okay in SQL. When I try this in Excel VBA I get an error message: "Command text was not set for the command object."

See code below

Dim strSQL As String
Dim Value As String
Dim Reference As String 

    Set RCconn = New ADODB.Connection
    Set TuneCMD = New ADODB.Command
    ' Establish Recordset
    Set Results = New ADODB.Recordset
    'Establish a Connection
    With RCconn
       .Provider = "SQLOLEDB"
       .ConnectionString = ConStr
    End With
    'Open the connection
    RCconn.Open

'i Columns
For i = 5 To 10 '16
'j rows
For j = 6 To 60 '145

Value= Sheets("Value").Cells(j, i)
Reference= "W_F/P_" & Sheets("Reference").Cells(j, i)

stringTest = "IF EXISTS (SELECT * FROM UploadTable WHERE Ref = '" & Reference &  "') "
stringTest = stringTest & "UPDATE Val "
stringTest = stringTest & "SET Val = '" & Value & "' "
stringTest = stringTest & "where Ref = '" & Reference & "' "
stringTest = stringTest & "Else "
stringTest = stringTest & "INSERT INTO UploadTable (Val , Ref ) "
stringTest = stringTest & "values ('" & Value & "', '" & Reference & "')"

RCconn.Execute strSQL

Next
Next

Set Results = Nothing
RCconn.Close
Set RCconn = Nothing

Is it the case that 'IF EXIST' can not be used in VBA? Is there a work around?

Thanks

Pradeep Kumar · Accepted Answer · 2013-11-14 11:10:59Z

3

ADODB.Connection.Execute sends a pass-through query to your database. It doesn't matter what SQL statement was in that string; if your database can understand it, it will be executed. So inspect your SQL query again.

Try this:

Put a breakpoint on the line RCconn.Execute strSQL. When the debugger breaks there, inspect the value of strSQL. Copy it and execute in SQL Server Management Studio directly. If that doesn't work, correct your code that builds that string. If it works, then there is some problem with your ConnectionString. In that case, check that the userID and password you are using in the ConnectionString to connect has adequate privileges.

answered Nov 14, 2013 at 11:10

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Selrac Over a year ago

Thanks Pradeep. I tried adding 'Sheets("Test_Page").Cells(j, i) = stringTest' before 'RCconn.Execute strSQL'. Coping the output into SQL Server Maagement Studio alows me to run the query with no errors.

Pradeep Kumar Over a year ago

In your connection string, if you set the User ID=sa and Password=<whatever your sa password> temporarily, does it work?

Selrac Over a year ago

Thanks Pradeep. My problem was with 'RCconn.Execute strSQL'. I changed to 'RCconn.Execute stringTest' and all works great. Thanks for confirming on the posibility to use 'if esxist' and thanks also to settin me in the right track to resolve this issue. Much appreciated

Collectives™ on Stack Overflow

'if exist' SQL query in VBA with ADODB database connection

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related