1. #1

    Excel VBA - Need help with a SQL query

    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.
    Last edited by Ryme; 2013-02-01 at 10:52 AM.

  2. #2
    I am Murloc! Cyanotical's Avatar
    Join Date
    Feb 2011
    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

    i7-6700K | Z170 Class. K | 16GB DDR4-3200 | GTX-980ti | Samsung 950 Pro RAID0 | SuperNOVA 1Kw P2 | Lian Li PC-07S
    ROG Swift PG348Q | Ducky DSX | Zowie ZA11 | Xonar Essence One | Tannoy Reveal 402

  3. #3
    Of course, I'm using SQL2000.

  4. #4
    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"
      End With
    Set rs = db.OpenRecordset("_table_", dbOpenDynaset)
    rs.open SQLCOUNT, cn
    With rs
        Do Until .EOF
            count1 = count1 + 1
    End With
    rs.open SQL, cn
    rs.open SQLCOUNT, cn
    With rs
        Do Until .EOF
            count2 = count2 + 1
    End With
    countCheck = count2 - count1
    MsgBox(countCheck & "row(s) affected", vbOK, Return)
    Computer: Intel I7-3770k @ 4.5GHz | 16GB 1600MHz DDR3 RAM | AMD 7970 GHz @ 1200/1600 | ASUS Z77-V PRO Mobo|

  5. #5
    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.
        End With
        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"
    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
    		' 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	
    	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

  6. #6
    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.

  7. #7
    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.

  8. #8
    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:

    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'.

Posting Permissions

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