Excel VBA - Need help with a SQL query

[Ryme]

Howdy fellow gamers, this is perhaps an unlikely forum to ask such a question, but I'm fully aware of how capable and knowledgeable the mmo-c community can be on such issues.

So, here's my problem:

I'm building the mother of all spreadsheets that is, even now before it's finished, going to reduce the amount of time to perform this mandatory daily task 15 fold. I'm quite proud

I'm nearing the final stages, one of the last things I need to do is run a SQL query that returns nothing to the user except a standard

(1 row(s) affected)

(1 row(s) affected)

It's a stored procedure that allocates a new entry into one our databases.

Now, I've had a lot of success in pulling tables from sql to excel through vba with something similar to this:

With ActiveWorkbook.Sheets("XXXX").QueryTables.Add(Connection:=connectionstringgoeshere, Destination:=Sheets("XXXX").Range("A1"), Sql:=sqlquerystringgoeshere)
    .RefreshStyle = xlOverwriteCells
    .Refresh BackgroundQuery:=False
End With

However, this is clearly the wrong format to be using on something that has no table output and indeed it does return an error on execute. My question is, does anybody know what the appropriate format to run a basic sql query is?

Apologies, I've only just started using VBA this week, it's all exceptionally new to me.

Oh, if it helps, I can provide my connection string.

Many thanks.

[Cyanotical]

iirc not all SQLs are the same, oracle's, microsoft's, and mySQL all use a slightly different syntax, so it could help if you posted what SQL server you are using

[Ryme]

Of course, I'm using SQL2000.

[Yohassakura]

I haven't really used VBA but I think something like this could work:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String = "INSERT INTO _table_ VALUES (_,_)"
Dim SQLCount As String = "SELECT COUNT(*) FROM table_name"
Dim count1 As Integer
Dim count2 As Integer
Dim countCheck As Integer

With cn
    .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\.mdb"
    .Provider = "Microsoft Jet 4.0 OLE DB Provider"
    .Open
  End With

Set rs = db.OpenRecordset("_table_", dbOpenDynaset)

rs.open SQLCOUNT, cn

With rs
    Do Until .EOF
        count1 = count1 + 1
    Loop
End With

rs.close

rs.open SQL, cn
rs.close

rs.open SQLCOUNT, cn

With rs
    Do Until .EOF
        count2 = count2 + 1
    Loop
End With

rs.close
cn.close

countCheck = count2 - count1


MsgBox(countCheck & "row(s) affected", vbOK, Return)

[ArPharazon]

There is a significant error in the code sample posted above - the 'Do Until .EOF' block is missing a ".movenext' statement so you'll be stuck in an infinite loop because the recordset pointer is never moving to the next record, so it will never get to the end and exit the loop.

Additionally, its always good practice to check that the connection to the database was successfully opened (or not) by checking the connection state before proceeding to iterate the recordset:

if (rs.State = adStateOpen) then
    With rs
        Do Until .EOF
            count1 = count1 + 1
            .movenext                   '<--- if this line is missing, we get stuck in an endless loop.
        Loop
    End With
else
    MsgBox "Could not open a connection to the database."     '<-- sadface
end if

Here is a better way to get the affected rows count by using the 'Command' object instead, as follows:

Dim cnn As ADODB.Connection
Dim cmd as ADODB.Command
Dim totalAffected as long

' instantiate the connection object
Set cnn = New ADODB.Connection

' Open a connection to the database (for example, using the ODBC driver)
cnn.ConnectionString = "driver={SQL Server};server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"

cnn.Open

if (cnn.State = adStateOpen) then

	' instantiate a Command object to handle SQL statements or storedprocs
	Set cmd = New ADODB.Command

	' set its properties
	With cmd  
		.ActiveConnection = cnn

		.CommandType = adCmdStoredProcedure
		.CommandText = YOUR_STOREDPROCEDURE_NAME_GOES_HERE

		' note:
		'  we're not dealing with any stored proc parameters in this example.
		'  leaving that as an exercise for the reader to figure out

		.Execute RecordsAffected:=totalAffected
	End With

	MsgBox "(" & totalAffected & " row(s) affected)"

	Set cmd = Nothing	

else
	MsgBox "Hmm ... Could not open a connection to the database - check the connection string, and that the DB is running", "Connection failed :("
end if

Set cnn = Nothing

I left out robust error handling cos I'm lazy.

Hope that helps

[Ryme]

That you very much for your input and help, I'm back at work on Tuesday and I'll let you know how it goes.

[Ryme]

Sadly, just getting huge amounts of errors. Each time I think I fix one, a new one appears.

Currently trying to progress through run-time error 3001; "arguments are of the wrong type, are out of acceptable range, or are in conflict with one another".

Which is currently occuring on the following

        .CommandType = adCmdStoredProcedure

It's quite bizarre know exactly how to describe what you want done, but not knowing where to start telling a program to do it. I suppose it's like forgetting vowels.

[ArPharazon]

Doh - it should be:

        .CommandType = adCmdStoredProc

Sorry for the typo :/

---------- Post added 2013-02-07 at 03:30 PM ----------

Also, I should mention that your stored procedure will need to have this line:

SET NOCOUNT OFF

This ensures that SQL server sends the DONE_IN_PROC message (i.e. the 'xx rows affected' message) when it performs the action (insert/update/delete) statement.

The established best practice is to always use 'SET NOCOUNT ON' in every stored proc because it saves a little bit of memory (9 bytes) if SQL server does not have to send the DONE_IN_PROC message. That 9 bytes doesnt sound like terribly much, but it does have an impact dealing with database triggers, or working with some ORMs.

However, if you're dealing with a fairly simple / straightforward stored proc that is just doing an INSERT , UPDATE or DELETE only (and no other complicated shenanigans), then by all means, set nocount to 'OFF'.