1. #1
    Legendary! Ryme's Avatar
    Join Date
    Jul 2009
    Location
    In a field, somewhere
    Posts
    6,171

    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

    Code:
    (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:

    Code:
    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.
    I am the lucid dream
    Uulwi ifis halahs gag erh'ongg w'ssh


  2. #2
    I am Murloc! Cyanotical's Avatar
    Join Date
    Feb 2011
    Location
    Aurora, Co
    Posts
    5,040
    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-4790K | Z97 Class. | 8GB DDR3-2133 | GTX-690 Quad SLI | RAIDR | 512GB Samsung 830 | AX1200 | FT02
    Dell U2711 | Ducky Shine3 YoS | Steelseries Sensei Wireless | Xonar Essence One | KRK RP8G2s

  3. #3
    Legendary! Ryme's Avatar
    Join Date
    Jul 2009
    Location
    In a field, somewhere
    Posts
    6,171
    Of course, I'm using SQL2000.
    I am the lucid dream
    Uulwi ifis halahs gag erh'ongg w'ssh


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

    Code:
    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)
    Kuroiwolf - 90 Brewmaster Monk
    Youtube Account Twitch TV
    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:

    Code:
    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:

    Code:
    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

  6. #6
    Legendary! Ryme's Avatar
    Join Date
    Jul 2009
    Location
    In a field, somewhere
    Posts
    6,171
    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.
    I am the lucid dream
    Uulwi ifis halahs gag erh'ongg w'ssh


  7. #7
    Legendary! Ryme's Avatar
    Join Date
    Jul 2009
    Location
    In a field, somewhere
    Posts
    6,171
    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

    Code:
            .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.
    I am the lucid dream
    Uulwi ifis halahs gag erh'ongg w'ssh


  8. #8
    Doh - it should be:

    Code:
            .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:

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

Posting Permissions

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