How to display SQL Syntax error from a Excel VBA Userform

I would like to ask for some help, here's the context: I'm using an Excel Workbook that is connected to my SQL Server with ODBC, so the user can use it to make some queries using some macros + buttons.

He asked me if it's possible to create an interface between the Excel and the SQL Server, like if you're using the DBMS, showing a userform to type the query and if you get some syntax error, it will be showed to you).

Here's my problem : I've created successfully the interface, but I cannot display the Syntax Error. It appears only the message : "Run Time Error '1004' SQL Syntax Error".

It's possible to show the exactly message like if you're using the DBMS?


To make it easier to understand, here's my code :

Function Query(SQL As String)

On Error GoTo Err_handler

    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=mydb;Description=test;UID=test;PWD=test;APP=Microsoft Office 2003;WSID=test123" _
        , Destination:=Range("A1"))
        .CommandText = (SQL)
        .Name = "test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

    Exit Function

Err_handler:
    MsgBox Err.Number & " - " & Err.Description

End Function

Thanks in advance!

Answers


You need to use something like the ActiveX Data Objects library (ADODB) so you can get specific connection information. Therefore, when you run the code, the SQL will raise an error on the ADO object, but then the Err object will contain SQL-specific error information bubbled up from the database.

You need to add a Reference to ActiveX Data Objects in your VBA project. Once you have done that, then try this:-

Function MyQuery(SQL As String)

  Dim cn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim rs As ADODB.Recordset

  On Error GoTo Err_handler

  'DB Connection Object
  Set cn = New ADODB.Connection
  cn.Open "DSN=mydb;Description=test;UID=test;PWD=test;APP=Microsoft Office 2003;WSID=test123"

  'SQL Command Object
  Set cmd = New ADODB.Command
  cmd.ActiveConnection = cn
  cmd.CommandType = adCmdText
  cmd.CommandText = SQL

  'Recordset Object to contain results
  Set rs = cmd.Execute

  With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
    .Name = "test"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
  End With

MyQueryx:

  'Clean up - close connections and destroy objects
  If Not rs Is Nothing Then
    If rs.State = ADODB.adStateOpen Then
      rs.Close
    End If
    Set rs = Nothing
  End If

  If Not cmd Is Nothing Then
    Set cmd.ActiveConnection = Nothing
    Set cmd = Nothing
  End If

  If Not cn Is Nothing Then
    If cn.State = ADODB.adStateOpen Then
      cn.Close
    End If
    Set cn = Nothing
  End If

  Exit Function

Err_handler:
  MsgBox Err.Number & " - " & Err.Description
  'Goto to the function exit to clean up
  GoTo MyQueryx

End Function

Need Your Help

Incorrect date in TIMESTAMP like DD_MM_YYYY_HH_MI_SS

javascript variables date timestamp

Colls, I have a code which should create a sToday variable which returns timestamp like “DD_MM_YYYY_HH_MI_SS”:

Rails by Example (by Michael Hartl), Lesson 9.23 redirect will not work

ruby-on-rails ruby authentication login railstutorial.org

I am following Lesson 9 covering redirects, yet the redirect just won't work! I followed the code to the letter to no avail.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.