Solved

Excel 3021 VBA error on blank/null field when running query to access SQL 2005 database

Posted on 2010-11-09
4
847 Views
Last Modified: 2012-06-27
Hi
I am running a query from Excel 2007 VBA to extract particular fields from my SQL database.  Soome of the fields are NULL or blank.  When the code hits these fields I get a 3021 error.  Please see code below:

Sub getDetail()

Dim rs As ADODB.Recordset
Dim con As New ADODB.Connection
Dim LastCell As Integer
Dim osheet As Worksheet

On Error GoTo errHandler

    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    iRow = 1
   
    'Select worksheet
    Sheets("ACCT").Select
   
    'specify server
    dataServer = "Database"
   
    'open conncetion
    con.Open "Driver={SQL Server};Server=" & dataServer & ";Database=TEST;Trusted_Connection=yes;"
   
   'Select worksheet
    Sheets("ACCT").Select

     'get last cell with Data
    LastCell = ActiveSheet.UsedRange.Rows.Count
     
   'start processing loan numbers
    For counter = 2 To LastCell
 
            'Get Number
        Set curCell = Worksheets("TD").Cells(counter, 1)
        LNumber = curCell.Value

                 With rs
                     .ActiveConnection = con
                 
                    .Open "SELECT SRNumber,LNumber,CreationDate, " & _
                           "ERSCompleteDate,SRStatus,PrimaryCategory, " & _
                           "CompletionType,RType,Workgroup, " & _
                           "ResolutionSummary " & _
                           "FROM " & _
                           "VW_ECRData " & _
                           "WHERE LNumber = '" & LNumber & "' "
                            iRow = iRow + 1
                            iCol = 2
                            'copy records
                            If Not rs.EOF Then
                               
                                   'Do Until rs.EOF
                                    For i = 0 To rs.Fields.Count - 1
                                        'If IsNull(rs("ERSCompleteDate")) Then
                                        Sheets("TD").Cells(iRow, iCol).Value = rs.Fields.Item(i).Value
                                        'End If
                                        iCol = iCol + 1
                                        'iRow = iRow + 1
                                        rs.MoveNext
                                    Next i
                                   'Loop
                            Else
                               Sheets("TD").Cells(iRow, 2).Value = "CLOSED"
                               appErr = True
                            End If
                 
                  'rs.MoveNext
                'cleanup rs
                .Close
                End With
          Else
         
      End If
    Next

    'cleanup
    con.Close
    Set rs = Nothing
    Set con = Nothing
   
errHandler:

If Err.Number = 3021 Then
    rs.MoveFirst
    Resume Next
ElseIf Err.Number = -2147217871 Then
    MsgBox "Sorry, server is busy, please try again later!", vbInformation, "SERVER BUSY"
    Exit Sub
End If
End Sub

As you can see, I am currently using an Error Handler which will skip the field, but I am not sure is this is the best way to do this and wondering what issues it will cause.

Also, In some instances I'm getting the 3021 error even when the field contains data.  
0
Comment
Question by:DMUM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 125 total points
ID: 34099787
I have had this issue also and used a function to trap nulls

you will note I changed the Open function to build the Sql string separately as I find it much easier to debug when I can see exactly what sqlquery has been built by my code as I frequently get syntax wrong and can rebuild or make test queries on the fly.

I also have a function that converts Null to zero for numeric data; though the BlankNull generally does the same thing as the string "" is converted to zero when assigned to a numeric variable.

'start processing loan numbers
    For counter = 2 To LastCell
 
            'Get Number
        Set curCell = Worksheets("TD").Cells(counter, 1)
        LNumber = curCell.Value

                 With rs
                     .ActiveConnection = con
                    
                    sqlQ = "SELECT SRNumber,LNumber,CreationDate, " & _
                           "ERSCompleteDate,SRStatus,PrimaryCategory, " & _
                           "CompletionType,RType,Workgroup, " & _
                           "ResolutionSummary " & _
                           "FROM " & _
                           "VW_ECRData " & _
                           "WHERE LNumber = '" & LNumber & "' "
                    .Open sqlQ
                            iRow = iRow + 1
                            iCol = 2
                            'copy records
                            If Not rs.EOF Then
                               
                                   'Do Until rs.EOF
                                    For i = 0 To rs.Fields.Count - 1
                                        'If IsNull(rs("ERSCompleteDate")) Then

                                        Sheets("TD").Cells(iRow, iCol).Value = BlankNull(rs.Fields.Item(i).Value)  '<<<<<< call the trap function
                                        'End If
                                        iCol = iCol + 1
                                        'iRow = iRow + 1
                                        rs.MoveNext
                                    Next i
                                   'Loop
                            Else
                               Sheets("TD").Cells(iRow, 2).Value = "CLOSED"
                               appErr = True
                            End If
                 
                  'rs.MoveNext
                'cleanup rs
                .Close
                End With
          Else
         
      End If
    Next

'function to turn db nulls or empty into blank strings
Function BlankNull(dbdata As Variant) As Variant
    If IsNull(dbdata) Then
        BlankNull = ""
     ElseIf IsEmpty(dbdata) Then
        BlankNull = ""
     Else
        BlankNull = dbdata
    End If
End Function

Open in new window

0
 

Author Comment

by:DMUM
ID: 34398510
Hi, I do want to close this and accept the last solution provided.  I apologize for the delay, but I was pulled onto another project and was not able to get back to this before going on vacation and since my return have just been able to get back to this, but not the task itself.  Thank you
0
 

Author Closing Comment

by:DMUM
ID: 34398512
Thank you
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Modify Text File with Excel Macro 13 51
sort time order 10 46
T-SQL: need to reset a declared variable 4 33
Error 1004 Excel 2013 11 18
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question